i All. I have a script that brings in an excel workbook and exports to CSV. Problem is, i want this to hit multiple workbooks in excel, starting from
$oWorksheet = $oExcelDoc.Worksheets.item
(7)
And going to the end
How can i achive this? Code below
$sExcelFile="C:\WindowsPatching_Reference_2016.xlsx"
$sCSVFile="C:\temp\$wksname.csv"
#$SheetName = "WinA-Test_Thu"
# Get COM Object
$oExcel = New-Object -ComObject "Excel.Application"
# Should Excel be visible?
$oExcel.Visible = $true
# and open excel file
$oExcelDoc = $oExcel.Workbooks.Open($sExcelFile)
# Open Worksheet
#$oWorkSheet = $oExcelDoc.sheets.item($SheetName)
$oWorksheet = $oExcelDoc.Worksheets.item(7)
#Get Sheet Name
$Name = $oworksheet.name
# Activate, show it
$oWorksheet.Activate()
$oExcelDoc.SaveAs($sCSVFile,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)
$oExcelDoc.Close($false)
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
$oWorksheet=$null
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
$oExcelDoc=$null
# Close Excel
$oExcel.Quit()
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
$oExcel=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
Select all Open in new window
if your code is currently working, then the following loop should then export all sheets until the end starting with sheet number 7:
Open in new window
I just added the counter to the filename, otherwise the file would always be overwritten.
HTH
Rainer