How to populate data from .txt file into Excel in VBA?

Question

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 ActiveSheet is not pointing to .txt file.

My .txt file is like this (space delimited):

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?

Answer 1

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.

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

Answer 2

You should generally avoid using the ActiveWorkbook object unless you're positive that the workbook you want to reference will always be 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:

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