My goal is the following. I want to have a macro that I will apply to a constantly extending bunch of .xlsx files. I don't have macros inside those files (I download them), I can't change it in any way. But I have a script and I want it to be applied to an .xlsx file
Should it be a .vbs file? If so, how do I address a workbook?
I have the advantage to see your previous question on a similar issue...
So, a script example able to find a specific workbook in an Excel open session and write in some cells, may look like the following one:
dim objEx, wb, myWb, sh, wbName, mySheet, boolFound
myWB = "mock_data_template.xlsx"
mySheet = "data"
set objEx = GetObject(,"Excel.Application")
if not objex is nothing then
msgbox "Excel session open"
for each wb in objEx.Workbooks
If wb.Name = wbName then
set myWb = wb:boolFound = true: exit for
end if
next
if boolFound then
msgbox "myWorkbook open"
set sh = mywb.Sheets(mySheet)
if not sh is nothing then
msgbox "my sheet exists"
sh.Range("G2").Value = 1
sh.Range("G3").Value = 2
sh.Range("G4").Value = 3
end if
else
msgbox "Workbook not found"
end if
else
msgbox "No Excel session open"
end if
This is just an example to show you how such a simple task can be handled with VBScript. But this can also be handled in Excel, writing a macro to do something similar. Even simpler...
Now, the above code shows you how an Excel existing session can be found and then how to handle workbooks, worksheets, ranges etc. But it can be extended to open such a session and a specific workbook in that new session, then doing what the actual code does. It will only need the workbook full path, like an extra variable. If interested, i can adapt it for such an alternative, too.
