External Data Text file Refresh Macro

[Will Graham]

I have a text.CVS file I down load daily off a server. I have used Get External Data From Text. Works well. I want to write a VBA sub to refresh the link as part of a large reporting package
What I have is:

Selection.QueryTable.Refresh BackgroundQuery:=False

But when it runs it asks/promts me for the file name. I don't want the entire application to stop here. How do I tell it to use the same file? with out stoping?

 

[pbornemeier]

This question is being answered from an Excel 2003 POV, but it should be applicable to 2007 as well.

Turn on the macro recorder while you run the External Data command and you will get code you can edit to re-run the query. The line you posted is the last line of a block of ~17 commands that set up a query.

I usually preface the resulting code with something like this to ensure that the query count does not build up, and no data remains from previous queries:

    For iX = ActiveSheet.QueryTables.Count To 1 Step -1
        ActiveSheet.QueryTables(iX).Delete
    Next
    Range("A1").CurrentRegion.Cells.Clear
    Range("A1").Select