Converting a csv file to Excel (with columns as text) using VBA

Question

I have a csv file, for example with data as below (a.csv) to convert into Excel.

In the second column the leading 0s are truncated.

Is there a way to define the second column as text before importing the csv into Excel?

I dont want to append "`" or any characters to the second column as suggested in many solutions.

a.csv
--------
123,001232323
456,004567772

I have the code below.

srccsvfile = "C:\a.csv"
tgtxlsfile = "C:\a.xls"

'Instantiate Excel
Set objExcel = CreateObject("Excel.Application")

'open the CSV file
Set objWorkBook = objExcel.Workbooks.open(srccsvfile)

Set objWorksheet1 = objWorkbook.Worksheets(1)

objWorksheet1.Columns("A:I").AutoFit
objWorksheet1.Columns("B").NumberFormat="@"

'Save the file as excel workbook
objWorkBook.SaveAs tgtxlsfile, 39

'close workbook
objWorkBook.Close False
'quit excel
objExcel.Quit

'clean up
Set objWorkBook = Nothing
Set objExcel = Nothing

i think this link will help you http://www.vbaexcel.eu/vba-macro-code/extract-get-data-from-mysql-php#yvComment11 — Ranjit Kumar, Nov 05 '12 at 12:24

score 0 · Answer 1 · answered Nov 03 '12 at 21:23

0

Change the cell's format to 'text' rather than 'general' or 'number' and Excell will leave it alone.

answered Nov 03 '12 at 21:23

Brian Willis

22,768
9
46
50

Can you please give me a sample vba script – liju mathew Nov 03 '12 at 21:30
@lijumathew try this question: http://stackoverflow.com/questions/8265350/how-to-change-format-of-a-cell-to-text-using-vba – Brian Willis Nov 03 '12 at 21:32

score 0 · Answer 2 · answered Nov 04 '12 at 02:51

I am not sure where from you tried to execute your code. Here is what you could do from Excel VBA; executing the code from elsewhere would require few tweaks already mentioned in your code...

Sub importCSVFile()
Dim objWorkBook As Workbook
Dim myFile as String
srccsvfile = "C:\temp\a.csv"

Workbooks.OpenText Filename:=myFile, Origin:=-535, StartRow:=1, _
       DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
        :=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
        Other:=True, OtherChar:=",", FieldInfo:=Array(Array(1, 1), Array(2, 2)), _
        TrailingMinusNumbers:=True

Set objWorkBook = ActiveWorkbook

'do something with active WorkBook

End Sub

Converting a csv file to Excel (with columns as text) using VBA

2 Answers2