Running Excel Macro in EasyMorph Project

[PMReis]

I would to run some Excel Macro actions on top of an EasyMorph Project.

The reason for this is improving the user output (like formatting headers, adding a pivot table for a specific sheet, etc).

I’m thinking of doing this from PowerShell as the final action of the project, as I need to know some of the project’s properties from parameters etc (ex: I need to know it’s sheet 4 that I want to do a pivot table from, or know that it’s column 5 to put color or a subtotal on)

Any suggestion on how to do this?

[dgudkov]

You can use a PowerShell script. For instance (not tested):

$param1 = "{EM_Param1}"
$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("C:\myfile.xls")
$excel.Run("MyMacro", $param1)
$workbook.close()
$excel.quit()

Where {EM_Param1} is an EasyMorph parameter which passed to “MyMacro” as an argument.

Note that running a VBA macro from PowerShell works only in EasyMorph Desktop. It won’t work in EasyMorph Server.

[CraigT]

@dgudkov

$param1 = "{EM_Param1}" $excel = new-object -comobject excel.application $workbook = $excel.workbooks.open("C:\myfile.xls") $excel.Run("MyMacro", $param1) $workbook.close() $excel.quit()

An old post, but something I'm working on now - running Excel VBA as a post-process to EasyMorph. I plugged in the above into a PowerShell action, replacing the path to the Excel file and the macro name (and removed the "$param1" assignment line and "$param1" reference within the macro call ($excel.Run) as I don't need them...right now.). I result in a "Object reference not set to an instance of an object" error. Ideas? Thanks much, Dmitry!

[dgudkov]

Hi Craig,

what returns the error - EasyMorph or the PowerShell script itselft? Can you post a screenshot?

[CraigT]

@dgudkov

$param1 = "{EM_Param1}" $excel = new-object -comobject excel.application $workbook = $excel.workbooks.open("C:\myfile.xls") $excel.Run("MyMacro", $param1) $workbook.close() $excel.quit()

Uploading 2 pics... one ends with "not captured" meaning I set PowerShelll to not capture the error, and the error dialog pops up. The file "captured" is setting PowerShell to log errors - you see the errors listed in the PowerShell Action output.

From an old Excel forum, I grabbed a .vbs file that does the same thing (after I tweaked it for my use). in theory, this should do the same thing - fire up Excel and run the defined macro. I haven't found how to run a .vbs from EasyMorph. However, if I double-click on it, it runs fine.

So, the chain I have set up is... I have the end .xlsx file with the table I want to format. I have an .xlsm file with the macro to open the .xlsx file and apply the formatting. So, the vbs opens the .xlsm file and runs the macro which opens the "end" file, formats it, and saves & closes it. It works if I double-click the .vbs file.

Any/all help is most appreciated. Long-term though was setting up an Excel "form" for the UI input from the user, which would trigger an EasyMorph workflow, handing off the input from the user. After the workflow runs, the final macro is triggered to format the output.

Thanks much for all the help. Much appreciated.

[andrew.rybka]

Hi Craig,

Can you please try to run the same PowerShell script from the PowerShell console? Will it give you the same error?

[CraigT]

And for background, if anyone needs it:

Windows 10 Pro (1903) (OS build 18362.720) 64-bit
Excel for Office 365 MSO (16.0.12527.20260) 32-bit
EasyMorph Version 4.3.0.4

[andrew.rybka]

Craig, it’s possible that this error is related to the fact that you are trying to call a COM object of a 32-bit application from a 64-bit application (which EasyMorph is).

Which PowerShell console you were using to execute this script? 32-bit or 64-bit?

You can check the bitness of the PowerShell console by printing the value of the [Environment]::Is64BitProcess variable.

[DKcrm]

Hi
Is there an example project where this actually works? Would like to play with it to optimize our updating flows as some data is based on pivots in excel that needs updating before we can use the data.

[dgudkov]

The example posted above actually works:

Running Excel Macro in EasyMorph Project

You can use a PowerShell script. For instance (not tested): $param1 = "{EM_Param1}" $excel = new-object -comobject excel.application $workbook = $excel.workbooks.open("C:\myfile.xls") $excel.Run("MyMacro", $param1) $workbook.close() $excel.quit() Where {EM_Param1} is an EasyMorph parameter which passed to “MyMacro” as an argument. Note that running a VBA macro from PowerShell works only in EasyMorph Desktop. It won’t work in EasyMorph Server.

[DKcrm]

Cool - Thanks!

Tried it, however it generates the mentioned error despite the fact that it actually performs the macro in excel.

[dgudkov]

If it actually works, keep the “PowerShell” action in the “Capture output” mode so that it doesn’t abort project execution. Meanwhile, we will take a closer look at what could go wrong.

[dgudkov]

We’ve fixed the PowerShell error. Get the updated version from our website.