• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Export as pdf depends on number of validation list

Dear Excel Genius

The below code is exported as a pdf to the mentioned path with the specified range("B3:L37") and the name Q7.value as MMM-YYYY and O11.Value.pdf"

here the challenge is O11 cell has the dropdown validation list. Example values are "AAAA BBBBBB","CCCCC DDDDD EEEEEE","FFFFF GGGGGGGGG HH",....etc.,

I want to change the code to make the pdf depend on list values. If the validation list has 7 names then 7 pdf to be exported. if it is 100 then 100 pdf to be exported.

The file name and the path are the same. Please modify the code accordingly.

Code:
Sub ExportAsPDF()
Dim folderPath As String
folderPath = ActiveWorkbook.Path & Application.PathSeparator & "Payslip/"
Dim yearMonth As String
Dim pdfFileName As String
pdfFileName = Format(ThisWorkbook.Worksheets("Payslip").Range("Q7").Value, "MMM-YYYY") & ThisWorkbook.Worksheets("Payslip").Range("O11").Value & ".pdf"
Dim pdfFilePath As String
pdfFilePath = folderPath & pdfFileName
If Dir(folderPath, vbDirectory) = "" Then
    MsgBox "Folder not found: " & folderPath
    Exit Sub
End If
If Dir(pdfFilePath) <> "" Then
    Dim response As VbMsgBoxResult
    response = MsgBox("File already exists. Do you want to overwrite it?", vbYesNo)
    If response <> vbYes Then
        Exit Sub
    End If
End If
Dim exportRange As Range
On Error Resume Next
Set exportRange = ThisWorkbook.Worksheets("Payslip").Range("B3:L37")
On Error GoTo 0
If exportRange Is Nothing Then
    MsgBox "Worksheet 'Payslip' or range 'B3:L37' not found"
    Exit Sub
End If
exportRange.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=pdfFilePath, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
MsgBox "PDF exported to " & pdfFilePath
End Sub
 

Attachments

  • Dropdown List PDF.xlsm
    22.6 KB · Views: 1
Hello, as the VBA procedure is located in the Sheet1 (Payslip) worksheet module so referencing the same worksheet is totally useless !​
And do not forget to well do the print page setup before to launch the procedure …​
According to your attachment a beginner starter VBA demonstration to paste only to the same worksheet module :​
Code:
Sub Demo1()
    Dim P$, F$, V
        P = ThisWorkbook.Path & "\Payslip\":  If Dir(P, 16) = "" Then MsgBox "Path not found: " & P, 16: Exit Sub
        F = P & Format([Q7], "mmm-yyyy ")
        V = Split([O11].Validation.Formula1, "!")
        Application.DisplayAlerts = False
    For Each V In Sheets(Mid(Replace(V(0), "'", ""), 2)).Range(V(1)).Value
        [B3:L37].ExportAsFixedFormat 0, F & V & " .pdf", 0
    Next
        Application.DisplayAlerts = True
        MsgBox "PDF saved to " & P, 64
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Dear @Marc L
it is working great! Only it has to refresh or calculate each list name changes and then export to pdf. Now it is exported pdf with the same data for all the names.
After changing the validation list names, B3:L37 need to update the data according to the name selected.
Please modify the same and provide a new code.
 
Sorry Boss, I forgot to update it in the initial post as I thought if the validation name changes then values also will get updated accordingly.

Please see the attached sheet and exported pdf as well (Only 4 PDFs I attached for example) it is exported the same content for all the names in the validation list.
 

Attachments

  • Dropdown List PDF.xlsm
    23 KB · Views: 2
  • Mar-2023 AAAAA AAAAA .pdf
    9.1 KB · Views: 2
  • Mar-2023 BB BBB .pdf
    9.1 KB · Views: 1
  • Mar-2023 BBB .pdf
    9.1 KB · Views: 0
  • Mar-2023 FFF HHH GGG .pdf
    9.1 KB · Views: 0
It is working like a magic boss.

without knowing this simple trick I tried Calculate, Do Events, and pause one second to calculate before export. but nothing has resulted.
 
That's just about reproducing the same action than manually like any Excel beginner can achieve with the Macro Recorder …​
 
Back
Top