I have VBA script that allows me to export the contents of a Excel spreadsheet into a CSV file. I have been trying to figure out how to get the VBA command into a PS command and am lost on the parameters. I would like to open the spreadsheet in read-only mode and not see Excel and not prompt me to save changes (as there should be none) when the spreadsheet is closed.
Here is the VBA call that works:
oExcel.Workbooks.Open(InputFile, 0, True, , , , True, , , , False, , False)
from MSDN site, the following are the options for this call:
Workbooks.Open( FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
If I try the following in PS, it throws an error of a missing expression for the blank parameters:
$WorkBook = $Excel.WorkBooks.Open($inputFile, $False, $True,,,,$True,,,,$False,,$False)
If I insert $Null where the missing parameters are, it does not work either.
So, I am not sure how to the blank (optional) values in the procedure call.
I think I have found the answer. Is this correct?
$objMissingValue = [System.Reflection.Missing]::Value
$WorkBook = $Excel.WorkBooks.Open($inputFile, $False, $True,$objMissingValue,$objMissingValue, $objMissingValue,$True,$objMissingValue,$objMissingValue,$objMissingValue, $False,$objMissingValue,$False,$objMissingValue)
This does not generate any errors, but it still asks me to save the file.
$wb = $excel.Workbooks.Open("c:\path...")