Convert multiple Excel Documents to CSV using Powershell

Looking for a Powershell solution to convert all Excel documents in a specific folder to CSV files.  Also it needs to convert a specific worksheet within all the Excel documents.  All the Excel documents are formatted exactly the same.  I found this solution but it does not convert a specific worksheet (see below).  It only converts the last opened worksheet.  

https://gallery.technet.microsoft.com/office/How-to-convert-Excel-xlsx-d9521619#content

Anyone have this Powershell solution?  I prefer a solution that does not require Excel installed but any solution would be helpful.
Tony EllisIT ManagerAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Try this, change the folder C:\Test\ to the folder you workbooks are in.
$Excel = New-Object -ComObject Excel.Application
$Location = "C:\Test\"     # Path to read files
$SheetName = "Outbound"
$files = Get-ChildItem C:\Test -Filter "*.xls*"

ForEach ($file in $files)
{
    # Save Sheet1 in each workbook as CSV in same folder
       
     $WorkBook = $Excel.Workbooks.Open($Location+$file)
     
     $NewName =$WorkBook.Name.Split(".")[0] + ".csv "

     Write $NewName

     $WorkSheet = $WorkBook.WorkSheets($SheetName)

     $WorkSheet.Copy()

     $NewBook = $Excel.ActiveWorkbook

     $WorkBook.Close()

     $NewBook.SaveAs($Location+$NewName,6)

     $NewBook.Close()
 
}


$Excel.Quit()

Stop-Process -processname EXCEL

Open in new window

0
 
NorieVBA ExpertCommented:
Tony

Is the same worksheet in each workbook you want to save as a CSV?

Where would the new file be saved and what would it be named?
0
 
Tony EllisIT ManagerAuthor Commented:
Yes all the worksheets are named the same in each Excel document.  Just the data is different.  The new file name will be same as the original Excel document (not the worksheet name/label).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
Tony

What's the name of the worksheet you want to save as a CSV?
0
 
Tony EllisIT ManagerAuthor Commented:
Outbound
0
 
Tony EllisIT ManagerAuthor Commented:
Brilliant!!!  It worked perfectly.  Thank you Norie.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.