Question on SaveCopyAs

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?

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 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.

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

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\Workbook\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