Using powershell to convert csv file to excel

Question

I'm running a PowerShell script that converts a CSV file to Excel. When I run it manually it works fine, but when I run it through a scheduled task it fails. Here is the error I'm getting:

Exception calling "Open" with "1" argument(s): "Open method of Workbooks class failed" At C:\PowerShell\Weekly\WeeklyReport.ps1:337 char:34 + $workbook = $excel.Workbooks.Open <<<< ($csvFilePath) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

I'm using the same account in the scheduled task as I am when logged in. I also had the scheduled task use the local administrator account, but still had the same issue. What I'm actually launching is a VB script, which in turn launches a .bat file, which lists out a few PowerShell scripts. The VB script is used to hide the window so it doesn't show up when it's running. If I manually run the VB script it runs fine, but when running it through scheduled task it fails with the error I listed. I'm using Windows Server 2008 and I have Office 2003 installed. Here is the portion of the PowerShell script that is failing:

   $excel = New-Object -ComObject excel.application 
   $excel.visible = $False 
   $excel.displayalerts=$False 
   $workbook = $excel.Workbooks.Open($csvFilePath) 
   $workSheet = $workbook.worksheets.Item(1)

It's failing on the line

$workbook = $excel.Workbooks.Open($csvFilePath)

Any help would be greatly appreciated. Thank you!

Is $csvFilePath pointing to a local drive or a mapped drive? — Tony Hinkle
– Tony Hinkle, Commented May 28, 2015 at 20:35

Bugsta · Accepted Answer · 2015-05-28 23:30:29Z

1

Okay so I found my problem. I went into dcomcnfg. Since my application "Excel" was 32bit and I was running on a 64bit machine I had to lunch the 32 bit version of it:

c:\Windows\SysWOW64>mmc comexp.msc /32

Once I was in there I went into Component Services -> Computers -> My Computer -> DCOM Config. In that list I fond Microsoft Excel Application and went into properties. In the security tab I hit the radio box for "Customize" next to each one and hit the edit button for each one and added the user account I was lunching the scheduled task with. Full access to everything. Under "Identity" tab I hit the radio circle for "This user" and again put the information for the user I was running the task with. I probably didn't need to do all of that, I'm sure it only took one thing to fix the problem, but I wasn't sure which one to do so I did them all. And it worked.

answered May 28, 2015 at 23:30

Bugsta

511 gold badge2 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincent De Smet Over a year ago

you could've tested $env:PROCESSOR_ARCHITECTURE in you script and if it wasn't 32 bit, re-launched your script with %windir%\SysWOW64\WindowsPowerShell\v1.0\powershell.exe

Collectives™ on Stack Overflow

Using powershell to convert csv file to excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related