Nov 22 2021 10:38 AM
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
Nov 22 2021 12:09 PM
See Finding a way to generate "report" exports (PDF) down a list of ID's where you also asked this.
Nov 22 2021 12:23 PM
Solution
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
Nov 22 2021 12:23 PM
Solution
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