refreshing imported tables

[uklobster]

I have two csv tables imported into a spreadsheet and would like to automate the 'refreshing data' part so its run froma macro.

I have recorded a macro which should do the trick, but when I run it it falls over and cannot find the CSV file. The import text file dialogue box comes up, but until i click the file type 'text file' and reset it back to 'text file' the CSV isnt visible.

The two downloads are set to go straight to c:\data

Is there a way in VB to locate and load up the correct file ?

At the moment the macro code is...
Selection.QueryTable.Refresh BackgroundQuery:=False

 

[tactps]

Before you try to load the file, add the following code in the macro:

chdir c:\data

This should ensure that it is looking in the correct folder.

 

[uklobster]

Thanks for the reply, but it doesnt solve the problem.

The directory seems to be ok, its just that Excel doesnt recognise the CSV file the table is linked to. But, when I change the open dialogue box from 'Text File' and then sent it back to 'Text File' again - the CSV suddenly becomes visible again and I can then select it and open the table to refresh the data.

Can I specify the file and the location so the open data source dialogue box doesnt open ?

Thanks for all your help