File disconnect with QueryTable

[TomD]

I’m importing a string of text and I’m using the only method that I know
which is with a QueryTable, as shown below. If there’s a better and easier
way, please let me know.

Application.Goto Reference:="ReceivingCell"

With Selection.QueryTable
.Connection = "TEXT;C:\Source.txt"
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=True
End With

I’m experiencing two problems. The biggest problem is that periodically it
loses the connection, for some reason that I don’t understand, and it’s not
easy to reconnect it. That disconnect also happens every time I make a copy
of the WorkBook. The other problem is that from time to time the source file
is blank. I really don’t understand that. Of course it’s just test data at
this time, but still I have to recreate it every time. Is it mandatory that
there be a constant link?

TomD

 

[Patrick Molloy]

there are several ways. In my oipinion, if you just want to read simple data,
then use the "OPEN" method

here's some example code:

Option Explicit

Sub FetchData()

Dim ch As Long ' assign channel for the OPEN command
Dim sFileName As String '
Dim text As String
Dim rowindex As Long

sFileName = "C:/Temp/Source.txt"
ch = FreeFile

Open sFileName For Input As ch
Do Until EOF(ch)
Input #ch, text
rowindex = rowindex + 1
Cells(rowindex, 1) = text

Loop

Close ch


End Sub