Forum Discussion

mydataismeaningless's avatar
mydataismeaningless
Copper Contributor
Nov 22, 2021
Solved

Export to PDF Macro Naming issue XLOOKUP of firstname&&" "&lastname

Hello there,

 

I have a macro that exports to PDF down a list of employees. I can successfully export each report with the employee ID in the filename, but I'm trying to add the full name as well.
The ID is pulled from cell D4 of the report.
I have an xlookup pulling firstname&" "&lastname in the report's cell B4. How do I get the value from B4 (John Smith) to end up in each file name? example: 100321_John Smith.pdf

I can't find the correct code to do this. I either end up with reports with blank names, or correct reports with filenames that only have the last name.

My Macro:

Sub OGExport2PDF()
Dim wshReport As Worksheet
Dim wshData As Worksheet
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngID As Long
Dim strPath As String
Application.ScreenUpdating = False
Set wshReport = Worksheets("Total Comp & Benefits Statement")
Set wshData = Worksheets("Salary and Benefits")
strPath = ThisWorkbook.Path & Application.PathSeparator
lngLastRow = wshData.Range("D" & wshData.Rows.Count).End(xlUp).Row
For lngRow = 3 To lngLastRow
lngID = wshData.Range("D" & lngRow).Value
wshReport.Range("D4").Value = lngID
wshReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & lngID & "_" & ".pdf"
Next lngRow
Application.ScreenUpdating = True
End Sub

developer
excel
Formulas and Functions
Macros and VBA
Office Scripts
  • DKoontz's avatar
    DKoontz
    Nov 22, 2021

    mydataismeaningless 

     

    Hi, try this. I just added a nameID string that works the same way lngID works and gets called into the PDF export. Can't perfectly test without the workbook but let me know what works and what doesn't and we can narrow it down more! 

    Sub OGExport2PDF()
    
    Dim wshReport As Worksheet, wshData As Worksheet
    Dim lngRow As Long, lngLastRow As Long, lngID As Long
    Dim strPath As String, nameID As String
    
    Application.ScreenUpdating = False
    
    Set wshReport = Worksheets("Total Comp & Benefits Statement")
    Set wshData = Worksheets("Salary and Benefits")
    
    strPath = ThisWorkbook.Path & Application.PathSeparator
    lngLastRow = wshData.Range("D" & wshData.Rows.Count).End(xlUp).Row
    
    For lngRow = 3 To lngLastRow
        lngID = wshData.Range("D" & lngRow).Value
        nameID = wshData.Range("B" & lngRow).Value
        wshReport.Range("D4").Value = lngID
        wshReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & lngID & "_" & nameID & ".pdf"
    Next lngRow
    
    Application.ScreenUpdating = True
    
End Sub

     

2 Replies

  • DKoontz's avatar
    DKoontz
    Iron Contributor
    Nov 22, 2021

    mydataismeaningless 

     

    Hi, try this. I just added a nameID string that works the same way lngID works and gets called into the PDF export. Can't perfectly test without the workbook but let me know what works and what doesn't and we can narrow it down more! 

    Sub OGExport2PDF()
    
    Dim wshReport As Worksheet, wshData As Worksheet
    Dim lngRow As Long, lngLastRow As Long, lngID As Long
    Dim strPath As String, nameID As String
    
    Application.ScreenUpdating = False
    
    Set wshReport = Worksheets("Total Comp & Benefits Statement")
    Set wshData = Worksheets("Salary and Benefits")
    
    strPath = ThisWorkbook.Path & Application.PathSeparator
    lngLastRow = wshData.Range("D" & wshData.Rows.Count).End(xlUp).Row
    
    For lngRow = 3 To lngLastRow
        lngID = wshData.Range("D" & lngRow).Value
        nameID = wshData.Range("B" & lngRow).Value
        wshReport.Range("D4").Value = lngID
        wshReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & lngID & "_" & nameID & ".pdf"
    Next lngRow
    
    Application.ScreenUpdating = True
    
End Sub

     

Resources