从文本文件加载 VBA 中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2884714/
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
loading Data in VBA from a text file
提问by omegayen
I am not very familiar with VBA but need to use it for a new software program I am using (not Microsoft related)
我对 VBA 不是很熟悉，但需要将它用于我正在使用的新软件程序（与 Microsoft 无关）
I have a text file that has columns of data I would like to read into VBA.
我有一个文本文件，其中包含要读入 VBA 的数据列。
Specifically the text file has 4 entries per row. Thus I would like to load in the column vectors (N by 1).
具体来说，文本文件每行有 4 个条目。因此，我想加载列向量（N × 1）。
The text file is separated by a space between each entry.
文本文件由每个条目之间的空格分隔。
So for example I want to load in column one and save it as array A, then column two and save as array B, then column three and save as array C, and then column four and save as array D.
因此，例如我想加载第一列并将其保存为数组 A，然后第二列并保存为数组 B，然后第三列并保存为数组 C，然后第四列并保存为数组 D。
This code snippet found below from http://www.tek-tips.com/faqs.cfm?fid=482is something I found that can load in text to an array, but I need to adapt it to be able to save the columns as different arrays as specified above...
下面从http://www.tek-tips.com/faqs.cfm?fid=482找到的这段代码片段是我发现可以将文本加载到数组的东西，但我需要对其进行调整才能保存列作为上面指定的不同数组...
Open "MyFile.txt" For Input As #1
ReDim Txt$(0)
Do While Not EOF(1)
ReDim Preserve Txt$(UBound(Txt$) + 1)
Input #1, Txt$(UBound(Txt$))
Loop
Close #1
Open "MyFile.txt" For Input As #1
ReDim Txt$(0)
Do While Not EOF(1)
ReDim Preserve Txt$(UBound(Txt$) + 1)
Input #1, Txt$(UBound(Txt$))
Loop
Close #1
回答by Fionnuala
For this example, you will need a file called schema.ini in the same directory as the text file. It should contain:
对于此示例，您将需要与文本文件位于同一目录中的名为 schema.ini 的文件。它应该包含：
[Import.txt]
Format=Delimited( )
Where Import.txt is the name of the file (http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx).
其中 Import.txt 是文件的名称 ( http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx)。
You can then use this, which should work in VBScript or VBA with very little tampering:
然后你可以使用它，它应该在 VBScript 或 VBA 中工作，几乎没有篡改：
Set cn = CreateObject("ADODB.Connection")
'Note HDR=Yes, that is, first row contains field names '
'and FMT delimted, ie CSV '
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Docs\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
cn.Open strcon
strSQL="SELECT * FROM Import.txt" _
set rs = createobject("adodb.recordset")
rs.open strSQL,cn
MsgBox rs(2)
MsgBox rs.GetString
The first message box should return the third column of the first row, it is a test that it works.
第一个消息框应该返回第一行的第三列，测试它是否有效。
The second message box should return the whole file, so don't use it with a large set. The recordset can be manipulated, or you can use .GetRows to create an array of values (http://www.w3schools.com/ado/met_rs_getrows.asp)
第二个消息框应该返回整个文件，所以不要将它与大集合一起使用。可以操作记录集，或者您可以使用 .GetRows 创建一个值数组 ( http://www.w3schools.com/ado/met_rs_getrows.asp)
回答by Karsten W.
Seems the remaining problem is to convert from an array of lines to four arrays of columns. Maybe this snippet helps
似乎剩下的问题是从一个行数组转换为四个列数组。也许这个片段有帮助
Option Explicit
Option Base 0
Sub import()
Dim sTxt() As String
Dim sLine As Variant
Dim iCountLines As Long
Dim iRowIterator As Long
Dim i As Long
Dim sRow() As String
Dim sColumnA() As String
Dim sColumnB() As String
Dim sColumnC() As String
Dim sColumnD() As String
' read in file '
Open "MyFile.txt" For Input As #1
ReDim sTxt(0)
Do While Not EOF(1)
Input #1, sTxt(UBound(sTxt))
ReDim Preserve sTxt(UBound(sTxt) + 1)
Loop
Close #1
' dim array for each columns '
iCountLines = UBound(sTxt)
Debug.Print "working with ", iCountLines, "lines"
ReDim sColumnA(iCountLines)
ReDim sColumnB(iCountLines)
ReDim sColumnC(iCountLines)
ReDim sColumnD(iCountLines)
' "transpose" sTxt '
iRowIterator = 0
For Each sLine In sTxt
sRow = Split(sLine, " ")
If UBound(sRow) = 3 Then
sColumnA(iRowIterator) = sRow(0)
sColumnB(iRowIterator) = sRow(1)
sColumnC(iRowIterator) = sRow(2)
sColumnD(iRowIterator) = sRow(3)
iRowIterator = iRowIterator + 1
End If
Next sLine
' now work with sColumnX '
Debug.Print "Column A"
For i = 0 To iCountLines
Debug.Print sColumnA(i)
Next i
End Sub
回答by Marek
There is few detais in your question, but i would suggest using "Text to column"
您的问题中的细节很少，但我建议使用“文本到列”
If you're not very familiar with VBA programming try recording macro with this steps:
如果您对 VBA 编程不是很熟悉，请尝试使用以下步骤录制宏：
- Import file to Excel
- select column A
- select "Text to columns" form tools menu
- choose delimited by space
- 将文件导入 Excel
- 选择A列
- 选择“文本到列”表单工具菜单
- 选择以空格分隔
This way you'll get array of data you asked for, now assigning to any variables you want shouldn't be a problem.
通过这种方式，您将获得所需的数据数组，现在分配给您想要的任何变量都不是问题。
EDIT (Without using Excel):
编辑（不使用 Excel）：
Take a look on that FSOmethod.
看看那个FSO方法。
by replacing
通过替换
MsgBox strLing
消息框字符串
with some kind of split function, like
具有某种拆分功能，例如
strTemp = Split(strLine, " ")
You'll be able to loop through all the values in your source file, would that work?
您将能够遍历源文件中的所有值，这行得通吗？
