I have seen the documentation about executing Excel Macro but it’s not very clear exactly how to do it and I have some questions.
the documentation mentions about sample file but not sure where I could find them ( sample_macro.zip)
Why are there two parameters one is Path and the other is Macro file path. If Macro file path is the VBS file then what happens to the macro on the xlsm file? How do I execute the macro on the excel file?
How do I pass a parameter to either of the macro? I can see that in VBS sample you have "Set Book = xlApp.Workbooks.Open(WScript.Arguments(0)) " what is WScript.Arguments(0) ?
Thank you for your feedback. Here’s how you can proceed:
We appreciate you bringing this to our attention. We’ll review the download link for the sample file to ensure it’s accessible.
Studio Pro Macro Support: Studio Pro supports macros using .vbs files. The “Path” field expects an Excel file, which doesn’t necessarily have to be an .xlsm type; it can be an .xlsx file as well. This file will be used to load the .vbs macro logic. The “Macro file path” field is for the .vbs file containing the macro script. Studio Pro requires the macro to be in a .vbs format rather than embedded directly in the Excel file because it executes VBScript files.
Passing Parameters to .vbs Script: You can pass parameters to the .vbs script using the WScript.Arguments array. For instance, WScript.Arguments(0) can be the path of the Excel file, and WScript.Arguments(1) can be another parameter you need to pass to your macro. In Studio Pro, you can send these parameters using the “Input Arguments” field.
Please feel free to reach out if you have more questions or need further assistance!
Thank you for your reply.
so does it mean that ultimately the macro that will be running is the one on the VBS (Macro File Path) and if the excel file has a macro in it, it won’t be executed?
My use case is, I have an excel file which I need to manipulate using another Macro enable excel file and generate a new formatted excel file. Not sure VBS is robust enough to manipulate the data I need but I can explore that. Does it mean, the Path can be the excel file input data, then I use VBS to format the input data and spit out another formatted excel file?
I will have to test passing another parameter to the VBS. still learning about the input arguments.
Yes, the macro logic that will be executed is defined in the .vbs file.
Additionally, you can use .vbs files to format Excel files. For instance, you can apply bold formatting to specific rows, set a background color for specific columns, or change the font size of individual cells. These are just a few examples of the formatting options available through .vbs files. Moreover, you can also process data, read matrix data, and perform operations as needed using .vbs scripts, similar to .vba macros.
Do you know a way to call a function/sub on the excel vba from the vbs? I noticed that if i put a vba on the opening of workbook that it gets executed as well.
