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

Hello,

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.

 

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,
                        $objMissingValue,$True,$objMissingValue,$objMissingValue,$objMissingValue,
                        $False,$objMissingValue,$False,$objMissingValue)

 

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

Top 10 Contributor
Posts 636
Microsoft MVP
Top Contributor

try:

$excel.workbooks.Open("c:\file..")

 

Top 10 Contributor
Posts 636
Microsoft MVP
Top Contributor

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

and later....:

 

$wb.Save()

$wb.Close()

$excel.Quit()

 

 

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