CODEWITHSUNDEEP
excelvbapowershell
BenderIO
BenderIO

Reputation: 412

"Method 'Open' of object 'Workbooks' failed" in VBA when launched from a Powershell, but work when directly call from Excel

I'm stuck on this problem. An user will go to holiday and want to automate some actions on his Excel. I've created a very basic Powershell for the Windows Task Scheduler that open a first Excel file, launch a macro and close:

$target = "G:\ES\GI\xxxx\xxxxMailer.xlsm" #Where is my file
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$workbook = $objExcel.Workbooks.Open($target) #Open my file
$objExcel.Run("Callxxxx") #Launch a macro in the target file
$workbook.close($false)
$objExcel.Quit()
[Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)

In the macro I have this very simple line (so in VBA this time) for opening an other workbook:

Workbooks.Open Filename:="G:\ES\GI\xxxxx\destinations xxxxxx 2018.xlsm", _
    UpdateLinks:=3, _
    ReadOnly:= False

My problem is the following: when I launch the macro directly from the first workbook, the second one open without any problem; but when I try to launch the process from Powershell, I got the error "Method 'Open' of object 'Workbooks' failed".

I tried to change all properties from Workbooks.Open, disable macro at StartUp from the destination file, same process work with others files and not this one. I'm short on ideas.

Upvotes: 0

Views: 842

Answers (1)

BenderIO
BenderIO

Reputation: 412

The problem came from specific addin in the targetted workbook. The $ObjExcel don't run with them loaded correctly. I finally assign the $ObjModel to the full Excel path containing all addins, and not to a "-New-Object" who's running on separate instance. Everything working fine after that.

Upvotes: 1

Related Questions