"Refresh Data" Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Excel 2000 SP-3

I used: Data/Get External Data/Import Text File
to import data into a worksheet. Then, to import from another file into
the same worksheet, I used: Data/Refresh Data. It asked me for a file name
and when I entered it it worked fine.

However, when I attempted to record the "Refresh Data" as a macro, all that
was recorded was:
Selection.QueryTable.Refresh BackgroundQuery:=False

The part where I specified the file name was not recorded. When I run the
macro, it asks me for the file name.

How can I specify the file name in the macro so that it does not have to be
entered manually?

Many thanks.
 
NS

Change the Connection property of the QueryTable object. The Connection
property looks like

TEXT;C:\MyText1.txt

You can use code like this

sNewFile = "C:\MyText2.txt"

With Sheet1.QueryTables(1)
.TextFilePromptOnRefresh = False
.Connection = "TEXT;" & sNewFile
.Refresh False
End With
 
Back
Top