Reputation: 412
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
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