CODEWITHSUNDEEP
excelvba
user3853292
user3853292

Reputation: 13

Run Time Error - Cannot access file

I have the following VBA script which saves a workbook and this works fine.

However if commandbutton1 is clicked again it correctly brings up the option box

'Do you want to replace the file'- Yes, No, Cancel.

The yes option works fine but the No and Cancel option bring up an error box - RunTime error 1004: Cannot access 'file'.

Can anyone point me in the right direction to solve the problem please. Code is below:

Private Sub CommandButton1_Click()

Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = "C:\temp\Saved Invoices\"
FileName1 = Range("R12")
FileName2 = Range("S12")
ActiveWorkbook.SaveAs Filename:=Path & FileName1 & "-" & FileName2 & ".xlsm", FileFormat:=52 
End Sub

Upvotes: 1

Views: 7383

Answers (2)

David Rachwalik
David Rachwalik

Reputation: 181

If you would still like a prompt for your user, you can do so using a MsgBox. Below is a simple Yes/No prompt.

This code uses the Microsoft Scripting Runtime. If you don't already have this referenced, go to the Visual Basic Editor (ALT-F11) and in the Tools dropdown, select "References..." Tick the check-box next to "Microsoft Scripting Runtime" and then click the OK button.

The code below sets up the FileSystemObject, sets the path and filename you provided, and checks if that file already exists. If it doesn't find the file, it will SaveAs like before. If it already finds the file, it will give the user a MsgBox asking whether they'd like to overwrite the file or not. If they click YES, it does a SaveAs like before. Clicking NO will do nothing.

Private Sub CommandButton1_Click()
    Dim fso As FileSystemObject
    Dim Path As String
    Dim FileName1 As String
    Dim FileName2 As String
    Dim FilePath As String

Set fso = CreateObject("Scripting.FileSystemObject")

Path = "C:\temp\Saved Invoices\"
FileName1 = Range("R12")
FileName2 = Range("S12")
FilePath = Path & FileName1 & "-" & FileName2 & ".xlsm"

If fso.FileExists(FilePath) Then
    If MsgBox("Your file already exists in this location.  Do you want to replace it?", vbYesNo) = vbYes Then
        ActiveWorkbook.SaveAs FilePath, 52
    End If
Else
    ActiveWorkbook.SaveAs FilePath, 52
End If

End Sub

Application.DisplayAlerts provided by Thinkingcap is also a great method. The reason you received the error in the first place is because your script knew what to do when they clicked YES but had no direction for NO or CANCEL. When prompts are enabled, NO is the selected default for SaveAs. Wrapping your script in Application.DisplayAlerts = False not only disables prompts but changes the default value to YES, so it replaces the file every time without user input.

Upvotes: 0

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

All you need to do is to wrap the code in Application.DisplayAlerts

Application.DisplayAlerts = False
 Dim Path As String
 Dim FileName1 As String
 Dim FileName2 As String
 Path = "C:\temp\Saved Invoices\"
 FileName1 = Range("R12")
 FileName2 = Range("S12")
 ActiveWorkbook.SaveAs Filename:=Path & FileName1 & "-" & FileName2 & ".xlsm", FileFormat:=52
Application.DisplayAlerts = True

Upvotes: 2

Related Questions