suppress file open dial. when refreshing a txt file data src

[hon]

I am trying to refresh an external data source which sources the data from a text file. But everytime I refresh, it would open up a file open dialogue to pick a text file. Is it possible to store the text file name somewhere or use a macro to supply a hardcoded file name and suppress the dialogue?

 

[tactps]

Record a macro and post it here. It should be able to be hadcoded to pick up the correct file and suppress the question fairly easily.

 

[hon]

I tried recording the macro but it didn't store the file name. When I tried the macro recorded, it still asked for the file name. Any idea how to code it manually?

 

[Tazguy37]

Post the recorded code anyways....

 

[tactps]

Something like this:

Range("a:a").EntireColumn.Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;c:\My Documents\nov09.dat", Destination:=Range( _
"A1"))
.Name = "nov09"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[hon]

How silly of me!
I just found out how to suppress the file name by checking the "Prompt for file name on refresh" option.

Thanks for you advice anyway.