Loading CSV file programmatically into Excel

Question

Hello,

I have the need inside a macro to load a CSV file. All my attempts (Workbooks.Open or OpenText) resulted in the CSV file itself loaded in a single column instead of having all CSV fields loaded into separate columnus.

Do I have to parse myself each line and fill the cells ? Excel can load the CSV file when I simply open it manually, so I guess it can be done programmatically.

Any help appreciated.

---

-- Francois Piette

Answer 1

Try the below

Sub Macro1()

Dim wb As Workbook, strFile As String

strFile = "c:\1.txt"

Set wb = Workbooks.Add

With wb.Sheets(1).QueryTables.Add(Connection:="TEXT;" & strFile, _

Destination:=wb.Sheets(1).Range("A1"))

    .TextFileParseType = xlDelimited

    .TextFileOtherDelimiter = ";"

    .Refresh

End With

End Sub

Answer 2

This works fine exactly as I wanted.

Thanks a lot.

---

-- Francois Piette

Answer 3

This is something I already tested. But I have done it again to be sure. I replaced "Comma:=True" by "Semicolon:=True" since my file has semicolon as separator.

It doesn't work. As I said before, Everything is loaded in the first column.

Thanks anyway.

---

-- Francois Piette

Answer 4

Try the below.

Workbooks.OpenText Filename:="C:\test.csv", StartRow:=1, _

DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True