Thank you,

That would work...having the references point to a dummy file; but I found a
little bit of a better option for swapping out the reference links:

By choosing Edit/Links/Change Source, I can have the whole Conversion.XLS
switch the file reference to that of the new source CSV.

Since the users would not be handling anyone else's download files, then it
would be just a matter of updating the links once per user and then all the
downloads thereafter would always be the same filename. As the download is
performed every week the download will lay down the new data over the old by
a complete file replacement.

I also found that the XLS will not read a closed CSV automatically like how
an XLS can read a closed XLS. So I found that opening the source CSV is
necessary...which is not too bad as that does offer the user a means to
visually verify that the formulas of the XLS are converting the data
correctly. I find the Import and Query options are more of a pain as the
report file CSV has the header row on the third row and so requires a number
of more steps to either get the Import or the Query to work.

Now that I am aware of the Edit/Links/Change Source option (much like how MS
Access has a link management feature) I have a hunch that the Links are a
property somewhere on the XLS file that can be modified by a VB command or
macro. I might be able to contatenate a filename based on the UserID and
update the property that stores the Source for the Links. I used to be able
to do the same thing in DBase / FoxBase where I could apply a substring such
as the UserID into a Path property to change a link to a file residing in a
user's folder based on their logon name. Again, I have a hunch it can be
done.

The macro suggestion for automation sounds good as well, and the less hands
on for the average user the better.

I think I will start to Name ranges in the XLS on the dynamic ranges in the
CSV to try and get the XLS formulas to shrink and expand as needed since the
source CSV will have variable rows.

This is getting to be an interesting journey.

"Harlan Grove" wrote:

If you want to avoid copy & paste into the new CSV-generating XLS, then open
each original CSV file, immediately save under a dummy filename, e.g.,

C:\Windows\Temp\foobar.CSV

Then open your XLS file in which you would have changed all file references
from <whatever to C:\Windows\Temp\[foobar.CSV]. Generate the new CSV file,
close both files WITHOUT SAVING EITHER, and repeat the process with the next
original CSV file. You could use another XLS file with macros to automate
this process.