VBA Excel cell to SAP GUI error "at 207 ')' expected"

Question

I am struggling to get the right information from an Excel sheet via VBA script into SAP.

A single cell works fine and will go into SAP GUI screen but the original Excel sheet contains a cell where the last 4 digits need to be deleted and afterwards combined with another cell and then added to go into SAP GUI screen.

This works fine for me:

session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP06/ssubTABFRA1:SAPLMGMM:2000/subSUB5:SAPLZMM_GINCOFIELDS:5802/ctxtMARC-PRCTR").text = (objSheet.Cells(6,"P") & (objSheet.Cells(38,"P")).Value)

But this one doesn't:

session.findById("wnd[0]/usr/subSUBSCR_BEWERT:SAPLCTMS:5000/tabsTABSTRIP_CHAR/tabpTAB1/ssubTABSTRIP_CHAR_GR:SAPLCTMS:5100/tblSAPLCTMSCHARS_S/ctxtRCTMS-MWERT[1,1]").text = (objSheet.Cells(40,"F") & (Left((40,"F"), Len(40,"F") - 4)) & (objSheet.Cells(39,"L") & (objSheet.Cells(39,"O").Value)   

The error message while starting the script means translated:

... at 207 ')' expected ...

Thanks in advance for having a look at this and providing help or a hint.

Answer 1

You are using Left(40,"F"): I suspect that what you actually mean is objSheet.Cells(40, "F"). Same with your Len call, I suspect what you actually want is: Len(objSheet.Cells(40, "F")). So putting that together:

Left(objSheet.Cells(40, "F"), Len(objSheet.Cells(40, "F")) - 4) & objSheet.Cells(39, "L") & objSheet.Cells(39, "O")