VBA - open xls, xlsx files, export worksheets to csv

Question

I am using 2 different sets of code I found here to accomplish the following task:

Take all xls, xlsx files in a designated directory (usually 4 files) and export the data in their worksheets to separate csv files.

One set of code loops through a directory and finds xls files; this works perfectly. The other set of code takes the currently open worksheet and exports it to a csv, this works perfectly too. But when I try to convert a file that's being looped through using the first set of code, I get errors depending on what I modify.

I think the objects I am creating aren't the right type so they can't be looped through, but I don't know how to create the right kind of object.

Sub select_rows()

strPath = "C:\temp\xldev"
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" Or objFso.GetExtensionName(objFile.Path) = "xlsx") Then
   Set objWorkbook = objExcel.Workbooks.Open(objFile.Path, ReadOnly)
   ' Include your code to work with the Excel object here

   Dim WS As Excel.Worksheet
   Dim SaveToDirectory As String

   Dim CurrentWorkbook As String
   Dim CurrentFormat As Long

   ' CurrentWorkbook = objWorkbook
   ' CurrentFormat = objFile.FileFormat
   ' Store current details for the workbook
   SaveToDirectory = "C:\temp\"

   For Each WS In objWorkbook.Worksheets
    Sheets(WS.Name).Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
 Next

Application.DisplayAlerts = False
objWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True
' objWorkbook.Close True 'Save changes
End If
Next
objExcel.Quit

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

This might be your problem

Convert xls / xlsx files (all sheets) to csv using VBScript

You are using the Workbook object to save a Worksheet. Try using the Worksheet object.

edited May 23 '17 at 12:29

Community

1
1

answered Jun 23 '14 at 14:23

le Père Mersenne

56
2

Thank you. I will try that solution. This was easier to do in Python but Python converts the sheets to UTF-8 which doesn't work for me. – Tensigh Jun 24 '14 at 03:00

score 1 · Answer 2 · answered Aug 14 '15 at 19:03

Try this:

Dim objExcel
Dim objWorkBook
Dim strPath 
Const xlCSV = 6
strPath = "C:\temp\filename"
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open(FileName:=strPath & ".xlsx")
On Error Resume Next
objExcel.DisplayAlerts=False
With objWorkbook
     .RefreshAll
     .Saveas FileName:=strPath & ".csv", FileFormat:=xlCSV, Local:=True
     .Close SaveChanges:=False
objExcel.DisplayAlerts=True
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing

VBA - open xls, xlsx files, export worksheets to csv

2 Answers2