如何在 VBA 中将 .txt 文件中的数据填充到 Excel 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2580623/
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
How to populate data from .txt file into Excel in VBA?
提问by swei
I'm trying to create something to read data from a .txt file, then populate data into .xls, but after open the .txt file, how do I get the data out? Basically I'm trying to get the the third column of the lines dated '04/06/2010'. After I open the .txt file, when I use ActiveSheet.Cells(row, col), the ActiveSheetis not pointing to .txt file.
我正在尝试创建一些东西来从 .txt 文件中读取数据，然后将数据填充到 .xls 中，但是在打开 .txt 文件后，我如何获取数据？基本上，我试图获取日期为“04/06/2010”的行的第三列。打开 .txt 文件后，当我使用 时ActiveSheet.Cells(row, col)，ActiveSheet不是指向 .txt 文件。
My .txt file is like this (space delimited):
我的 .txt 文件是这样的（空格分隔）：
04/05/10 23 29226
04/05/10 24 26942
04/06/10 1 23166
04/06/10 2 22072
04/06/10 3 21583
04/06/10 4 21390
Here is the code I have:
这是我的代码：
Dim BidDate As Date
BidDate = '4/6/2010'
Workbooks.OpenText Filename:=ForecastFile, StartRow:=1, DataType:=xlDelimited, Space:=True
If Err.Number = 1004 Then
MsgBox ("The forecast file " & ForecastFile & " was not found.")
Exit Sub
End If
On Error GoTo 0
Dim row As Integer, col As Integer
row = 1
col = 1
cell_value = activeSheet.Cells(row, col)
MsgBox ("the cell_value=" & cell_value)
Do While (cell_value <> BidDate) And (cell_value <> "")
row = row + 1
cell_value = activeSheet.Cells(row, col)
' MsgBox ("the value is " & cell_value)
Loop
If cell_value = "" Then
MsgBox ("A load forecast for " & BidDate & " was not found in your current load forecast file titled '" + ForecastFile + ". " + "Make sure you have a load forecast for the current bid date and then open this spreadsheet again.")
ActiveWindow.Close
Exit Sub
End If
Can anyone point out where it goes wrong here?
谁能指出这里出了什么问题？
回答by Dick Kusleika
In the example below, I set the variable ws equal to the sheet I want and I'm able to use that variable to refer to the sheet later. The keyword ActiveWorkbook should point to the newly opened text file. I could tell what you wanted to do with the info, such I just made some stuff up.
在下面的示例中，我将变量 ws 设置为等于我想要的工作表，以后我可以使用该变量来引用工作表。关键字 ActiveWorkbook 应指向新打开的文本文件。我可以告诉你想用这些信息做什么，所以我只是编了一些东西。
Sub GetBidData()
Dim dtBid As Date
Dim ws As Worksheet
Dim rFound As Range
Dim sFile As String
dtBid = #4/6/2010#
sFile = Environ("USERPROFILE") & "\My Documents\ForecastFile.txt"
Workbooks.OpenText Filename:=sFile, _
StartRow:=1, _
DataType:=xlDelimited, _
Space:=True
Set ws = ActiveWorkbook.Sheets(1)
Set rFound = ws.Columns(1).Find( _
Format(dtBid, ws.Range("A1").NumberFormat), , xlValues, xlWhole)
If Not rFound Is Nothing Then
MsgBox rFound.Value & vbCrLf & _
rFound.Offset(0, 1).Value & vbCrLf & _
rFound.Offset(0, 2).Value
End If
End Sub
回答by Nick Spreitzer
You should generally avoid using the ActiveWorkbook object unless you're positive that the workbook you want to reference will alwaysbe active when your code is run. Instead, you should set the workbook you're working with to a variable. Theoretically, you should be able to use the OpenText method to do this, but VBA doesn't like that. (I'm pretty sure it's a bug.) So right after you open your text file, I would do this:
通常应避免使用 ActiveWorkbook 对象，除非您确定要引用的工作簿在代码运行时始终处于活动状态。相反，您应该将您正在使用的工作簿设置为一个变量。从理论上讲，您应该能够使用 OpenText 方法来执行此操作，但 VBA 不喜欢那样。（我很确定这是一个错误。）所以在你打开文本文件后，我会这样做：
Workbooks.OpenText Filename:=Forecastfile, StartRow:=1,
DataType:=xlDelimited, Space:=True
Dim ForecastWorkbook As Workbook, book As Workbook
Dim ForecastFileName As String
ForecastFileName = "YourFileNameHere.txt"
For Each book In Application.Workbooks
If book.Name = ForecastFileName Then
Set ForecastWorkbook = book
Exit For
End If
Next book
Then, instead of this...
然后，而不是这个......
cell_value = activeSheet.Cells(row, col)
...do this...
...做这个...
cell_value = ForecastWorkbook.Sheets(1).Cells(row, col).Value
回答by nishit dey
Below code will read the text file and paste the values in the cell of Sheet2. However if you put a formatting in the Date column that will do the trick
下面的代码将读取文本文件并将值粘贴到 Sheet2 的单元格中。但是，如果您在日期列中放置格式，则可以解决问题
Public Sub Read_text()
Sheet2.Activate
Set fso = New FileSystemObject
Fname = Application.GetOpenFilename
x = 1
y = 1
Set Stream = fso.OpenTextFile(Fname, ForReading, True)
Do While Not Stream.AtEndOfStream
Str_text = Stream.ReadLine 'Perform your actions
rdtext = Split(Str_text, " ")
Sheet2.Cells(x, y) = rdtext(0)
Sheet2.Cells(x, y + 1) = rdtext(1)
Sheet2.Cells(x, y + 2) = rdtext(2)
x = x + 1
y = 1
Loop
Stream.Close
End Sub
For example : Below code will change the format in '05/04/2010'
例如：下面的代码将更改“05/04/2010”中的格式
Sheet2.Cells(x, y) = Format(rdtext(0), "mm/dd/yyyy;@")
