Running Macro Code (VB Script) in a New Excel Instance

[John_Hosh]

Hi all,

I'm trying to run some VB Script in a new Excel file.

I have found many tutorials on how to execute a Macro stored within an already existing Excel file, and the following one on Microsoft learn shows how to run some code after opening an already existing file.

Autofit Excel columns using VBScript - Power Automate | Microsoft Learn

In a nutshell what I'm basically trying to do (and failing at) is:

・Launch blank Excel document (stored as ExcelInstance).

・Run VB Script (macro) on that file.

(The resizes are left over from previous experiments to make sure the flow was still working)

Here is the VB Script (mostly based on the above linked help file).

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%ExcelInstance%")
objExcel.Application.Visible = True

'Selects the specified sheet'
Set objSheet = objWorkbook.Sheets("Sheet1")
objSheet.Range("B4").Select
objSheet.Range("D7").Value = "Test Complete - Macro Worked"

If anyone can point me in the right direction (or let me know that it is not possible), that would be fantastic.

(I'm new to Power Automate, so please be gentle)

*** Edit ***

Ideally I'm looking for a way to avoid saving files.

I have found one way of doing this now, however it causes complications if there is already an instance of Excel running when the flow is started (due to it not specifying which instance it should refer to, it uses the already running Excel rather than the one launched by Power Automate). To which I could add a dialogue box asking the user to close Excel before continuing.

Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet
objExcel.Application.Visible = True
objSheet.Range("D7").Value = "Test Complete - Macro Worked"

Is it possible to use the .Handle property of the Excel Instance generated in Power Automate to specify the instance?
(%ExcelInstance.Handle%)

[eetuRobo]

%ExcelInstance% variable is just instance variable for the PAD to know which Excel to use if there are more than one. It isn't file path. You need to give the filepath in the vbscript: objExcel.Workbooks.Open("C:/User/filepath")

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("%FilePath%")

 
So add Set variable action that you have the filepath as a string. Then use that in the vbscript instead of %ExcelInstance%

For error handling toggle on ScriptError -variable to get to know what might be the issue:

With this error it tells pretty clear what is the issue. But if not then look for the numbers after .tmp( 
Those will tell you where the error happens. So its in the second row in this case.

[John_Hosh]

Thank you for your reply.

Since the Excel document is created at the start of the flow and is unsaved, it does not have a filepath.

[eetuRobo]

Can you just use save aciton to save the excel to certain filepath you have already set to FilePath variable before using vbscript?

[Nit1]

please use save excel action to save new file and then refer that path in vbscript action.

[John_Hosh]

Hi @eetuRobo and @Nit1 ,

Thank you for your suggestions, and thank you for pointing out the ScriptError variable.

Ideally I'm looking for a way to avoid saving files.

I have found one way of doing this now, however it causes complications if there is already an instance of Excel running when the flow is started (due to it not specifying which instance it should refer to, it uses the already running Excel rather than the one launched by Power Automate). To which I could add a dialogue box asking the user to close Excel before continuing.

I'll edit the original post to add this.

Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet
objExcel.Application.Visible = True
objSheet.Range("D7").Value = "Test Complete - Macro Worked"

Is it possible to use the .Handle property of the Excel Instance generated in Power Automate to specify the instance?

[Steve48]

@John_Hosh I have read this thread and I may be missing something. To run an Excel macro, the macro workbook (something.xlsm) must already exist with an existing macro. The macro is a sub in this workbook that you call from Power Automate Desktop. Let me know if you need further explanation.

[John_Hosh]

Hi @Steve48 

I'm sorry, I don't think I explained it well.

I'm trying to run some VB code in a new instance of Excel.

You are right in that normally a macro would be within a macro workbook, but as this a blank book launched by Power Automate there are no macros stored within. This is why I am trying to use the "Run VBScript" action with the blank Excel instance.

I hope this helps explain it a bit better.

[Nit1]

I have not seen any example that work with finding excel application handle and run macro on it. if you are not passing any input from PAD flow, you can create excel add-in that loads when new excel is open. then, you can run add-in macro on that excel instance.

[Steve48]

Still don't understand. Are you referring to your vbScript as a Macro? If yes, it isn't. Where is your macro?

[Steve48]

Hi @John_Hosh @Nit1 @eetuRobo 

One other thought. You can have an existing macro workbook that you can interact with from PAD. This macro workbook (an xlsm file) can create a new Excel workbook at a path of your choosing that you can use in your flow. Does this satisfy your requirement?

[John_Hosh]

Hi Steve,

Sorry, I don't think I explained it clearly enough.

Yes, I was referring to the VB Script as a Macro.

The reason being I'm still new to Power Automate and so at first I was looking through all the Macro options (and Googling thusly), since that is where the code is - VB Script in a macro (within a Sub etc).

Eg

Sub Macro17()
'
' Macro17 Macro
' Testing Macro
'

'
MsgBox "Hello World"
End Sub

I'll change the title of the topic from 'Running a Macro (VB Script) in a New Excel Instance' to 'Running Macro Code (VB Script) in a New Excel Instance' to made it clearer, and to hopefully help future finders of this page know it's about the running of the code, not its own Macro.

---

One other thought. You can have an existing macro workbook that you can interact with from PAD. This macro workbook (an xlsm file) can create a new Excel workbook at a path of your choosing that you can use in your flow. Does this satisfy your requirement?

---

I hadn't considered having using a macro stored within an already saved document. However as I may be passing the flow on to other users I'd rather keep things simple, and not have to worry about extra files. Good idea, though.

My current status with this is that I have worked out how to get the VB Script to run in a new Excel instance, assuming that there are no instances already running (I popped this in the Edit of the post). What I'm looking for now is how to make sure it runs only on the new instance if there are instances running beforehand (I'm currently experimenting with the .Handle property).