Results 1 to 8 of 8

Thread: VBA to cycle through dropdown list and export into pdf

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to cycle through dropdown list and export into pdf

    Hi all

    I have created a dashboard which updates based on a dropdown list. I need a macro which will cycle through all of the options in the dropdown list and also export a pdf copy of each updated dashboard. The current excel model takes a considerable time to recalculate after each dropdown change so I need a way of automating it.

    Thanks in advance for any advice!

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,411
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to cycle through dropdown list and export into pdf

    Use Something like this (assumed that dropdown values stored in a named range - if that is incorrect let me know )

    To test
    - amend every constant values to match your own and run the code (- fpath is the full path to save folder )


    Code:
    Sub Test1()
    Const NamedRangeName = "Choices"
    Const SheetName = "MyDashboardSheet"
    Const CellWithDropdown = "A1"
    Const PrintRange = "A1:E40"
    Const fPath = "C:\Folder\SubFolder"
    
    Dim choice As Range
    With Sheets(SheetName)
        For Each choice In Range(NamedRangeName)
            .Range(CellWithDropdown) = choice
            fname = choice & Format(Date, " yymmdd") & ".pdf"
            .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fname
        Next
    End With
    End Sub
    If, for any reason you cannot get it running, post your full code enclosed in code tags
    (by clicking first on #icon - which is found above post window - and posting your code between the code tags [CODE ] code goes here [/CODE ] )
    Last edited by Yongle; May 23rd, 2018 at 09:48 AM.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to cycle through dropdown list and export into pdf

    Quote Originally Posted by Yongle View Post
    Use Something like this (assumed that dropdown values stored in a named range - if that is incorrect let me know )

    To test
    - amend every constant values to match your own and run the code (- fpath is the full path to save folder )


    Code:
    Sub Test1()
    Const NamedRangeName = "Choices"
    Const SheetName = "MyDashboardSheet"
    Const CellWithDropdown = "A1"
    Const PrintRange = "A1:E40"
    Const fPath = "C:\Folder\SubFolder"
    
    Dim choice As Range
    With Sheets(SheetName)
        For Each choice In Range(NamedRangeName)
            .Range(CellWithDropdown) = choice
            fname = choice & Format(Date, " yymmdd") & ".pdf"
            .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fname
        Next
    End With
    End Sub
    If, for any reason you cannot get it running, post your full code enclosed in code tags
    (by clicking first on #icon - which is found above post window - and posting your code between the code tags [CODE ] code goes here [/CODE ] )

    So this worked perfectly for me, plugged in my Const where needed and all is well.
    I also added a dynamic Named range with an offset function, so the drop down list automatically fills up as new rows are added in my case.

    My question is how can we change it so that the user is propmted for a folder destination for the PDF exports to be saved in?

    i.e. User clicks macro button, file browser window pops up, "select destination folder for pdf export", browse+select, click ok, macro continues and finishes as usual.

    Thank you

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to cycle through dropdown list and export into pdf

    Quote Originally Posted by tzallas View Post
    My question is how can we change it so that the user is propmted for a folder destination for the PDF exports to be saved in?
    I should mention that I did find this post https://www.mrexcel.com/forum/excel-...xport+location

    It seems to be similar to what I sort of need (?) , but I would have no idea as to how to incorporate it into the original macro above and maintian functionality

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,411
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to cycle through dropdown list and export into pdf

    @tzallas
    Welcome to the board
    - old threads are often not monitored (and this one is very old)
    - (for the future) you are more likely to get a response by starting a new thread to ask your question (with a link to an old thread if helpful)

    The RED lines set the initial folder
    - amend the folder path to whichever is relevant to you
    - delete both if not required

    Code:
    Sub Test1()
    Const NamedRangeName = "Choices"
    Const SheetName = "MyDashboardSheet"
    Const CellWithDropdown = "A1"
    Const PrintRange = "A1:E40"
    Const DefaultFolder = "C:\Test\PDF"
    Dim fPath As String, choice As Range, fName As String
    'allow user to select folder
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select destination folder for PDF export"
            .InitialFileName = DefaultFolder
            If .Show = -1 Then fPath = .SelectedItems(1)
        End With
    
    'rest of code
        If fPath <> "" Then
            With Sheets(SheetName)
                For Each choice In Range(NamedRangeName)
                    .Range(CellWithDropdown) = choice
                    fName = choice & Format(Date, " yymmdd") & ".pdf"
                    .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
                Next
            End With
        Else
            MsgBox "No folder selected"
        End If
    
    End Sub

    You may find this link helpful
    https://wellsr.com/vba/2016/excel/vb...gFolderPicker/
    Last edited by Yongle; May 19th, 2019 at 02:36 AM.

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to cycle through dropdown list and export into pdf

    Thank you for the reply, fair point about the old post comment. I did notice it was quite old but saw that you had last edited it in 2018, so I gave it a shot.
    Will follow your advice in the future.

    I will try your amendments this evening and let you know if all goes well


  7. #7
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: VBA to cycle through dropdown list and export into pdf

    Quote Originally Posted by Yongle View Post
    @tzallas
    Welcome to the board
    - old threads are often not monitored (and this one is very old)
    - (for the future) you are more likely to get a response by starting a new thread to ask your question (with a link to an old thread if helpful)

    The RED lines set the initial folder
    - amend the folder path to whichever is relevant to you
    - delete both if not required

    Code:
    Sub Test1()
    Const NamedRangeName = "Choices"
    Const SheetName = "MyDashboardSheet"
    Const CellWithDropdown = "A1"
    Const PrintRange = "A1:E40"
    Const DefaultFolder = "C:\Test\PDF"
    Dim fPath As String, choice As Range, fName As String
    'allow user to select folder
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select destination folder for PDF export"
            .InitialFileName = DefaultFolder
            If .Show = -1 Then fPath = .SelectedItems(1)
        End With
    
    'rest of code
        If fPath <> "" Then
            With Sheets(SheetName)
                For Each choice In Range(NamedRangeName)
                    .Range(CellWithDropdown) = choice
                    fName = choice & Format(Date, " yymmdd") & ".pdf"
                    .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
                Next
            End With
        Else
            MsgBox "No folder selected"
        End If
    
    End Sub

    You may find this link helpful
    https://wellsr.com/vba/2016/excel/vb...gFolderPicker/
    Worked perfectly!!
    I noodled around and also added a msgbox for the users so it is clear what the file browser is asking from them (since the browser title is hard to catch, this makes them press ok first befoe selecting the destination folder) and its all working like clockwork!

    Thank you

    Now to incorporate this with only one file destination prompt for all and worksheets closing after completion, the head scratching begins.

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,411
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to cycle through dropdown list and export into pdf

    Like I said earlier , start a new thread ....

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •