File Properties Location retrieve to refresh background query?

[Guest]

Hi! I have several CSV files that I am exporting from a server, and I bundle
them with a pre-formatted XLS that has an Auto-Open macro that brings them
all into the spreadsheets in the appropriate location. The whole package is
zipped up and emailed to my users in a batch process. I'd like them to unzip
these files to their location of choice and just click on the XLS, but I
don't know the name of the path that they're going to use when they unzip
them, I just assume they will all go into the same folder or desktop
location, the File.XLS, File1.CSV, File2.CSV, etc., all together.

My problem is that when the macro tries to execute the background query
refreshes, it uses an embedded hardcode to the path that I last used when I
saved the file. I have tried stripping out the specifics, e.g., to leave
only the FILE1.CSV information without the path, but it doesn't work. Is
there a way that I can retrieve the File/Properties/Location information for
the XLS and use that path information, minus the file name itself, and stuff
that into my macro, appending each of the CSV file names? Thanks for your
help!

 

[Guest]

Hi,

Unfortunately when you double click on an xls file in Windows Explorer,
Excel opens with the its default path so you can't use CurDir to return the
path where the file/s are located.

I usually provide instructions to the user that they must open Excel first
and change to the directory where the file is located and open it from within
Excel. You then do not need the path for the other files but if you want it,
CurDir can be used to return it. However, need to append a back slash before
appending the file name.

strCurPath = CurDir & "\"