Data - Get External Data - Import Text File

Posted by SJC on March 23, 2001 5:36 PM

I recorded a macro that refreshes data that's imported from a text file into Excel. Here are the code lines:

Range("B1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

My problem is: each time I run the macro, Excel asks me for the name of the text file to import. How can I set the name of that text file in the code so that Excel doesn't ask me for that info each time I run the macro?

Posted by Dave Hawley on March 23, 2001 8:37 PM

Hi SJC

I haven't tested this but I'm sure you need to supply either the Index number or the name of the Query table:

Range("B1").Select
Selection.QueryTable(1).Refresh BackgroundQuery:=False

Or...

Range("B1").Select
Selection.QueryTable("MyTable").Refresh BackgroundQuery:=False

Dave

OzGrid Business Applications

Posted by SJC on March 25, 2001 6:57 PM

I tried inserting the name of the table as you suggested but I got Run-Time error 438: Object doesn't support this property or method.

Posted by Dave Hawley on March 25, 2001 7:37 PM

SJC

Are you sure your table intersects range B1 ? If so try this method, it is the same as the VBE help example:

'This example refreshes the query table that intersects cell A10 on worksheet one.
'Worksheets(1).Range("a10").QueryTable.Refresh

Sheets("Sheet1").Range("B1").QueryTable.Refresh BackgroundQuery:=False

OzGrid Business Applications