VBA just isn't saving files to folder.

[RockandGrohl]

Hi guys, I'm trying to do something relatively straightforward.

A user is opening a Template that they then add data to. On the Save_Click button press, I want it to verify the user is one of four able to execute the macro, then collect Project Information, assemble it into a filename, and open the SaveAs dialogue box for the user to save the file with the pre-written name.

What's happening is that this line gives "object variable or with block variable not set"

            fPth = Application.GetSaveAsFilename(InitialFileName:=fpath & fname & ".xlsm") ', FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")

It also isn't pre-filling the save dialogue box.


I've unfortunately erased some progress I made with chatGPT haha.. whoops. Can someone help me back on the right path? Thanks.

Private Sub Save_Click()

Application.ScreenUpdating = False

Dim ws As Worksheet, ct As Worksheet
Dim wb As Workbook
Dim pnum As String, pname As String, pgate As String, uname As String, ver As String, fpath As String, fname As String
Dim fPth As String

uname = Environ("username")
fpath = "C:\Users\" & uname & "\Company\Cost Estimating - Documents\Project Estimates\"


Set ct = Worksheets("Capture Template")

Select Case uname
        Case Is = "Me", "Colleague1", "Colleague2", "Colleague3"
            pnum = ct.Range("B2").Value
            pname = ct.Range("B3").Value
            pgate = Left(ct.Range("D4").Value, 3)
            ver = ct.Range("U2").Value
            fname = pnum & " - " & pname & " - " & pgate & " - " & ver
           
            'Remove/replace illegal characters
           
            pnum = Replace(pnum, "/", "")
            pnum = Replace(pnum, "\", "")
            pnum = Replace(pnum, "-", "")
            pnum = Replace(pnum, " ", "")
            pname = Replace(pname, "\", "")
            pname = Replace(pname, "/", "")
            pname = Replace(pname, "&", "and")
           
           
            ' Use Application.GetSaveAsFilename to get the file path
            fPth = Application.GetSaveAsFilename(InitialFileName:=fpath & fname & ".xlsm") ', FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")

            ' Check if the user canceled the save operation
            If fPth <> "False" Then
                ' Save the workbook
                ThisWorkbook.SaveAs fPth
            End If
           
        Case Else
            MsgBox "This button is for Internal Staff only. Access Is Denied. If you require access please contact [my name]"
End Select

Application.ScreenUpdating = True

End Sub

 

[Micron]

Can't see the reason just based on the code. Did you step through the code (F8) and check the variable and reference values? One thing I might try is changing fPth to a variant that since the dialog returns a variant. If this is only happening when user does not make a selection, the dialog returns a Boolean so that could be it.

 

[RockandGrohl]

Can't see the reason just based on the code. Did you step through the code (F8) and check the variable and reference values? One thing I might try is changing fPth to a variant that since the dialog returns a variant. If this is only happening when user does not make a selection, the dialog returns a Boolean so that could be it.

Thanks, that allows the code to run.

The filename however is not pre-populated, and the file saves as "all files" with no extension

 

[Micron]

Don't know if this reference will help or not as it seems you're describing 2 different issues.

Application.GetSaveAsFilename method (Excel)

Office VBA reference topic

learn.microsoft.com

I have to go out for a while. If you remain stuck consider posting files in a drop box and I will probably find time to look at it later. Would need template as well I think.