Forum Discussion

Copper Contributor

Sep 25, 2023

Solved

VBA to ask for a file location to be manually selected

I have a worksheet that is set out as a report - users pick a customer from a drop down list and the rest of the report is populated based on cell contents on many other worksheets. I am trying to e...

Sep 25, 2023

The Application.GetSaveAsFilename method is intended to return a value, but your syntax cannot receive a value. It makes me wonder what you are expecting the method to do.

Use syntax that captures a value, such as:

    Dim vntFileSpec As Variant
    '
    vntFileSpec = Application.GetSaveAsFilename(InitialFileName:=Range("b2") _
            & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf")
    If vntFileSpec = False Then
        '...the user decided not to save.  Exit the procedure or do
        '    whatever else is appropriate.
    Else
        'Use vntFileSpec in your <workbook>.SaveAs or <worksheet>.SaveAs method
        'or <range>.ExportAsFixedFormat method or whatever.
    End If

SnowMan55

Bronze Contributor

Sep 25, 2023

JPL_76

The Application.GetSaveAsFilename method is intended to return a value, but your syntax cannot receive a value. It makes me wonder what you are expecting the method to do.

Use syntax that captures a value, such as:

    Dim vntFileSpec As Variant
    '
    vntFileSpec = Application.GetSaveAsFilename(InitialFileName:=Range("b2") _
            & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf")
    If vntFileSpec = False Then
        '...the user decided not to save.  Exit the procedure or do
        '    whatever else is appropriate.
    Else
        'Use vntFileSpec in your <workbook>.SaveAs or <worksheet>.SaveAs method
        'or <range>.ExportAsFixedFormat method or whatever.
    End If

JPL_76
Copper Contributor
Sep 26, 2023
Thanks - I'm an absolute newbie to VBA and I've used Google to help me with the VBA I've referenced above, as you would expect there are a number of one-size-fits-all options but nothing specific for what I want/ need. As a result of being a noob, I'm not quite sure what to do with the VBA you've provided, does that replace my script altogether, or do I embed it within what I've done?