Forum Discussion

JPL_76's avatar
JPL_76
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 export these cells to a .pdf file so that they can be shared with stakeholders and I've got the vba working when the workbook 'lives' in my C: drive, but its a team tool, and therefore, 'lives' on Microsoft Teams. I'm using the VBA:

 

Sub SaveRangeAsPDF()

'Create and assign variables
Dim ws As Worksheet
Dim rng As Range

Set ws = Sheets("Sheet4")
Set rng = ws.Range("b2:ai38")

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Range("b2") & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"

End Sub

 

and when the workbook is on my C drive, when I run the report, it creates a .pdf with the filename of the customer and a timestamp in the same location that the workbook resides.

When I upload it to Teams, I get a 400 error as I assume, it doesn't know where to save the file.

 

What can I add to this VBA so that it brings up the SaveAs box, ideally pre-populated with the filename and .pdf extension. I've tried using

 

Application.getsaveasfilename InitialFilename:=Range("b2") & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"

 

but not having any luck at all. Any help would be gratefully received.

 

Thanks in advance,

excel
Macros and VBA
  • SnowMan55's avatar
    SnowMan55
    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

     

     

2 Replies

  • SnowMan55's avatar
    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's avatar
      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?

Resources