VBA - How to convert xls, xlsx files in a directory to csv

[Tensigh]

I'm trying to write code in VBA in Excel that opens a desginated directory, finds all xls and xlsx files and then converts them to csv. I found two sets of code that do one or the other perfectly, but I can't make it work.

I'm sure I'm just missing one step but the more I tweak the code the less it works:

Sub select_rows()

strPath = "C:\temp\pydev"

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
   MsgBox (objFile.Name) ' this is just scaffolding to make sure it opens the right files.
   Dim WS As Excel.Worksheet
   Dim SaveToDirectory As String

   Dim CurrentWorkbook As String
   Dim CurrentFormat As Long

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

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

Application.DisplayAlerts = False
' ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True




   ' objWorkbook.Close True 'Save changes
End If

Next

objExcel.Quit
End Sub

[Stuugie]

Hi Tensigh,

I've reworked what you have a bit, try the following and let me know if it comes close to what your requirements are:

Sub SaveToCSVs()

    Dim fDir As String
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim fPath As String
    Dim sPath As String

    fPath = "C:\temp\pydev\"
    sPath = "C:\temp\"
    fDir = Dir(fPath)
    Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then
            On Error Resume Next
            Set wB = Workbooks.Open(fPath & fDir)
            For Each wS In wB.Sheets
                wS.SaveAs sPath & wS.Name, xlCSV

            Next wS
            wB.Close False
            Set wB = Nothing
        End If
        fDir = Dir
        On Error GoTo 0
    Loop

End Sub

[Tensigh]

Stuugie, thank you! That's almost perfect. For me, I just need to change two parts:

1. Will it work if I add this to the beginning to prevent the user from seeing the Excel sheets as they open and close?

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

If not, I can live with it, but it would be better if the user doesn't see what's going on.

1. I just need to change the file names because some of these files have "Sheet1" as the worksheet name so they overwrite each other:

(line 17 above)
wS.SaveAs sPath & wb.name & wS.Name, xlCSV

Other than that, your code has been the cleanest and the clearest of any of the examples I've seen, thank you so much!

[Stuugie]

Hi Tensigh,

sorry about not adding in the lines that prevent the user from seeing the workbook and yes it should work fine if you add them. As for the sheet and or file name when the csv is saved, how would you want to go about selecting a file name? I ask because, if you don't want user interaction, then you don't want the user to be selecting the file name and therefore you'll have to make it so that the sheets have names, for which my code will work without issue. If you want the user to be in charge of naming the file, I can add some code to the procedure to do so. I'll leave that to you. Or, maybe you have another way that you want the names for the files to be selected?

[Tensigh]

I plan on having the workbook name be the first part of the file name. For example:

Workbook1-sheet1.csv
Workbook2-sheet1.csv
Workbook2-sheet2.csv
Workbook3-sheet1.csv

and so on. I have about 4 Excel files that have to be converted each month (each one contains data from convenience stores). One of them combines two cvs chains in separate worksheets, the rest put all data on a single worksheet.

I think if I use the line above it should work, something like:

wS.SaveAs sPath & wb.name & "-" & wS.Name, xlCSV

[Stuugie]

That looks good to me.

[Tensigh]

Okay, I have 2 questions. This works pretty well with one minor exception. If there are multiple sheets in a workbook, the file appends the previous sheets to the filename. For example, using the example listed above:

Workbook1-sheet1.csv
Workbook2-sheet1.csv
Workbook2-sheet2.csv
Workbook3-sheet1.csv

What I get for the second sheet in Workbook 2 ends up like this:
Workbook1-sheet1.csv
Workbook2-sheet1.csv
Workbook2-sheet2-sheet1.csv
Workbook3-sheet1.csv

Also, it doesn't append the ".csv" unless I add it like this:

wS.SaveAs sPath & wB.Name & "-" & wS.Name & ".csv", xlCSV

[Stuugie]

Hi Tensigh,

Try the following but do not forget to add your file hiding code at the top:

