VBA Save as

[dannyok90]

Hi all,

See the code below, its working fine apart from if the user selects 'No' in the dialogue box, see the pictures attached. it comes up with some run time error, how do I stop this?

Thanks

 Public Sub SaveAsC3()
 Dim ThisFile As String, DoF As String
 ThisFile = Range("C3").Value
 DoF = Range("V2").Value
 ActiveWorkbook.Save
 ActiveWorkbook.SaveAs Filename:="C:\Users\OKEED3\Documents\Templates\Saved\" & ThisFile & " " & "PSR" & " " & DoF, FileFormat:=xlOpenXMLWorkbookMacroEnabled
 ActiveWorkbook.Close
 End Sub

[/url]

 

[JackDanIce]

Try:

 Public Sub SaveAsC3()
 Dim ThisFile As String, DoF As String
 ThisFile = Range("C3").Value
 DoF = Range("V2").Value
 ActiveWorkbook.Save
 On Error Resume Next
 ActiveWorkbook.SaveAs FileName:="C:\Users\OKEED3\Documents\Templates\Saved\" & ThisFile & " " & "PSR" & " " & DoF, FileFormat:=xlOpenXMLWorkbookMacroEnabled
 On Error GoTo 0
 ActiveWorkbook.Close
 End Sub

 

[dannyok90]

Try:

 Public Sub SaveAsC3()
 Dim ThisFile As String, DoF As String
 ThisFile = Range("C3").Value
 DoF = Range("V2").Value
 ActiveWorkbook.Save
 On Error Resume Next
 ActiveWorkbook.SaveAs FileName:="C:\Users\OKEED3\Documents\Templates\Saved\" & ThisFile & " " & "PSR" & " " & DoF, FileFormat:=xlOpenXMLWorkbookMacroEnabled
 On Error GoTo 0
 ActiveWorkbook.Close
 End Sub

Hi JackDanIce,

Thanks! It sort of works but now when I click no, instead of returning the runtime error it just returns to excel with the workbook closed?

 

[VishMishra]

Hi,

Can you please specify your intention? for example:
1. Do you even like to show this message to user to choose? say yes or no or cancel?
2. Shouldn't you either replace it always? or simply send a warning message to change the file name as it exists already?

Let me know your expectation, then i can provide you the correct solution.

 

[dannyok90]

Hi,

Can you please specify your intention? for example:
1. Do you even like to show this message to user to choose? say yes or no or cancel?
2. Shouldn't you either replace it always? or simply send a warning message to change the file name as it exists already?

Let me know your expectation, then i can provide you the correct solution.

Hi,

Thankyou for getting back to me.

Basically the file is stored on a network multiple times in different locations (but with different details). I would like the file to save in its original location so next time its edited it still has the same details in and only requires minor tweaks. but I would also like to save it in another location, one that I have access to so I can see all the updated documents. this way instead of going to everybody's different file locations and printing them off I can just go to my file location and there will be the most recent copy available for me to view (I know which is the most recent copy because the VBA is referencing a date in the spreadsheet along with an ID number)

the code is doing what im requiring but say if the 'submit' button is clicked and they realize they have made an error and click 'no' to the save as dialogue box it closes the workbook instead of letting them go back in, edit the mistake and submit again.

Is that clear or am I just rambling on? haha

Thanks!

 

[VishMishra]

Hi,

Thankyou for getting back to me.

Basically the file is stored on a network multiple times in different locations (but with different details). I would like the file to save in its original location so next time its edited it still has the same details in and only requires minor tweaks. but I would also like to save it in another location, one that I have access to so I can see all the updated documents. this way instead of going to everybody's different file locations and printing them off I can just go to my file location and there will be the most recent copy available for me to view (I know which is the most recent copy because the VBA is referencing a date in the spreadsheet along with an ID number)

the code is doing what im requiring but say if the 'submit' button is clicked and they realize they have made an error and click 'no' to the save as dialogue box it closes the workbook instead of letting them go back in, edit the mistake and submit again.

Is that clear or am I just rambling on? haha

Thanks!

yes, it is clear now. I guess then this will do what you are expecting:

If yes/no choice you want to give it to user everytime then, why only a file already exists. In fact following code will always prompt and check what user wants and based on his/her selection, it will act.

let me know if this helps.

Public Sub SaveAsC3()
    Dim ThisFile As String, DoF As String
    ThisFile = Range("C3").Value
    DoF = Range("V2").Value
    fName = "[COLOR=#333333][I]C:\Users\OKEED3\Documents\Templates\Saved\" & ThisFile & " " & "PSR" & " " & DoF[/I][/COLOR]
    ActiveWorkbook.Save
    On Error GoTo err
    Application.DisplayAlerts = False
    If (MsgBox("Are you sure you want to save the changes?", vbYesNo, "Decision")) = vbYes Then
        With ActiveWorkbook
            .SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            .Close
        End With
    End If
    err:
    Application.DisplayAlerts = True
End Sub

 

[dannyok90]

VishMishra, thank you, that works like an absolute dream!