excel - How is it better to create a macros and apply it to a changing set of xlsx files?

Question

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?

Answer 1

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.