vba 制表符分隔的文本文件到带有文本格式的 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20511121/
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
Tab delimited text file to Excel file with text formatted
提问by satish
I am new to VB Programming .I want to convert a text file into a excel file.The text file is comma seperated and Unicode file.And the converted excel file should have all fields in text format(To preserve the prefixed Zeros).After converting the text file to excel with the below code,the zeros are truncated in the excel file.First i am converting the text file with comma delimited to tab delimited and loading the tab delimited text file into excel.
我是 VB 编程新手。我想将文本文件转换为 excel 文件。文本文件以逗号分隔和 Unicode 文件。转换后的 excel 文件应具有文本格式的所有字段（以保留前缀零）。之后使用以下代码将文本文件转换为 excel，excel 文件中的零被截断。首先，我将用逗号分隔的文本文件转换为制表符分隔并将制表符分隔的文本文件加载到 excel 中。
Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("SOURCE_FILE_PATH.txt", ForReading,False,-1)
strContents = objFile.ReadAll
objFile.Close
strContents = Replace(strContents, ",", vbTab)
Set objFile = objFSO.OpenTextFile("SOURCE_FILE_PATH.txt", ForWriting, True, -1)
objFile.Write strContents
objFile.Close
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments.Item(0))
objWorkbook.Saveas WScript.Arguments.Item(1),51
objWorkbook.Close False
Set objWorkbook = objExcel.Workbooks.Open("Target_XLSX_FILE_PATH.xlsx")
objExcel.Visible = True
Set objRange = objExcel.Range("A:K")
objRange.NumberFormat = "@"--converting to text format
objExcel.Save
objWorkbook.Close False
objexcel.Quit
In the line Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments.Item(0)) The tab delimited file is loaded in to various columns with Genetal format.After i convert that to text format also,The zeros won't be there. Thanks in advance for the help.
在 Set objWorkbook = objExcel.Workbooks.Open(Wscript.Arguments.Item(0)) 行中，制表符分隔的文件以 Gental 格式加载到各个列中。我也将其转换为文本格式后，零不会是那里。在此先感谢您的帮助。
回答by Jerome Montino
There's no need to actually use FSOfor this, if you're not going to do anything to the data inside the text file prior to importing to Excel.
FSO如果在导入 Excel 之前不打算对文本文件中的数据执行任何操作，则无需实际使用此功能。
This is my data:
这是我的数据：
This is the code:
这是代码：
Sub TextOpen()
arrtext = Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2))
Workbooks.OpenText Filename:="C:\Users\jeromem\Desktop\BK201.txt", Comma:=True, FieldInfo:=arrtext
End Sub
This is the result after running:
这是运行后的结果：
Check thisfor the OpenTextmethod and thisfor the FieldInfodata types. As you can see there, text format is equal to 2.
检查这对OpenText方法和这为FieldInfo数据类型。正如您在那里看到的，文本格式等于2.
arrtextis basically read as, for column 1 of text, apply format 2; for column 2 of text, apply format 2..... We then feed this array to FieldInfo. We also set Comma:=Truesince this is the delimiter in your text file.
arrtext基本上读作，for column 1 of text, apply format 2; for column 2 of text, apply format 2....。然后我们将这个数组提供给FieldInfo。我们还设置了Comma:=True因为这是您的文本文件中的分隔符。
Let us know if this helps.
如果这有帮助，请告诉我们。
