Hi Tony -

Looking at your original posting again, it seems to me that the reason the
copy is created in the My Documents folder is that you're not specifying the
full path to where you really want the copy to be created:

'copy the template to the containerID
spreadsheetfile = templatedir + templatefile
targetfile = Trim(Sheet1.Cells(x, 1)) & ".xls"
FileCopy spreadsheetfile, targetfile

You're reading the targetfile from Sheet1, but not specifying the path.
Excel therefore uses the user's designated documents directory as a default.


From: Tony Alston
Subject: FileCopy creates two files
Date: 2004-06-30 16:15:14 PST


Hi Mark,

I had thought of this earlier, but am unable to reproduce it stepping
line by line. It seems to happen only when executing the macro which
adds to the puzzlement. I thought that the path I had set in my
variables might have been the culprit, but I when including the path in
the statement to open the new file, it worked ok to open - but not when
activating different sheets.

ie:

templatedir = "C:\WINDOWS\desktop\hsgbdrwwis\"
targetfile = Trim(Sheet1.Cells(x, 1)) & ".xls"
targetfile = templatedir + targetfile

Workbooks.Open (targetfile) 'this worked
... 'yadda yadda code
Windows(targetfile).Activate 'this didn't

Any other thoughts? It's not a BIG deal (other than if it starts
writing over the top of the modified file with my blank template) but is
a curious anomoly.

-Tony