0

I'm struggling with macros and I can't seem to find what I'm doing wrong.

I have a .vbs file with the following content:

Sub TestMacros()
Application.ScreenUpdating = False
Workbooks("mock_data_template.xlsx").Worksheets("data").Activate
Range("G2").Value = 1
Range("G3").Value = 2
Range("G4").Value = 3
Application.ScreenUpdating = True
End Sub

Here's the screenshot: enter image description here

The Excel file is super simple enter image description here

All the trust settings are enabled. I created the macro above just to test the launch from cmd.

In the cmd, I type: cscript "full-path-to-.vbs" "full-path-to-.xlsx" "TestMacros"

It doesn't return any error but there's no any result in the .xlsx file.

Could you please tell me what I'm doing wrong?

My general goal is I want to have a macros 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

MGMKLML
  • 39
  • 6
  • 1
    A vbs file is not able to access a workbook, even open, if it does not previously access the Excel session where it is open. You look to confuse VBA with VBScript, which are two different things... Then, if the `vbs` does not return any error, this only mean that it does not run. What's happening if you double click the file? – FaneDuru Dec 09 '20 at 12:34
  • @FaneDuru, browsing on the internet, I found an RPA platform that runs macros through "Invoke VBA" activity. But the fact is you provide a `.vbs` file to the function. How does it execute it then? https://youtu.be/nAckIftip0E?t=208 - here's the example (timecoded) – MGMKLML Dec 09 '20 at 12:39
  • You are completely confusing things. **That application** is able to run a function in a VBScript file and a function to take some data from an Excel sheet. It only happens that the sheet name was "VBS" and they named the function in the same way. Did you see there that somebody run the vbs file and it returns from Excel? Where from did you take the code you show in your question? If you need to automate Excel, I can show you how your vbs code has to be adapted... – FaneDuru Dec 09 '20 at 13:00
  • @FaneDuru, sorry but I don't get what you mean. In that particular example from the video, he doesn't interact with the Excel file through the .vbs script at all. If he would do it, he'd have to count how the sheet is named and I can't see how the sheet name would influence the process. Okay, my goal is the following. I want to have a macros 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 – MGMKLML Dec 09 '20 at 13:12
  • I'm afraid you do not know very well what you want. If the application does not interact with Excel through vbs, why did you put the link there? Where is any connection between what you asked and what I said? In the meantime I prepared a VBScript able to do what you explained in your last comment, but I cannot post it, since the question has been closed. If you want seeing it, you have to post another question, but clearly explain what you want doing. And tag me, if you need my code. If your question will be clearer, maybe somebody else will solve it. Put also VBA tag if you want me seeing it. – FaneDuru Dec 09 '20 at 13:32
  • As @FaneDuru points out you can't access the `Application` object from outside VBA in Excel you have to use COM to create an instance of "Excel.Application". – user692942 Dec 09 '20 at 13:50
  • @Lankymart, Do I have then to explicitly assign the workbook to the instance? Something like `Workbooks()`, `Worksheets()` and things like this? – MGMKLML Dec 09 '20 at 14:41
  • @FaneDuru, https://stackoverflow.com/questions/65219056/how-is-it-better-to-create-a-macros-and-apply-it-to-a-changing-set-of-xlsx-files - here's the new question – MGMKLML Dec 09 '20 at 14:45

0 Answers0