使用 VBA 将 csv 文件转换为 Excel（以列为文本）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13213590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting a csv file to Excel (with columns as text) using VBA
提问by liju mathew
I have a csv file, for example with data as below (a.csv) to convert into Excel.
我有一个 csv 文件，例如将数据如下（a.csv）转换为 Excel。
In the second column the leading 0s are truncated.
在第二列中，前导 0 被截断。
Is there a way to define the second column as text before importing the csv into Excel?
在将 csv 导入 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
回答by Brian Willis
Change the cell's format to 'text' rather than 'general' or 'number' and Excell will leave it alone.
将单元格的格式更改为“文本”而不是“常规”或“数字”，Excell 将不理会它。
回答by salih0vicX
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...
我不确定你从哪里试图执行你的代码。这是您可以从 Excel VBA 执行的操作；从其他地方执行代码需要在您的代码中已经提到的一些调整......
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
