Is it possible to batch convert csv to xls using a macro?
Solution 1
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.
Solution 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.
Solution 3
The Code of Scott Holtzman nearly did it for me. I had to make two changes to get it to work:
-
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
-
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)
Comments
-
Alistair Weir almost 2 years
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