"Refresh Data" Question

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.
 
D

Dick Kusleika

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
 
Top