xoxotmc
09-12-2013, 07:42 PM
I have a buggy, non-functional VBA script to batch convert ALL .csv files in a folder to .xlsx files. I attempted to specify that the delimiter is a "Comma" to move each value in the sheet into its own cell. I tried to modify a script I found on here that opens all files in a specified folder and SaveAs an .xls files, but the values are still comma separated. No success even though I've poured over this forum and googled everything I could think of for the past three days. Would love to have this menial, awful task automated since I have thousands of files to convert.
My goal is to: Convert .csv files into .xlsx files specifying that the delimiters are Tabs & Commas.
Here is my current script that isn't working for me. Any suggestions or nudges in the right direction are greatly appreciated. Also, i'm very new to VBA and naively thought this was a pretty simple task... Man, was I wrong.
Sub csvtoxls()
Dim wb As Workbook
Dim strFile As String, strDir As String
strDir = "C:\Documents and Settings\Tiana\Desktop\t=0 TEST"
strFile = Dir(strDir & "*.csv")
Do While strFile <> ""
Set wb = Workbooks.OpenText(strDir & strFile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True)
With wb
ActiveWorkbook.SaveAs Replace(wb.FullName, ".csv", ".xls"), 50, CreateBackup:=False
ActiveWorkbook.Close True
End With
Set wb = Nothing
strFile = Dir
Loop
End Sub
My goal is to: Convert .csv files into .xlsx files specifying that the delimiters are Tabs & Commas.
Here is my current script that isn't working for me. Any suggestions or nudges in the right direction are greatly appreciated. Also, i'm very new to VBA and naively thought this was a pretty simple task... Man, was I wrong.
Sub csvtoxls()
Dim wb As Workbook
Dim strFile As String, strDir As String
strDir = "C:\Documents and Settings\Tiana\Desktop\t=0 TEST"
strFile = Dir(strDir & "*.csv")
Do While strFile <> ""
Set wb = Workbooks.OpenText(strDir & strFile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True)
With wb
ActiveWorkbook.SaveAs Replace(wb.FullName, ".csv", ".xls"), 50, CreateBackup:=False
ActiveWorkbook.Close True
End With
Set wb = Nothing
strFile = Dir
Loop
End Sub