Is it possible to batch convert csv to xls using a

Question

I have a large amount of csv files that I need in .xls format. Is it possible to run a batch conversion with a macro or best done with another language?

I have used this code http://www.ozgrid.com/forum/showthread.php?t=71409&p=369573#post369573 to reference my directory but I'm not sure of the command to open each file and save them. Here's what I have:

Sub batchconvertcsvxls()
    Dim wb As Workbook
    Dim CSVCount As Integer
    Dim myVar As String

    myVar = FileList("C:\Documents and Settings\alistairw\My Documents\csvxlstest")
    For i = LBound(myVar) To UBound(myVar)

        With wb

            Application.Workbooks.OpenText 'How do I reference the myvar string ?
            wb.SaveAs '....

        End With

    Next
End Sub

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = Split("No files found", "|") 'ensures an array is returned
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
    Loop
    FileList = Split(sTemp, "|")
End Function

Edit: The files are .txt files formatted as csv

Answer 1

The Code of Scott Holtzman nearly did it for me. I had to make two changes to get it to work:

1. He forgot to add the line that makes our loop continue with the next file. The last line before the Loop should read

   strFile = Dir

2. The Workbooks.Open method did not read my CSV files as expected (the whole line ended up to be text in the first cell). When I added the parameter Local:=True it worked:

   Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)

Answer 2

By combining the code given by Scott Holtzman and 'ExcelFreak', the conversion works quite well. The final code looks something like this:

Sub CSV_to_XLS()

Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "U:\path\"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

    Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
    wb.SaveAs Replace(wb.FullName, ".csv", ".xls"), 50 'UPDATE:
    wb.Close True

    Set wb = Nothing
    strFile = Dir
Loop

End Sub

Opening the converted .xls file throws a warning everytime:

"The file you are trying to open, 'filename', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

Clicking Yes then opens the .xls file.

Is there a way to get rid of this warning message? Excel throws a warning everytime the .xls file is opened.

Answer 3

This works properly at least on Excel 2013. Using FileFormat:=xlExcel8 parameter instead of the filetype tag 50 creates files that open without security nags.

Sub CSV_to_XLS()

Dim wb As Workbook Dim strFile As String, strDir As String

strDir = "C:\temp\" strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
wb.SaveAs Replace(wb.FullName, ".csv", ".xls"), FileFormat:=xlExcel8
wb.Close True

Set wb = Nothing
strFile = Dir

Loop

End Sub

Answer 4

This was a good question and I have found in the internet several answers. Just making very small changes (I couldn't edit any of the codes already published) I could make things work a bit better:

Sub CSV_to_XLSX()

Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "C:\Users\acer\OneDrive\Doctorado\Study 1\data\Retest Bkp\Day 1\Sart\"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

    Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
    With wb
        .SaveAs Replace(wb.FullName, ".csv", ".xlsx"), 51
        .Close True
    End With
    Set wb = Nothing
    strFile = Dir
Loop

End Sub

Answer 5

In a lot less lines of code, this should get you what you want. However, I will say this may not be the fastest way to get it done, because you are opening, saving, and closing the workbook every time. I will look for a faster way, but I forget the method off the top of my head.

Sub batchconvertcsvxls()

Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "C:\"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

    Set wb = Workbooks.Open(strDir & strFile)
    With wb
        .SaveAs Replace(wb.FullName, ".csv", ".xls"), 50 'UPDATE:
        .Close True
    End With
    Set wb = Nothing
Loop

End Sub

** UPDATE ** you need the proper fileformat enumeration for a .xls file. I think its 50, but you can check here Excel File Type Enumeration, if it's not.