View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Utahstew Utahstew is offline
external usenet poster
 
Posts: 6
Default Extract matching data from large data file (csv)

Joel,
Here is the modified code I have in the spreadsheet. It throws a Run-time
error '9': Subscript out of range when reading data from line 3073 of the
Total.csv file.

Equity 93671105 NYSE - H&R Block Inc 20080102 18.61 US0936711052 H&R Block
Inc HRB 93671105


Sub getDatafromTextFile()
Dim Ticker As String

tickers = Application.CountA(ActiveSheet.Range("A:A")) 'determines number of
Tickers

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 3
Const TristateUSeDefault = -2
Const TristateTrue = -1
Const TristateFalse = 0

Dim Data(9)

'Default folder

Set fsread = CreateObject("Scripting.fileSystemObject")
Fname = Application.GetOpenFilename(FileFilter:="Text Files (*.csv), *.csv",
Title:="Please select a file")
If Fname = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
End If

Set fread = fsread.getfile(Fname)
Set tsread = fread.openastextstream(ForReading, TristateUSeDefault)

RowCount = 1


Do While tsread.atendofstream = False

inputline = tsread.readline

For i = 0 To 10
If InStr(inputline, ",") 0 Then
Data(i) = Left(inputline, InStr(inputline, ",") - 1)
inputline = Mid(inputline, InStr(inputline, ",") + 1)
Else
If Len(inputline) 0 Then
Data(i) = inputline
inputline = ""
Else
Exit For
End If
End If

Next i

Set c = Columns("A:A").Find(what:=Trim(Data(8)), LookIn:=xlValues,
lookat:=xlWhole)
If Not c Is Nothing Then
c.Offset(0, 1) = Left(Data(2), 3)
c.Offset(0, 2) = Left(Data(7), 35)
End If
Loop
tsread.Close
End Sub