Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveCopyAs cannot access opened file?
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveCopyAs cannot access opened file?
Nevermind about the second part (checking if a workbook is the only on
opened). I found the Workbooks.count function -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveCopyAs cannot access opened file?
Does anyone know a solution, or a possible elegant workaround to m
problem of not being able to backup to an opened file -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveCopyAs cannot access opened file?
This may be inelegant, but I have got round this problem by using:
Application.Sendkeys ("y") ActiveWorkbook.SaveAs Filename:="whatever your file name is -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveCopyAs cannot access opened file?
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] Does anyone know a solution, or a possible elegant workaround to my problem of not being able to backup to an opened file? --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveCopyAs cannot access opened file?
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file links incorrect when file is re-opened | Excel Worksheet Functions | |||
personal.xls doesn't open when excel is opened from Access | Setting up and Configuration of Excel | |||
cannot open the excel file, the file is already opened | Excel Discussion (Misc queries) | |||
SaveCopyAs | Excel Programming | |||
Exporting data directly from Excel to an un-opened Access database using VBA | Excel Programming |