Excel Spreadsheet Export

rated by 0 users
This post has 3 Replies | 1 Follower

Not Ranked
Posts 4
charles05663 Posted: 05-16-2011 3:40 PM


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:

        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.


Thank you...



Not Ranked
Posts 4

I think I have found the answer.  Is this correct?


$objMissingValue = [System.Reflection.Missing]::Value

$WorkBook  = $Excel.WorkBooks.Open($inputFile, $False, $True,$objMissingValue,$objMissingValue,


This does not generate any errors, but it still asks me to save the file.

Top 10 Contributor
Posts 654
Microsoft MVP
Top Contributor




Top 10 Contributor
Posts 654
Microsoft MVP
Top Contributor

$wb = $excel.Workbooks.Open("c:\path...")

and later....:







Page 1 of 1 (4 items) | RSS
Copyright 2012 PowerShell.com. All rights reserved.