0

I've written a simple VBS file to pull data from an SAP GUI and export it to an Excel document. However, I'd like to not manipulate that open Excel document and cannot, for the life of me, get the syntax right.

I've tried to create an Excel application object as such:

Set objXL = CreateObject("Excel.Application")

However, that doesn't seem to point to the active, open Excel document.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Kris Farruggia
  • 1
  • Its not very clear what you want to do, if your VBS creates a doc isn't it already open and available to your code? – Alex K. Nov 03 '17 at 17:48
  • yes it should be...however i'm unclear as to how I'd then manipulate that open file (i.e. change the value of a cell, say). – Kris Farruggia Nov 03 '17 at 17:50
  • Presumably you have a worksheet in a variable? `yourExcelSheetVar.Cells(1, 1) = "Hello"` failing that you would need to show the code that creates it. – Alex K. Nov 03 '17 at 17:53
  • For an already-open Excel instance you would use `GetObject()`, not `CreateObject()` But you should add the rest of your code. – Tim Williams Nov 03 '17 at 17:56
  • Thanks for responding @TimWilliams. I've tried that as well...Let's say for the sake of argument that I've already got an open Excel file and this is the script I'm trying to run: Set objX = GetObject( , "Excel") I get the following error message from Windows Script Host: ActiveX component can't create object: 'GetObject' – Kris Farruggia Nov 03 '17 at 18:00
  • How are you opening that excel document (that is already open) via your code? Are you issuing commands to the SAP GUI by way of SAP Scripting or something and having the gui hit a button that exports as excel and then... trying to grab that open excel object? Or... are you copying data out of SAP, opening an excel application/object and pasting in (or something similar)? I guess... we are trying to understand if, by chance, you already have the excel application available in your code and perhaps this is an XY problem. – JNevill Nov 03 '17 at 18:04
  • Thanks @JNevill ...It's the first instance you mention. I've got code that exports from SAP via scripting (i.e. clicks the button to export) as opposed to copying data out of SAP then pasting into Excel. – Kris Farruggia Nov 03 '17 at 18:10
  • Does the sap excel export deal SAVE the excel file? Like... could you do a `GetObject("C:\users\\Documents\SAP\SAP GUI\the_export.xls")` or is it still unsaved? Either way too, can you `Set objExcel = GetObject( , "Excel.Application")` to pick up the already open excel application, wherein you can find your workbook in the `objExcel.Workbooks` collection? – JNevill Nov 03 '17 at 18:16
  • It is saved...and I suppose I could use the code you show there, however not sure if the would pull in the appropriate user or if I need to call for that first? Thanks for the help...CS major but I haven't coded in years so I'm beyond rusty @JNevill – Kris Farruggia Nov 03 '17 at 18:19
  • You can pick up the username of the currently logged in user [like this](https://social.technet.microsoft.com/Forums/scriptcenter/en-US/6cb594a3-9655-4aaa-bc69-58daf26a7207/username-variable-for-a-vb-script?forum=ITCG) No worries about the rustiness. If you could stumble your way through SAP Scripting in a VBScript then the rest of this should be a cake walk. – JNevill Nov 03 '17 at 18:20
  • Thanks again @JNevill ! So here's another dumb question then...when I pull the user following the script at that link (the .ExpandEnvironmentStrings) I end up with something like "C:\Users\ABC123" but I only want / need the "ABC123" part...can I easily modify the string to remove the first 9 characters? – Kris Farruggia Nov 03 '17 at 18:28
  • Also following that, now that I have the object via the GetObject() function...shouldn't I be able to open the active excel document? – Kris Farruggia Nov 03 '17 at 18:28
  • The excel object contains a collection of workbooks (which may have 1 or many workbooks). You can refer to the one you want directly like `objExcel.Workbooks("workbookname.xls")` and so on. Or you can iterate `For each wb in objExcel.workbooks` or similar. You can split a string into an array and then reference the index of the array containing the value you want, so `Split("C:\Users\ABC123", "\")[2]` should give you the username. Using the ENVIRON stuff though you should just be able to get the username free of the home directory as well. – JNevill Nov 03 '17 at 18:34
  • Thanks again @JNevill ! I'm nearly there I think...so now that I've used the GetObject() to get an Excel object pointed at the file's location (i.e. the open, active workbook) how would I go about manipulating a sheet in that open workbook? Do I need to create an object to tie to the sheet? – Kris Farruggia Nov 03 '17 at 18:53
  • You can refer to the whole danged path like: `objExcel.Workbooks("Workbookname.xls").Sheets("Sheet1").Range("A1").Interior.ColorIndex = 256` Or use a variable `ws = objExcel.Workbooks("Workbookname.xls").Sheets("Sheet1")` and then `ws.Range("A1").value = "poo"` or what have you. Just depends on how much typing you want to do. Pretty much everything can be set to a variable so it's totally up to you. – JNevill Nov 03 '17 at 19:00
  • @JNevill so when I use that line of coding I get an error message: 'Object doesn't support this property or method: objExcel.Workbooks' which I'm suspecting has something to do with the object type? Does the GetObject() automatically set it as an Excel.Application type? (Sorry if you're cringing at my nomenclature). And thanks again! – Kris Farruggia Nov 03 '17 at 19:07
  • Holy crap...I got it! Just removed .Workbooks (as it's already the active workbook...duh). Now off to translate VBA pivot table creation to VBS. Thanks @JNevill you've been a HUGE help! – Kris Farruggia Nov 03 '17 at 19:13
  • I'm glad you got it figured out! – JNevill Nov 03 '17 at 19:26

0 Answers0