We received a file from a consultant that they need us to refresh daily. It contains a macro that allows the file to update based on some source data that is stored in a TEXT file.

The macro code is as follows

Sheets("AP_AR").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

When it is run, Excel opens an Import Text File Prompt, which we then need to point at the directory where AP_AR.txt exists.

Rather then have to do this, isn't there a much easier way to code this so that when it is run the VBA already has the path and just uses those paths to update. There are a total of 10 sheets in the Macro, so to have it automated would be very helpful.

Thank you in advance.

ADO Import works very well for this. You will need to create a reference to the "MS Active X 2.x" Dll in Tools References. You will also need to write code to run Text to Columns after importing the text. This requires custom Excel VB to run.

Here are some examples. You can find more by searching for 'Excel ADO Text Import' in any flavor search engine.

http://www.exceltip.com/st/Import_da...Excel/430.html

Importing more than 65,536 rows into Excel
http://www.danielklann.com/excel/imp...files_into.htm

(the second link didn't work when I just tried it. I've used this before so it may just be temporary)

Thanks, I will take a crack with that.