Sub SaveToCSVs()

    Dim fDir As String
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim csvWs As String, csvWb As String
    Dim extFlag As Long '0 = .xls & 1 = .xlsx extension types
    Dim fPath As String
    Dim sPath As String, dd() As String

    fPath = "C:\Documents and Settings\mvasas\Desktop\VBA\Dev\_My_Projects\"
    sPath = "C:\Documents and Settings\mvasas\Desktop\VBA\Dev\_My_Projects\"
    fDir = Dir(fPath)
    extFlag = 2

    Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Then
            extFlag = 0
        Else
            extFlag = 2
        End If
        If Right(fDir, 5) = ".xlsx" Then
            extFlag = 1
        Else
            extFlag = 2
        End If
        On Error Resume Next
        If extFlag = 2 Then
            fDir = Dir
        ElseIf extFlag = 1 Then
            Set wB = Workbooks.Open(fPath & fDir)
            csvWb = wB.Name
            'Be careful here, this split will split a string into an array
            'with a dot (.) delimeter. The string is the name of the workbook
            'testing was performed on workbooks where the only dots were at
            'the extension ie: CSVSAVERTESTFILE.xlsx.  If there is a file with
            'a name like, CSV.SAVER.TEST.FILE.xlsx, the file will be renamed:
            'CSV.Sheet1.csv as the code takes the first String value in the
            'array as the new name.
            dd = Split(csvWb, ".")
            For Each wS In wB.Sheets
                wS.SaveAs sPath & dd(0) & "-" & wS.Name & ".csv", xlCSV
            Next wS
            wB.Close False
            Set wB = Nothing
            fDir = Dir
            On Error GoTo 0
        End If
    Loop

End Sub

[Tensigh]

Stuugie, you're the best. One thing I can control is the name of the Excel files when they get stored in the original directory. If I tell the user to name the files as Workbook1.xls, Workbook2.xls, etc, then it will work. I'll try this at work tomorrow.

The split function in VBA works almost exactly like it does in Python.

[Stuugie]

I can create a function that checks the workbook name, thoroughly, and excludes only the extension but maximizing code efficiency will become an issue. I would basically create a function that performs the split and then checks each string in the array for the extension type and then concatenate all the strings back together that do not have the extension type. However, if you stamp down the law regarding naming conventions, as you say you can/will, than I don't see a need to do that.

And yes, the split is awesome and very similar to many syntaxes out there in the coding world.

[Tensigh]

Stuugie, thanks again. I had to make one mod to the code. For some reason, the code you listed above worked on xlsx files but not xls, so I modd'd the code to read this:

 Do While (fDir <> "")
        If (Right(fDir, 5) = ".xlsx" Or Right(fDir, 4) = ".xls") Then

and it works like a charm! Thanks again!

[Stuugie]

No problem Tensigh, I'm glad to have helped.

[Summer_1]

Please help! I added this to my vb script and updated my directories in SSIS but I'm getting an error that on this line --> "wb = Workbooks.Open(fPath & fDir)" **"reference to a non shared member requires an object reference" **
What am I doing wrong or missing? How do I reference these to match?

[Tensigh]

I'm not really an expert on VBA but I'll try to help.

Do you have the line:
Dim wB As Workbook
somewhere in the code? If I understand the error correctly, it's saying that you're trying to access the wb object without having it created as an object.

[Summer_1]

Yes I did as described about and changed my directory but gets that error which doesn't allow it to run.

[Tensigh]

