0

I am saving OGN.xlsx file as jeeva.csv. The below code runs for the first time and creates jeeva.csv.

PS C:\Users\Jeevananda_R> $objExcel=New-Object -ComObject Excel.Application
PS C:\Users\Jeevananda_R> $workbook=$objExcel.Workbooks.Open('D:\OGN.xlsx')
PS C:\Users\Jeevananda_R> $workbook.SaveAs('D:\jeeva.csv',6)

File jeeva.csv created

When i run the same again:

PS C:\Users\Jeevananda_R> $objExcel=New-Object -ComObject Excel.Application
PS C:\Users\Jeevananda_R> $workbook=$objExcel.Workbooks.Open('D:\OGNPL300.xlsx')
PS C:\Users\Jeevananda_R> $workbook.SaveAs('D:\jeeva.csv',6)

I get this error:

Exception calling "SaveAs" with "2" argument(s): "Cannot access 'jeeva.csv'." At line:1 char:1 + $workbook.SaveAs('D:\jeeva.csv',6) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

Jeevananda R
  • 9
  • 1
  • Editing the second command in rerun , this is what run PS C:\Users\Jeevananda_R> $workbook=$objExcel.Workbooks.Open('D:\OGN.xlsx') – Jeevananda R Jul 05 '18 at 12:24
  • Just because you saved the workbook it does not mean you closed it. – Matt Jul 05 '18 at 12:26
  • How to close that file? – Jeevananda R Jul 05 '18 at 12:26
  • I did this $workbook.Close() still same – Jeevananda R Jul 05 '18 at 12:27
  • Excel is fun like that. Have a look https://stackoverflow.com/questions/27798567/powershell-excel-save-and-close-after-run – Matt Jul 05 '18 at 12:33
  • PS C:\Users\Jeevananda_R> $workbook.Close($false) PS C:\Users\Jeevananda_R> $excel.Quit() You cannot call a method on a null-valued expression. At line:1 char:1 + $excel.Quit() + ~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull PS C:\Users\Jeevananda_R> $Excel.Quit() You cannot call a method on a null-valued expression. At line:1 char:1 + $Excel.Quit() + ~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull – Jeevananda R Jul 05 '18 at 12:36
  • $excel.Quit() and $Excel.Quit() didnt work, tried this $Excel.Quit executed ok.but re ran the three commands again and same error – Jeevananda R Jul 05 '18 at 12:38
  • You do not have a variable called `$excel` in your sample code above you called it `$objExcel`... – Matt Jul 05 '18 at 12:38
  • PS C:\Users\Jeevananda_R> $objExcel.Quit OverloadDefinitions ------------------- void Quit() void _Application.Quit() PS C:\Users\Jeevananda_R> $objExcel.Quit() ran and same again – Jeevananda R Jul 05 '18 at 12:40
  • Please stop putting errors and code in comments. Edit your question with all the additional information. – EBGreen Jul 05 '18 at 12:47

1 Answers1

0

This works for me

$objExcel=New-Object -ComObject Excel.Application
$objExcel.displayAlerts = $false
$workbook=$objExcel.Workbooks.Open('C:\test\test.xlsx')
$workbook.SaveAs('c:\test\new.csv',6) 
$workbook.close
[void]$objExcel.Quit()
Simon B
  • 210
  • 1
  • 5
  • Can you rerun the same first three commands again, because i copy pasted you code getting same error , work for first run but for the second time faile with same error – Jeevananda R Jul 05 '18 at 12:49
  • I dont get any errors if running the above in either the console or the ISE – Simon B Jul 05 '18 at 12:52
  • Windows powershell , in windows search i did a search for powershell and promt came up. Win 8.1 32 bit – Jeevananda R Jul 05 '18 at 12:54
  • Try ending all of your excel prossess first then try the code I posted again (edit the file paths ) – Simon B Jul 05 '18 at 12:57
  • 2
    I also suggest to release the Excel com object after quitting Excel: `[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null ; [System.GC]::Collect() ; [System.GC]::WaitForPendingFinalizers()` – Theo Jul 05 '18 at 13:06
  • The answer does not show you using the com object to save _another_ file using the same output name. – Matt Jul 05 '18 at 13:22
  • Matt, if you comment out line 2 of my code you will be prompted to overwrite the file the second time you run it. – Simon B Jul 05 '18 at 13:26
  • @Theo Don't forget to collect the `$workbook` object as well. – Maximilian Burszley Jul 05 '18 at 14:48