none
"Exception calling ".ctor" with "1" argument(s): "Can not open the package. Package is an OLE compound document" RRS feed

  • Question

  • I am trying to read an Excel spreadsheet that gets automatically generated every morning. It is a feed from Remedy. I can open the file manually with no problems, but I want to use the spreadsheet, which has user info in it, for getting said user info. Every time I use the PSExcel module and use Import-XLSX to read the file, I get the above error. It's a head scratcher and the only thing I could find was a reference to C# code. I'm not sure what to do, but it's weird that I can open it manually but not using PS. I haven't tried the ImportExcel module but I'm guessing it would be the same result. Any suggestions would be greatly appreciated.

    Brian

    Tuesday, October 18, 2016 2:41 PM

Answers

  • You need to fix your question title as it is broken.

    $xl=New-Object Excel.Application
    $xl.Workbooks.Open(<path to your xlsx>)

    For issues with the Excel module post in module authors home.


    \_(ツ)_/

    Tuesday, October 18, 2016 4:10 PM
    Moderator
  • $objExcel.ActiveWorkbook.SaveAs("$pwd\testxl.csv", [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)

    \_(ツ)_/

    • Marked as answer by BrianEmbree Monday, October 31, 2016 2:18 PM
    Friday, October 28, 2016 9:35 PM
    Moderator

All replies

  • You need to fix your question title as it is broken.

    $xl=New-Object Excel.Application
    $xl.Workbooks.Open(<path to your xlsx>)

    For issues with the Excel module post in module authors home.


    \_(ツ)_/

    Tuesday, October 18, 2016 4:10 PM
    Moderator
  • I can't use the above solution. I tried the script below but when I try to view the object ($test), I get a bunch of garbage at the beginning and end of file. Any suggestions?

    $xlsfile = "\\ssoremrep1v\Reports\People\Basic_People_Info.xls"
    $csvfile = "Y:\Scripts\ScriptDocs\Basic_People_Info.csv"
    $xlCSV = 6
    
    $objExcel = New-Object -ComObject Excel.Application
    $objExcel.Visible = $false
    $objExcel.DisplayAlerts = $false
    $objExcel.Workbooks.Open($xlsfile)
    $workbook.SaveAs($csvfile)
    $objExcel.Quit()
    if(ps excel) {kill -Name EXCEL}
    
    $test = Import-Csv $csvfile

    Friday, October 28, 2016 7:38 PM
  • You have to save the file as type CSV.

    This is a new question.  Look up the "SaveAs"  method to get all of the parameters.


    \_(ツ)_/

    Friday, October 28, 2016 7:43 PM
    Moderator
  • That is already in my script. The script saves it as a csv and I can manually open it in Excel, but if I output the object ($test), I get a ton of garbage data at the beginning and end of file. This is an OLE Compound Document that is generated by Crystal Reports (I erroneously thought it was from Remedy). Not sure if that helps.
    Friday, October 28, 2016 9:22 PM
  • I'm also getting an error when I manually open the csv file in Excel - "the file format and extension of 'Basic_People_Info.csv' don't match." Hope this gives you more to go on.
    Friday, October 28, 2016 9:24 PM
  • You are saving it as an Excel workbook and not a CSV file.


    \_(ツ)_/

    Friday, October 28, 2016 9:26 PM
    Moderator
  • $objExcel.ActiveWorkbook.SaveAs("$pwd\testxl.csv", [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)

    \_(ツ)_/

    • Marked as answer by BrianEmbree Monday, October 31, 2016 2:18 PM
    Friday, October 28, 2016 9:35 PM
    Moderator
  • Thank you so much! That seemed to do it. Now I don't get all that garbage.
    Monday, October 31, 2016 2:18 PM