File disconnect with QueryTable
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
"TomD" wrote:
Im importing a string of text and Im using the only method that I know
which is with a QueryTable, as shown below. If theres 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
Im experiencing two problems. The biggest problem is that periodically it
loses the connection, for some reason that I dont understand, and its 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 dont understand that. Of course its 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
|