Server PowerShell Fail

I’m running a macro through powershell and I get this error when the file is too large. Is there a timeout somewhere I can adjust to have the task complete successfully?

Document not saved.

Here’s the Powershell script:

$excel = New-Object -comobject Excel.Application
$wbMacro = $excel.workbooks.open("{Macro Workbook Work}")
$workbook = $excel.workbooks.open("{Export File Work}")
$excel.ScreenUpdating = $false
$excel.DisplayAlerts = $false
$worksheet = $workbook.worksheets.item(1)
$excel.run(“Macros.xlsm!Run”)
$wbMacro.Close()
$workbook.Save()
$workbook.Close()
$excel.Quit()

Hi Nick,

To run a VBA macro, try using the new “Excel command” action available in version 5.1 or above. If the problem persists, please post a screenshot of the error.

HRESULT: 0x800AC472

It will work if there aren’t that many rows of data, however at some point the file is too large where it hangs and fails.

Does the macro fail if you run it from Excel, not from EasyMorph or PowerShell?

Do you have 32-bit Office or 64-bit Office?

How big is the file (rows, mbytes)?

It will run from Excel without any issue. I have 32-bit Office installed. The file is about 14mb with around 66k rows. If I delete rows and keep around 14k rows it will run successfully.

Hi Nick,

Can you please try to add a delay for 5 seconds before closing workbooks like this:

$excel.run(“Macros.xlsm!Run”)
Start-Sleep -s 5
$wbMacro.Close()

Will it give you that error anyway?

Hi Andrew, I had the same thought and did try this even adding a delay up to 1 minute, however it still failed. I also tried adding a delay in the macro itself with no luck. Thanks for the suggestion though.

Is it possible for you to share one of those big files that gave you that error and the macros file with us? If so, you can send them to our support email.

Also, please:

  • Use the “Excel command” action to run the macro and make that the error appear again
  • Go to menu “About” and press “Diagnostic information.” It will open a dialog with diagnostic information.
  • Copy contents of both tabs in that dialog and send it to our support email.

Furthermore, is it possible for you to test that script or/and the “Excel command” action that runs the macro in question on a computer that has 64-bit Excel installed?

Thanks I’ll send the macro and model to support.

Andrew was able to help me resolve this by changing the Server Log On user.

Thank you!

I’m having the exact same issue, could either of you @nicktagz @andrew.rybka post the solution?

image

@tweeg On your EM Server. Change the logon user for the easymorph service to an account that has Excel permissions on that machine.