SaveCopyAs cannot access opened file?

[dule]

Hello,

I'm trying to create a backup of the current active workbook using
SaveCopyAs, however, if the backup is currently opened (which is
conceivable in this case) a runtime error occurs (1004) stating that
it cannot access the file.

Is there a way to allow access to the opened backup file and update
it?

On a side-note, how can I check if the current workbook is the only
workbook opened?

(Excel 2002)

Any help would be apprecitated. Thanks.

Dan

 

[dule]

Nevermind about the second part (checking if a workbook is the only on
opened). I found the Workbooks.count function

 

[dule]

Does anyone know a solution, or a possible elegant workaround to m
problem of not being able to backup to an opened file

 

[Kelston]

This may be inelegant, but I have got round this problem by using:

Application.Sendkeys ("y")
ActiveWorkbook.SaveAs Filename:="whatever your file name is

 

[Jake Marx]

Hi dule,

You'll have to decide what you want to do if the location you're trying to
save to is currently in use. If you want to fail over and save the backup
with a different name, then you can trap the error and move on. If you want
to close the open backup workbook, then do the backup, you can do that too
(assuming nobody else may have the backup workbook open, in which case
there's not much you can do).

Here's some code (untested) that will hopefully help.

If you want to save to another location:

On Error Resume Next
Workbooks("Test.xls").SaveCopyAs "c:\test_backup.xls"
If Err.Number Then
Application.DisplayAlerts=False
Workbooks("Test.xls").SaveCopyAs "c:\test_backup_" & _
Format$(Date, "yyyymmddhhnnss.ss") & ".xls"
Application.DisplayAlerts=True
End If
On Error Goto 0

If you want to close the backup workbook and savecopyas:

On Error Resume Next
Workbooks("test_backup.xls").Close False
On Error Goto 0
Application.DisplayAlerts=False
Workbooks("Test.xls").SaveCopyAs "c:\test_backup.xls"
Application.DisplayAlerts=True

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[dule]

Thank you for the replies.

Kelston, I tried your method, but if the file is opened, it tells m
that it cannot save to the same name as an opened workbook.

I ended up closing the workbook before trying to backup. Thanks Jake