GetSaveAsFilename Issues

[pujo]

I am trying to save a daily report to its current directory.
It looks as it is saving correctly however, the file is not there...
Here is the logic I am using, I just can't figure it out!
I am pulling my hair out over this!

Sub SaveDailyReport()
Dim S As Variant, MyName As String, fName() As String, fPath As String
Application.DisplayAlerts = False
MyName = Application.UserName
fName = Split(MyName, ",")
fPath = ActiveWorkbook.Path
  S = Application.GetSaveAsFilename(InitialFileName:=fPath & "\" & "Daily Report" & " - " & Format(Now(), "dd-mmm-yy"), FileFilter:="Excel Files (*.xlsb), *.xlsb")
    If S = False Then
      MsgBox ("Hey " & fName(1) & vbNewLine & "You canceled the save" & vbNewLine & _
      "Your data will be lost if you do not save!"), Buttons:=vbOKOnly + vbCritical, Title:="Save Canceled"
      Exit Sub
    End If
End Sub

 

[pujo]

When I Debug.Print S, It shows the correct folder path.
I just don't get it.....

 

[AC PORTA VIA]

try this

Sub SaveDailyReport()
Dim S As Variant, MyName As String, fName() As String, fPath As String
Application.DisplayAlerts = False
MyName = Application.UserName
fName = Split(MyName, ",")
fPath = ActiveWorkbook.Path
  S = Application.GetSaveAsFilename(InitialFileName:=fPath & "\" & "Daily Report" & " - " & Format(Now(), "dd-mmm-yy"), FileFilter:="Excel Files (*.xlsb), *.xlsb")
    ActiveWorkbook.SaveAs S
    If S = False Then
      MsgBox ("Hey " & fName(1) & vbNewLine & "You canceled the save" & vbNewLine & _
      "Your data will be lost if you do not save!"), Buttons:=vbOKOnly + vbCritical, Title:="Save Canceled"
      Exit Sub
    End If
End Sub

 

[dmt32]

Hi,

The answer to your issue is in the VBA Helpfile Application.GetSaveAsFilename method (Excel)

“Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.”

You need to add a line of code to save the file

Sub SaveDailyReport()
    Dim S             As Variant
    Dim MyName As String, fName() As String, fPath As String
    
    MyName = Application.UserName
    fName = Split(MyName, ",")
    fPath = ActiveWorkbook.Path
    S = Application.GetSaveAsFilename(InitialFileName:=fPath & "\" & "Daily Report" & " - " & Format(Now(), "dd-mmm-yy"), FileFilter:="Excel Files (*.xlsb), *.xlsb")
    
    If S = False Then
        MsgBox ("Hey " & fName(1) & vbNewLine & "You cancelled the save" & vbNewLine & _
               "Your data will be lost If you Do Not save!"), Buttons:=vbOKOnly + vbCritical, Title:="Save Cancelled"  
    Else
        'save workbook
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs S, xlExcel12
    End If
    Application.DisplayAlerts = True
End Sub

Hope Helpful

Dave

 

[pujo]

Don't I feel dumb.... I just missed it.
Appreciate the help!

 

[dmt32]

No worries we have all done it at sometime
Appreciate your feedback

Dave