Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File disconnect with QueryTable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reset QueryTable error on empty text file | Excel Programming | |||
Browse 4 a file & disconnect a connection to external source | Excel Programming | |||
Disconnect linked file | Excel Discussion (Misc queries) | |||
Changing only source file of pre-existing text import QueryTable? | Excel Programming |