VBA to cycle through dropdown list and export into pdf

[imazhary]

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!

 

[Yongle]

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 )

[COLOR=#000080]Sub Test1()[/COLOR]
Const NamedRangeName = "[COLOR=#ff0000]Choices[/COLOR]"
Const SheetName = [COLOR=#ff0000]"MyDashboardSheet[/COLOR]"
Const CellWithDropdown = "[COLOR=#ff0000]A1[/COLOR]"
Const PrintRange = "[COLOR=#ff0000]A1:E40[/COLOR]"
Const fPath = "[COLOR=#ff0000]C:\Folder\SubFolder[/COLOR]"

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
[COLOR=#000080]End Sub[/COLOR]

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 ] )

 

[tzallas]

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 )

[COLOR=#000080]Sub Test1()[/COLOR]
Const NamedRangeName = "[COLOR=#ff0000]Choices[/COLOR]"
Const SheetName = [COLOR=#ff0000]"MyDashboardSheet[/COLOR]"
Const CellWithDropdown = "[COLOR=#ff0000]A1[/COLOR]"
Const PrintRange = "[COLOR=#ff0000]A1:E40[/COLOR]"
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
[COLOR=#000080]End Sub[/COLOR]

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

 

[tzallas]

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...ml?highlight=prompt+user+file+export+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

 

[Yongle]

@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

Sub Test1()
Const NamedRangeName = "Choices"
Const SheetName = "MyDashboardSheet"
Const CellWithDropdown = "A1"
Const PrintRange = "A1:E40"
[COLOR=#ff0000]Const DefaultFolder = "C:\Test\PDF"[/COLOR]
Dim fPath As String, choice As Range, fName As String
[COLOR=#006400]'allow user to select folder[/COLOR]
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
      [COLOR=#ff0000]  .InitialFileName = DefaultFolder[/COLOR]
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With

[COLOR=#006400]'rest of code[/COLOR]
    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/vba-select-folder-with-msoFileDialogFolderPicker/

 

[tzallas]

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

 

[tzallas]

@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

Sub Test1()
Const NamedRangeName = "Choices"
Const SheetName = "MyDashboardSheet"
Const CellWithDropdown = "A1"
Const PrintRange = "A1:E40"
[COLOR=#ff0000]Const DefaultFolder = "C:\Test\PDF"[/COLOR]
Dim fPath As String, choice As Range, fName As String
[COLOR=#006400]'allow user to select folder[/COLOR]
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select destination folder for PDF export"
      [COLOR=#ff0000]  .InitialFileName = DefaultFolder[/COLOR]
        If .Show = -1 Then fPath = .SelectedItems(1)
    End With

[COLOR=#006400]'rest of code[/COLOR]
    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/vba-select-folder-with-msoFileDialogFolderPicker/

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.

 

[Yongle]

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