Error 1004 while calling SaveAs function

M

mo_dee

New Member
Joined
Oct 12, 2004
Messages
4
Hi,

I have a problem using a macro in Excel. I have used the SaveAs method of the Workbook class to save a workbook. If a file by the same name (as I generate) already exists, excel asks the user whether they would like to overwrite or not.

ActiveWorkbook.SaveAs Filename:=fileSaveName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If the user clicks 'no' or 'cancel' , VB generates a 1004 run-time error.

however, 1004 seems to be a generic error. So, when I am trapping errors, and a 1004 error is generated, how can I distinguish between a user clicking on 'no' or 'cancel' and a genuine save error?

Thanks,
Simon.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Hi,

I don't think there's any way of checking when the user presses No or Cancel. I usually use my own code to first check to see if the file exists. If it does then warn the user and take appropriate action e.g.

Code: 
Sub Test()
    Dim FileSaveName As String

    FileSaveName = "C:\temp\book1.xls"



    If Dir(FileSaveName) <> "" Then
        If MsgBox("A file named " & FileSaveName & " already exists in this location.  " & _
                "Do you want to replace it?", vbYesNo + vbExclamation + vbDefaultButton2) = vbNo Then Exit Sub

                
        On Error GoTo FileInUseError
        Kill FileSaveName   'Attempt to delete the existing file.  If it fails then the code will jump
                            'to the error handler
        On Error GoTo 0

    End If

    ActiveWorkbook.SaveAs Filename:=FileSaveName, _
            FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False



FileInUseError:
MsgBox "Could not overwrite " & FileSaveName & ".  The file may be in use.", vbExclamation

End Sub

Hope this helps,
Dan
 
Upvote 0
This sounds good if I don't want to overwrite a file, but what if I do want to replace the file?

I save a backup of a critical file to a shared drive daily. I need to have the file "write reserved" to prevent someone from accidentally (or not!) changing the file. I want to replace the previous day's file each time. However, I get the following message:

"Run-time error '1004':

Operation Failed. Filename ___ is write reserved."

There has got to be a way to do this with a macro, isn't there? :oops:
 
Upvote 0
Does anyone know how this can be achieved? I don't like the idea of using the "kill" command and then saveas. Would rather saveas wrote over the file in the first place.
 
Upvote 0
Kev

Could you start a new thread?

This one is almost 3 years old.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Method 'SaveAs of Object'_Workbook' failed error handling routine help needed.
Replies
3
Views
604
rlv01
R
H
  • Question
Runtime Error
Replies
2
Views
182
Holley
H
M
  • Question
SaveAs CSV on Mac
Replies
7
Views
708
MARK858
MARK858
willow1985
  • Solved
VBA error handling - File already exists in location
Replies
9
Views
132
NateSC
NateSC
T
  • Solved
VBA/Macro to select specific worksheets and save them as separate files
Replies
4
Views
443
Trebor76
T

Forum statistics

Threads
1,216,128
Messages
6,129,036
Members
449,482
Latest member
al mugheen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top