Check if file is saved.

[S Oberlander]

I have the following sub to save my excel file as a binary workbook. It works great, I just want to add that it should check if my excel file is actually saved already, and if yes it should just save the current file and not save a copy.
I am looking to replace the default save button on the quickaccess toolbar, I find the method used there annoying and long winding...

Sub SaveBinary()

    Dim fname As Variant
    Dim FileFormatValue As Long
     
        fname = Application.GetSaveAsFilename(InitialFileName:=ActiveCell, filefilter:= _
            " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
            " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
            " Excel 2000-2003 Workbook (*.xls), *.xls," & _
            " Excel Binary Workbook (*.xlsb), *.xlsb", _
            FilterIndex:=4, Title:="Save as xlsb")

        'Find the correct FileFormat that match the choice in the "Save as type" list
            Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, ".", , 1)))
            Case "xls": FileFormatValue = 56
            Case "xlsx": FileFormatValue = 51
            Case "xlsm": FileFormatValue = 52
            Case "xlsb": FileFormatValue = 50
            Case Else: FileFormatValue = 0
            End Select
         
         If fname = False Then
         Exit Sub
         End If
         
           ActiveWorkbook.SaveAs fname, FileFormat:=FileFormatValue, CreateBackup:=False
           ActiveWorkbook.Save
           
End Sub

 

[JEC]

Something like this

Sub jec()
  If ThisWorkbook.Path = "" Then
    Save
  Else
    savecopy
  End If
End Sub

 

[S Oberlander]

If ThisWorkbook.Path = "" Then

but I don't necessarily know the path offhand. I would need to tell vba to find it

 

[S Oberlander]

I wrote the following line of code:

x = Mid(ActiveWorkbook.FullName, InStr(1, ActiveWorkbook.FullName, "/"), 1)

If the file is saved, I will get the slash assigned to x and will know to just save changes and skip the save as. But if it isn't saved I get a runtime error 5 because it can't find the slash. How do I tell vba to just continue with the code if it cant find the slash? I do not want to use On Error Resume Next

 

[S Oberlander]

Update
I tweaked the code a bit:

If Mid(ActiveWorkbook.FullName, InStr(1, ActiveWorkbook.FullName, "\"), 1) = "\" Then
ActiveWorkbook.Save
Else
'the original code
End If

It works if the file is already saved but I get runtime error 5 if it isn't saved cuz there is no slash yet in the FullName. How can I tell vba to ignore this error other than using On Error Resume Next?

 

[S Oberlander]

Oh well I'm just not having patience to wait for a proper answer. For now I'll use this:

On Error GoTo this
    If Mid(ActiveWorkbook.FullName, InStr(1, ActiveWorkbook.FullName, "\"), 1) = "\" Then
    ActiveWorkbook.Save
this:
On Error GoTo 0
'original code
    End If

 

[BSALV]

If Not ActiveWorkbook.Saved Then  
MsgBox "This workbook contains unsaved changes."  
End If

 

[S Oberlander]

If Not ActiveWorkbook.Saved Then 
MsgBox "This workbook contains unsaved changes." 
End If

This triggers the msgbox even if I took a previously saved file and edited it. I need something to differentiate between a file that is not saved anywhere permanent and a file that ActiveWorkbook.Save will actually do what I want it to.
(I found that running ActiveWorkbook.Save on a previously unsaved file, will save it, I just haven't yet figured out where to.)

 

[BSALV]

i think you want this,
- if no changes since last save of the main workbook, save only workbook
- if changes, then ask for copy-name, if name given, save also as copy

Sub SaveBinary()

     Dim fname As Variant, b
     Dim FileFormatValue As Long

     With ActiveWorkbook                                        'your workbook
          b = .Saved                                            'flag  "no changings since the last save"
          If Not b Then
               fname = Application.GetSaveAsFilename(InitialFileName:=ActiveCell, filefilter:=" Excel Macro Free Workbook (*.xlsx), *.xlsx," & " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & " Excel 2000-2003 Workbook (*.xls), *.xls," & " Excel Binary Workbook (*.xlsb), *.xlsb", FilterIndex:=4, Title:="Save as xlsb")
               Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, ".", , 1)))     'Find the correct FileFormat that match the choice in the "Save as type" list
                    Case "xls": FileFormatValue = 56
                    Case "xlsx": FileFormatValue = 51
                    Case "xlsm": FileFormatValue = 52
                    Case "xlsb": FileFormatValue = 50
                    Case Else: FileFormatValue = 0
               End Select
               If fname <> False Then                           'you choose a filename
                    ActiveWorkbook.SaveAs fname, FileFormat:=FileFormatValue, CreateBackup:=False
               Else
                    b = True                                    'set flag "only original"
               End If
          End If
          .Save
          MsgBox "Original " & IIf(b, "only ", "and copy ") & "Saved", vbInformation, "Result Saving"
     End With
End Sub

 

[S Oberlander]

i think you want this,
- if no changes since last save of the main workbook, save only workbook
- if changes, then ask for copy-name, if name given, save also as copy

Close, I do not want to invoke the save as dialogue, only if this workbook has not been previously saved.