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

--
taylor
-----------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...fo&userid=2889
View this thread: http://www.excelforum.com/showthread.php?threadid=52499

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??)

taylorm wrote:

Someone in my area created a macro a couple of years ago that did the
following:
1) Copy / Paste Special Values the selected ranges in the worksheet
2) SaveCopyAs a new, predetermined file name (with the hard-coded
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 trying
to use it again, but we're experiencing a problem with the macro. When
it gets to the step where its to reopen the original file, we get the
following error:

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

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

--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=524999

--

Dave Peterson

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

taylorm wrote:

Someone in my area created a macro a couple of years ago that did the
following:
1) Copy / Paste Special Values the selected ranges in the worksheet
2) SaveCopyAs a new, predetermined file name (with the hard-coded
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 trying
to use it again, but we're experiencing a problem with the macro. When
it gets to the step where its to reopen the original file, we get the
following error:

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

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

--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=524999

--

Dave Peterson

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

--
taylor
-----------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...fo&userid=2889
View this thread: http://www.excelforum.com/showthread.php?threadid=52499

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 wrote:

We've resolved the problem :) , but still not sure why it happened
.

The original template worksheet that we were attempting to reopen at
the end of the macro was written with its UNC address (because we have
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 particular
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 address
last year (and apparently even on one person's pc on Monday), but began
having the problem yesterday (Tuesday)?

Anybody care to venture a guess on that one?

By the way, thanks for your suggestions Dave.

--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=524999

--

Dave Peterson

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\W orkbook\Process\2006\Forecast\Forecast.xls"
Workbooks.Open FileName:=ActiveSheet.Range("TemplateDir").Value
"Forecast.xls"
Application.DisplayAlerts = Tru

--
taylor
-----------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...fo&userid=2889
View this thread: http://www.excelforum.com/showthread.php?threadid=52499

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.



taylorm wrote:

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

Here's the code to the last part of the macro where we're Saving the
Copy As and then re-opening the original file. We've commented out the
UNC address and replaced it with the fully qualified drive mapped path
from a named range "TemplateDir", which we pickup by using the
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.Open
FileName:="\\FNFNSH42\VOL1\MCAPPL\Hypprod5.5\Exp\W orkbook\Process\2006\Forecast\Forecast.xls"
Workbooks.Open FileName:=ActiveSheet.Range("TemplateDir").Value &
"Forecast.xls"
Application.DisplayAlerts = True

--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=524999

--

Dave Peterson