You could write a procedure that would open the text file, find the record you
want, parse it out, and return the string. But that sounds like an accident
just waiting to happen.
But if you're living dangerously, maybe this can get you started:
Option Explicit
Function getTextStr(myFilename As String, recNum As Long, _
fieldNumber As Long) As Variant
Dim testStr As String
Dim recCtr As Long
Dim myFileNum As Long
Dim myLine As String
Dim errRec As Boolean
Dim myArr As Variant
testStr = ""
On Error Resume Next
testStr = Dir(myFilename)
On Error GoTo 0
If testStr = "" Then
getTextStr = "File Not Found"
Exit Function
End If
myFileNum = FreeFile()
Close #myFileNum
Open myFilename For Input As #myFileNum
recCtr = 0
errRec = True
Do While Not EOF(myFileNum)
recCtr = recCtr + 1
Line Input #myFileNum, myLine
If recCtr = recNum Then
errRec = False
Exit Do
End If
Loop
Close #myFileNum
If errRec Then
getTextStr = "Record Not Found"
Else
myArr = Split97(myLine, ",")
If UBound(myArr) - LBound(myArr) + 1 < fieldNumber Then
getTextStr = "Field not found"
Else
getTextStr = myArr(fieldNumber - 1)
End If
End If
End Function
Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos > 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function Split97(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
Long = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split97 = sIn
End If
sRead = ReadUntil(sIn, sDelim, bCompare)
Do
ReDim Preserve sOut(nC)
sOut(nC) = sRead
nC = nC + 1
If nLimit <> -1 And nC >= nLimit Then Exit Do
sRead = ReadUntil(sIn, sDelim)
Loop While sRead <> ""
ReDim Preserve sOut(nC)
sOut(nC) = sIn
Split97 = sOut
End Function
And you could use it in a worksheet cell like:
=gettextstr("C:\my documents\excel\myfile.csv",13,8)
Where 13 was the record number and 8 is the field to bring back.
The bad news is you may have to be a little careful. If your fields contain
commas:
"my,field,with,commas",1324,test1,test2
It'll screw up the split procedure. But you could parse that yourself, too.
Keep track if you're in a quoted string or out and count the commas or ignore
them.
The last two procedures are stolen from an MSKB.
http://support.microsoft.com/default.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5
If you're using xl2k or higher, you can dump these two routines and change
split97 to split. (Split was added in xl2k.)
In a previous post I asked if I could link to a CSV file and it's been
pointed out that this is not possible.
My problem is that my spreadsheet application needs the latest data from a
.CSV file which is downloaded from the web on a regular basis by another
(non Excel) application and is always stored in the same location.
I can write a macro in my application which will load the .CSV file and
then save it as .XLS and then close it. [That way I'm able to link to the
non-open XLS source]
Problem is the macro has to overwrite the previously saved XLS and this
requires manual intervention whereas I need this app to be fully automatic.
Any ideas plse.
TIA
Craig
