Unanswered

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

(1) Share

Report

Posted on by JD-09041309-0

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%)

Categories:

Power Automate Desktop

I have the same question (0)

All responses (11)

Answers (0)

Sort by

eetuRobo 4,535 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
%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&colon; 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.

Was this reply helpful? Yes No
JD-09041309-0 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
eetuRobo 4,535 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
NM-24071613-0 27 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
JD-09041309-0 on at

Like (0)

Report
Copy link

Link copied!
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?

1 people found this reply helpful.

Was this reply helpful? Yes No
Steve48 431 on at

Like (1)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
JD-09041309-0 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
NM-24071613-0 27 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Steve48 431 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Steve48 431 on at

Like (1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No