Refreshing an imported text file

I am importing a text file using Data -> From Text. I would like to be able to refresh this using the code

Please Login or Register  to view this content.

This which once worked, no longer does. If I right click on the table in Excel the Refresh option is greyed out.
The only change I am aware of is that I have unchecked Prompt for File Name in DataRange Properties, which was available to me before the problem started.
Will this have caused my problem? If it has how do I allow my users to download the text file without knowing its name (which they will not)?

Data->From Text. Won't be able to refresh if relative address of the text file changes (unless you use prompt to specify file).

Depending on company network set up, text file may reside at different drive letter designation for each user (ex. some may have it mapped to S-drive, while other have it on Z-drive etc) even if it's located in network drive.

There are several ways to work around this, but it will depend on your intended audience (internal, external), versions of Excel used by users etc.

The textfile that I am copying resides on a network drive. The excel file that I am using will reside on each of the users own networked machines. As far as I am aware all the users machines have their networks configured the same way. I always use this technique of storing and retrieving data in text files and it works well on my network. I am developing the current application on my networked machine using Excel 2010. The other networked machines will use either 2010 or 2007.( Again these machines are currently running 3 applications which I have built, all refreshing textfiles using the same sub routine above.).
I would very much like to know of the sort of thing I could do to work round the problem.
John

How often is this connection refreshed?

CK76
Once as the application is opened. Usually the users keep it open on their machine during the working day. So I would say between 1 and 3 times
John

AS a work round of my problem I have decided to connect to the text file instead of refreshing. To do this I use the following code found by running a Macro

Please Login or Register  to view this content.

This leads to another problem which is that a new connection is added to the list of connections each time the Macro is used. I assume that I will be able to delete the connection in the code above, but I have not been able to fins a way of doing this. Can anyone help please.
John

This leads to another problem which is...

I'd suggest using ADO to read/import from text file. You can find extensive documentation in links below. If you encounter problem in implementation let me know.
MSDN article that has some examples of working with ADO
https://msdn.microsoft.com/en-us/library/ms974559.aspx

Good tutorial with explanation of common mistakes
http://www.xtremevbtalk.com/tutors-c...cel-files.html