Comma delimiters - Batch conversion of .csv to .xlsx

[xoxotmc]

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

[snb]

What happens with:

Sub M_snb()
  sn=split(createobject("wscript.shell").exec("cmd /c Dir ""C:\Documents and Settings\Tiana\Desktop\t=0 TEST\*.csv"" /b").stdout.readall,vbcrlf)

  for each it in sn
    with workbooks.open(it)
      .saveas replace(it.name,".csv",".xlsx"),51
      .close 0
    end with
  next
End Sub

PS. Does the first line in each csv file contain headernames, separated by commas ?