Silly question (remember I'm not an expert on VBA) but are there any .xls or .xlsx files in the directory?

The only other thing I can think of is that there are 3 variables at play here, all of which need to be declared: fdir, fpath and wb. As long as all three of those are declared it should work. Plus the directory should have read/write permissions by the user running the script.

[Summer_1]

I understand. I've done everything I think could help including adding all the references yet the Robles persists. I wish someone can come to my aide soonest.

[Summer_1]

I understand. I've done everything I think could help including adding all the references yet the Robles persists. I wish someone can come to my aide soonest.

[Tensigh]

Can you paste the code? I can't think of anything else without seeing the code.

[Tensigh]

According to this:

http://msdn.microsoft.com/en-us/library/zwwhc0d0(v=vs.90).aspx

it basically says that that error means you're referencing a non-shared item (whatever that is) without an object reference. Since opening an Excel workbook requires a reference I have the line Dim wB As Workbook. In my code, I tell it set wb = Workbooks.Open(fPath & fDir) so it tells Excel to open a Workbook object.

Your error message doesn't have the word set (see line 31). Is it in your code?

[Summer_1]

Thanks for your response. The word "set" deletes itself once I type it. Do you know what the issue is here? Am I missing something that causes this to happen?

[Summer_1]

Here is the code:
Public Sub SaveToCSVs()
Dim fDir As String
Dim wb As Excel.Workbook
Dim wS As Worksheet
Dim csvWs As String, csvWb As String
Dim extFlag As Long '0 = .xls & 1 = .xlsx extension types
Dim fPath As String
Dim sPath As String, dd() As String

    fPath = "C:\Data_Loads"
    sPath = "C:\Data_Loads"
    fDir = Dir(fPath)
    extFlag = 2
    Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Then
            extFlag = 0
        Else
            extFlag = 2
        End If
        If Right(fDir, 5) = ".xlsx" Then
            extFlag = 1
        Else
            extFlag = 2
        End If
        On Error Resume Next
        If extFlag = 2 Then
            fDir = Dir()
        ElseIf extFlag = 1 Then
            wb = Workbooks.Open(fPath & fDir)
            csvWb = wb.Name
            'Be careful here, this split will split a string into an array
            'with a dot (.) delimeter. The string is the name of the workbook
            'testing was performed on workbooks where the only dots were at
            'the extension ie: CSVSAVERTESTFILE.xlsx.  If there is a file with
            'a name like, CSV.SAVER.TEST.FILE.xlsx, the file will be renamed:
            'CSV.Sheet1.csv as the code takes the first String value in the
            'array as the new name.
            dd = Split(csvWb, ".")
            For Each wS In wb.Sheets
                wS.SaveAs(sPath & dd(0) & "-" & wS.Name & ".csv", xlCSV)
            Next wS
            wb.Close(False)
            wb = Nothing
            fDir = Dir()
            On Error GoTo 0
        End If
    Loop
End Sub

[Stuugie]

I think Tensigh got it on the last post.
@Summer, you had stated your code line to be:

wb = Workbooks.Open(fPath & fDir)

While it should be

Set wb = Workbooks.Open(fPath & fDir)

as Tensigh suggested.

When dealing with workbook variables, sheet variables, or range/cell variables, you need to Set them all to equal something.

[Stuugie]

The workd set deletes itself...

Really? I've never heard of that happening before. Where are you applying this code exactly? Is it in Excel's VBE(Visual Basic Editor)?

[Tensigh]

There are 2 differences:
1. Yours is a public sub
2. It looks like you're not doing this in Excel (guessing here)
(I'm not sure if any of these make a difference, it was just the first thing I noticed)
I'm not sure why it's removing the set keyword. I'm sure that's significant but I don't know why it's happening.

Here is my final version of the code that worked:

Sub SaveToCSVs()

    Dim fDir As String
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim csvWs As String, csvWb As String
    Dim extFlag As Long '0 = .xls & 1 = .xlsx extension types
    Dim fPath As String
    Dim sPath As String, dd() As String
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    objExcel.DisplayAlerts = False

    fPath = "C:\HP\Source\"
    sPath = "C:\HP\WS\"
    fDir = Dir(fPath)
    extFlag = 2
    Do While (fDir <> "")
        If (Right(fDir, 5) = ".xlsx" Or Right(fDir, 4) = ".xls") Then
            extFlag = 1
        Else
            extFlag = 2
        End If
        On Error Resume Next
        If extFlag = 2 Then
            fDir = Dir
        ElseIf extFlag = 1 Then
            Set wB = Workbooks.Open(fPath & fDir)
            csvWb = wB.Name
            dd = Split(csvWb, ".")
            For Each wS In wB.Sheets
                wS.SaveAs sPath & dd(0) & "-" & wS.Name & ".csv", xlCSV
            Next wS
            wB.Close False
            Set wB = Nothing
            fDir = Dir
            On Error GoTo 0
        End If
    Loop

End Sub

[Summer_1]

I'm doing this inside a "Script Take Studio" in SSIS

[Summer_1]

Sorry, I'm doing this inside a "Script Task Studio" in SSIS

[Stuugie]

Hi Summer 1,

Perhaps you should post this question in a database related forum, located around here. This thread is dealing with Excel VBA.

[Summer_1]

Interesting. This is what I found regarding the "Set" issue;

Set is a keyword in VB6, with the intrudction of VB.NET the keyword, as used in this context, was removed.

Formerly, Set was used to indicate that an object reference was being assigned (Let was the default). Because default properties no longer are supported unless they accept parameters, these statements have been removed

[mugua]

I have a trouble, then the result is that the excel is convered some sheet.csv,now I want the result is one csv which contain all sheets of excel, can you tell me how to do?