使用 VBA 将特定文本文件导入 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28213953/
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
Import a specific Text File into Excel Spreadsheet using VBA
提问by Natalie King
I have a text file which is created daily and placed in the same folder each day. The file path of the text file never changes. For now, let's say the path is : "\MyPath\test.csv\"
我有一个每天创建并每天放置在同一个文件夹中的文本文件。文本文件的文件路径永远不会改变。现在，假设路径是：“\MyPath\test.csv\”
I want to create a report in MS Excel 2010 where the user can press a button, the text file is imported into a range, say, Worksheets("Sheet1").Range("A1"), and then analysis is performed upon this imported data.
我想在 MS Excel 2010 中创建一个报告，用户可以在其中按下一个按钮，将文本文件导入一个范围，例如 Worksheets("Sheet1").Range("A1")，然后对此进行分析导入的数据。
I have been using VBA with MS Excel for a while now and understand how to create a script which will import data from various databases, etc but I can't seem to get my head around importing text files! All the examples I find online seem to open a dialog box for the user to select the text file, but I don't want my user to be able to do this. I want one specific text file to be imported each time, with no imput from the user. All they have to do is press a button to start the macro.
我已经使用 VBA 和 MS Excel 有一段时间了，并且了解如何创建一个脚本来从各种数据库等导入数据，但我似乎无法理解导入文本文件！我在网上找到的所有示例似乎都打开了一个对话框供用户选择文本文件，但我不希望我的用户能够这样做。我希望每次都导入一个特定的文本文件，而无需用户输入。他们所要做的就是按下一个按钮来启动宏。
Does someone have a very simple code which will do the importing for me? Basically, I want a macro to import a chosen text file into Sheet1 cell A1. I also want to manually specify the text delimiter within the code.
有人有一个非常简单的代码可以为我进行导入吗？基本上，我想要一个宏将选定的文本文件导入到 Sheet1 单元格 A1 中。我还想在代码中手动指定文本分隔符。
Any help/advice would be appreciated.
任何帮助/建议将不胜感激。
回答by barryleajo
A couple of assumptions to give you some flexibility in customising this code example. My test data file is shown in the image below i.e. ten variable length records, each field delimited by a comma. The code reads the .csv file one record at a time and puts each record on a separate row. Within each row (i.e. for each record), each field is put into a separate cell. It uses a couple of arrays to achieve this. You can adjust the code to suit your context and there are some comments within, to help. Just add a button to call this macro.
一些假设可以让您在自定义此代码示例时具有一定的灵活性。我的测试数据文件如下图所示，即十个可变长度记录，每个字段用逗号分隔。该代码一次读取 .csv 文件一条记录，并将每条记录放在单独的行中。在每一行内（即对于每条记录），每个字段都被放入一个单独的单元格中。它使用几个数组来实现这一点。您可以调整代码以适应您的上下文，其中有一些注释可以提供帮助。只需添加一个按钮即可调用此宏。
Sub ReadTxtFile()
Dim ws As Worksheet
Dim rearr(), wrarr()
Dim fName As String
Dim rowno As Long, colno As Long, rec As Long
Dim cnt As Long, cnt2 As Long
Dim delim As String
'specify output sheet
Set ws = Worksheets("Sheet1")
'specify text file to read (.csv in this example)
fName = "C:\MyPath\test.csv"
'set text file delimiter
delim = "," 'for Tab delimiter use delim = Chr(9)
ifnum = FreeFile
'set start row/col for text data to be placed ("A1" in this example)
rowno = 1 'row 1
colno = 1 'col A
With ws
Open fName For Input Access Read As #ifnum
rec = 0
Do While Not EOF(ifnum)
Line Input #ifnum, tmpvar
rec = rec + 1
'Put whole record into array
ReDim Preserve rearr(1 To rec)
rearr(rec) = tmpvar
'Split fields into a second array
wrarr = Split(rearr(rec), delim)
cnt2 = UBound(wrarr)
'Write fields out to specified ws range, one row per record
For cnt = 0 To cnt2
ws.Cells(rowno, colno + cnt) = wrarr(cnt)
Next cnt
rowno = rowno + 1
Loop
Close #ifnum
End With
End Sub
