I want to set up a query to download data from a Text file before I want to run the query.
I have tried the following code
Sub QuerySetUp(FileNameFrom, SheetNameTo )
FilePath = Sheets("Employees").Range("P23").Value
With Sheets(SheetNameTo)
With .QueryTables.Add(Connection:= _
"TEXT;" & FilePath & FileNameFrom & ".Txt", Destination:=.Range("$A$8"))
.Name = SheetName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
' .Refresh BackgroundQuery:=False
End With
End With
End Sub
hoping that commenting out the penultimate Refresh line would set up the connection for future refreshes without actually refreshing, but this does not seem to happen. How do I add a connection?
John
Bookmarks