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