Question on SaveCopyAs

[taylorm]

Someone in my area created a macro a couple of years ago that did th
following:
1) Copy / Paste Special Values the selected ranges in the worksheet
2) SaveCopyAs a new, predetermined file name (with the hard-code
values)
3) Reopen original template worksheet (with the formulas in tact)

This worksheet hasn't been used since July of 2005 and now we're tryin
to use it again, but we're experiencing a problem with the macro. Whe
it gets to the step where its to reopen the original file, we get th
following error:

"Run-time error '1004': A document with the name 'filename.xls' i
already open. You cannot open two documents with the same name, eve
if the documents are in different folders." etc.

One item to note is that we have migrated to Microsoft Office XP sinc
this file was used last July. And the person who wrote the macro (an
the end user) swears that it worked before. I can't find anything tha
suggests any change in the SaveCopyAs command between Excel 97 and Exce
2002. Can anybody shed any light or suggest a better method

 

[Dave Peterson]

Savecopyas saves a copy of the workbook using a new name.

The original file is still there and hasn't been saved/renamed to the new name.

It sure sounds like you really want

copy|paste special|values
..saveas (that new name)
and reopen the template workbook
(and maybe close the .saveas workbook, too??)

 

[Dave Peterson]

And I don't think you'll find a difference between savecopyas in any version.

 

[taylorm]

We've resolved the problem , but still not sure why it happene
.

The original template worksheet that we were attempting to reopen a
the end of the macro was written with its UNC address (because we hav
users with varying drive letter mappings for this network directory).
When we changed it to the drive letter mapping, it worked perfectly.
So we programmed into the macro the ability to identify the particula
user's drive letter mapping and used that in the filename variable.
Works like a charm!

Now our puzzle is, why did it work without issue using the UNC addres
last year (and apparently even on one person's pc on Monday), but bega
having the problem yesterday (Tuesday)?

Anybody care to venture a guess on that one?

By the way, thanks for your suggestions Dave

 

[Dave Peterson]

I don't have a guess. In fact, I'm not sure why that would work.

Maybe the template file and one of the predefined file names were the same???

 

[taylorm]

No, the filenames are different. The file being written out will have
name such as FCST_Mar-2408.xls and it's being written to a completel
different directory where the template file name is Forecast.xls.
(I've used the word "template" rather loosely, it's actually an xl
file, not an xlt.)

Here's the code to the last part of the macro where we're Saving th
Copy As and then re-opening the original file. We've commented out th
UNC address and replaced it with the fully qualified drive mapped pat
from a named range "TemplateDir", which we pickup by using th
cell("filename") function on the active sheet.

' Save file to destination directory, open original template
strDirPath = ActiveSheet.Range("DirectoryPath").Value
strFileName = ActiveSheet.Range("Category").Value
strBC = ActiveSheet.Range("Entity").Value
strFullName = strDirPath & "\" & strFileName & "-" & strBC
".xls"
ActiveWorkbook.SaveCopyAs FileName:=strFullName
Response = MsgBox("File has been copied to " & strFullName
vbOKOnly)
Application.DisplayAlerts = False
' Workbooks.Ope
FileName:="\\FNFNSH42\VOL1\MCAPPL\Hypprod5.5\Exp\Workbook\Process\2006\Forecast\Forecast.xls"
Workbooks.Open FileName:=ActiveSheet.Range("TemplateDir").Value
"Forecast.xls"
Application.DisplayAlerts = Tru

 

[Dave Peterson]

The lines that turn off alerts are hiding the problem.

Application.DisplayAlerts = False
Workbooks.Open _
FileName:=ActiveSheet.Range("TemplateDir").Value & "Forecast.xls"
Application.DisplayAlerts = True

If you comment them out, you'll see that you're suppressing that "you have a
workbook by this name already open, reopening will lose your changes" message.

I'd dump that savecopyas and use a straight .SaveAs

Then you can still reopen the template file. I think I'd approach it like this:

strDirPath = ActiveSheet.Range("DirectoryPath").Value
strFileName = ActiveSheet.Range("Category").Value
strBC = ActiveSheet.Range("Entity").Value
strFullName = strDirPath & "\" & strFileName & "-" & strBC & ".xls"

ActiveWorkbook.SaveAs Filename:=strFullName, FileFormat:=xlWorkbookNormal

MsgBox "File has been copied to " & strFullName, vbOKOnly

Workbooks.Open _
Filename:=ActiveSheet.Range("TemplateDir").Value & "Forecast.xls"

'close the workbook with the code--the one you just "saved As"
Thisworkbook.Close savechanges:=false

It seems more straightforward to me.