VBA 根据条件将文本文件读入 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18908127/
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
VBA read text file into Excel spreadsheet based on criteria
提问by user2238328
Folks, I am trying to read what is a very large space delimited text file into an Excel spreadsheet using VBA. The text file contains 5 columns of data which are delimited by multiple spaces. I have managed to open the file and split the file into various columns. Now I would like to choose only rows that meet certain criteria as output into the spreadsheet. So, for example, i would like to choose only the rows which in the first column are DIES and in the second column are EUR. Any advice on how to efficiently accomplish this? I've tried various combinations with If/Else statements but this not seem to work. Thank you.
伙计们，我正在尝试使用 VBA 将非常大的空格分隔文本文件读取到 Excel 电子表格中。文本文件包含由多个空格分隔的 5 列数据。我设法打开文件并将文件拆分为不同的列。现在我只想选择满足特定条件的行作为电子表格的输出。因此，例如，我只想选择第一列中为 DIES 且第二列中为 EUR 的行。关于如何有效地实现这一目标的任何建议？我已经尝试了 If/Else 语句的各种组合，但这似乎不起作用。谢谢你。
Text file
文本文件
DIES EUR REFGR OCT2008 847.000
VARS EUR REFGR NOV2008 154.000
EFFS OECD REFGR DEC2008 507.000
DIES EUR REFGR JAN2008 090.000
USUE EUR REFGR FEB2008 836.000
And my code so far
到目前为止我的代码
Sub ImportData()
Open "FileName" For Input As #1
lRow = 2
Do While Not EOF(1)
Line Input #1, Data
Data = Application.WorksheetFunction.Trim(Data)
sData = Split(Data, " ")
With Sheet1
lColumn = 2
For intCount = LBound(sData) To UBound(sData)
.Cells(lRow, lColumn) = sData(intCount)
lColumn = lColumn + 1
Next intCount
End With
lRow = lRow + 1
Loop
Close #1
End Sub
回答by Tim Ogilvy
Alternatively, treat the text file as a data source and query it.
或者，将文本文件视为数据源并对其进行查询。
Using DAO: http://msdn.microsoft.com/en-au/library/aa293458(v=vs.60).aspx
使用 DAO：http: //msdn.microsoft.com/en-au/library/aa293458(v= vs.60).aspx
Using ADO: http://msdn.microsoft.com/en-us/library/ms974559.aspx
使用 ADO：http: //msdn.microsoft.com/en-us/library/ms974559.aspx
回答by Renan Ranelli
I would go for something like that. You can write a function that decides if a row should or should not be written to the file.
我会去做这样的事情。您可以编写一个函数来决定是否应该将行写入文件。
function IsValid(stringArray as string()) as boolean
' Your criteria goes here, maybe with case/switches or lots of ifs
end function
Then your code would be like:
那么你的代码会是这样的：
...
With Sheet1
lColumn = 2
if IsValid(sData) then
For intCount = LBound(sData) To UBound(sData)
.Cells(lRow, lColumn) = sData(intCount)
lColumn = lColumn + 1
Next intCount
end if
End With
....
If you are having problems with the layout of your conditionals, you need to provide more info about then so we can help =).
如果您在条件布局方面遇到问题，则需要提供更多相关信息，以便我们提供帮助 =)。
