Transform a couple of xlsx to csv

Question

on my windows pc I have a folder with a couple of csv files but sometimes there is a xlsx file between.

Later I want to copy all csv files into one, so that I can load it into a DB. But for that I need to transform the xlsx files also into csv. I do not want to open all separately. Is there a way to do it automatically? I tried to create a macro in Excel but I didn't know how to apply that to all xlsx files.

Thanks for your help!

you could take a look at this: [StackOverflow: Code-for-looping-through-all-excel-files-in-a-specified-folder](http://stackoverflow.com/questions/5851531/code-for-looping-through-all-excel-files-in-a-specified-folder-and-pulling-data) and modify the code to only open files that have an `xlsx` extension — Our Man in Bananas, Jul 03 '13 at 15:22
I checked that out.. it was a good tip, even if I didnt get it working ;) If you want to create a answer I can vote it up. — cruxi, Jul 05 '13 at 11:38

score 1 · Accepted Answer · answered Jul 05 '13 at 13:43

try the FileSystemObject approach

strPath = "C:\PATH_TO_YOUR_FOLDER"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xls" Then
   Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
   ' Include your code to work with the Excel object here
   objWorkbook.Close True 'Save changes
End If

Next

objExcel.Quit

that should get u started.

Remember, after version 2003, Excel's Application.FileSearch is deprecated, so FileSystemObject approach is better

Transform a couple of xlsx to csv

1 Answers1