VB Script to Save Results to a single PDF Fila

[amkkhan]

this small VBA Chunk iterates from roll no 1 to last i(collecting data from sheet1 and getting results.
but unfortunately, it prints only the last result instead of all the iterated results.
All I want is just to export all the iterated results into a single pdf file on the said path.

Sub printPDF()
For n = 5 To 15
RollNo = Sheets("Sheet1").Cells(n, "A")
StudentName = Sheets("Sheet1").Cells(n, "C")
Sheets("Results").Cells(13, "M") = RollNo
Next
Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & RollNo & "-" & StudentName & ".pdf", , , False, , , False
End Sub

 

[John_w]

I have a list for 50 entries maximum. Every class have different number of students and every class have different strength.
When it starts from Roll No 1 to n...
It creates single sheet of 50 pages out of which only 23 have data(as I told earlier. For example if strength is 23)
For example there are 23 students enrolled in class. Now I am getting single pdf file of 50 pages out of which only 23 are filled with data and rest 27 are blank.

Looking at your uploaded workbook - thanks @HaHoBe.

You need to be more precise when talking about 'single sheet', 'pages' and 'blank'. With roll numbers 33 and higher, the "Result_Card" sheet isn't blank, because it still has all the row and column headings, etc., only the data cells are empty.

Try this slightly modified macro, which loops through rows 5 to 54 on the "Award List" sheet and if the column C cell (Name) isn't empty updates M13 on the "Result_Card" sheet to generate the result for that roll no. and copies the "Result_Card" sheet for later exporting to the single PDF file.

Public Sub Create_PDF()
  
    Dim PDFsheets As String
    Dim n As Long
  
    Application.ScreenUpdating = False
  
    PDFsheets = ""
    For n = 5 To 54
        If Not IsEmpty(Worksheets("Award List").Cells(n, "C").Value) Then
            Worksheets("Result_Card").Range("M13").Value = Worksheets("Award List").Cells(n, "A").Value
            Worksheets("Result_Card").Copy After:=Worksheets(Worksheets.Count)
            PDFsheets = PDFsheets & ActiveSheet.Name & ","
        End If
    Next
  
    Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select
  
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\All Results.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
  
    Application.ScreenUpdating = True
  
    MsgBox "Created " & ThisWorkbook.Path & "\All Results.pdf"
  
End Sub

 

[HaHoBe]

Hi amakkhan,

maybe

Sub printPDF_mod()
Dim n As Long

For n = 5 To 15
  With Sheets("Sheet1")
    Sheets("Results").Cells(13, "M") = .Cells(n, "A")
    Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & .Cells(n, "A") & "-" & .Cells(n, "C") & ".pdf", , , False, , , False
  End With
Next n
End Sub

Ciao,
Holger

 

[amkkhan]

Hi amakkhan,

maybe

Sub printPDF_mod()
Dim n As Long

For n = 5 To 15
  With Sheets("Sheet1")
    Sheets("Results").Cells(13, "M") = .Cells(n, "A")
    Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & .Cells(n, "A") & "-" & .Cells(n, "C") & ".pdf", , , False, , , False
  End With
Next n
End Sub

Ciao,
Holger

thanks @HaHoBe it worked fine but it exported all 11 files with separate name. I wanted all combined into a single pdf,

 

[amkkhan]

Hi amakkhan,

maybe

Sub printPDF_mod()
Dim n As Long

For n = 5 To 15
  With Sheets("Sheet1")
    Sheets("Results").Cells(13, "M") = .Cells(n, "A")
    Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & .Cells(n, "A") & "-" & .Cells(n, "C") & ".pdf", , , False, , , False
  End With
Next n
End Sub

Ciao,
Holger

I am totally new to VB and macros.
But my immature opinion is that...
As per your code(line no 6 of your code), I believe we are making mistake while exporting files with different names to we get multiple files instead of a single pdf file.
If we name all the queued files as a single file then we can get our expectation.

 

[HaHoBe]

Hi amkkhan,

as you rely in only one worksheet to be put into one pdf more than one time according to my knowledge you would need Adobe Acrobat to do so. If you want to work with VBA only you would have to work around that with either copying all information from Result to one collector sheet and use ExportAsFixedFormat for that sheet or copy Result to the end, giving it a tempname, group all worksheets starting with that tempname, export to PDF, delete all temp sheets. Code may look like this:

Sub printPDF_mod2()
Dim lngRow As Long
Dim strTempName As String
Dim wks As Worksheet

strTempName = "Test " & Format(Date, "yyyymmdd") & " - "

Application.ScreenUpdating = False
For lngRow = 5 To 15
  With Sheets("Sheet1")
    Sheets("Results").Cells(13, "M") = .Cells(lngRow, "I")
    Sheets("Results").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = strTempName & Worksheets.Count
  End With
Next lngRow
  
Worksheets(Worksheets.Count).Activate
For Each wks In Worksheets
  With wks
    If Left(.Name, Len(strTempName)) = strTempName Then .Select Replace:=False
  End With
Next wks

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="C:\Result\Total " & Format(Now, "yymmdd_hhmmss") & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False '

Application.DisplayAlerts = False
For Each wks In Worksheets
  With wks
    If Left(.Name, Len(strTempName)) = strTempName Then .Delete
  End With
Next wks
With Application
  .DisplayAlerts = True
  .ScreenUpdating = False
End With

End Sub

Ciao,
Holger

 

[amkkhan]

Hi amkkhan,

as you rely in only one worksheet to be put into one pdf more than one time according to my knowledge you would need Adobe Acrobat to do so. If you want to work with VBA only you would have to work around that with either copying all information from Result to one collector sheet and use ExportAsFixedFormat for that sheet or copy Result to the end, giving it a tempname, group all worksheets starting with that tempname, export to PDF, delete all temp sheets. Code may look like this:

Sub printPDF_mod2()
Dim lngRow As Long
Dim strTempName As String
Dim wks As Worksheet

strTempName = "Test " & Format(Date, "yyyymmdd") & " - "

Application.ScreenUpdating = False
For lngRow = 5 To 15
  With Sheets("Sheet1")
    Sheets("Results").Cells(13, "M") = .Cells(lngRow, "I")
    Sheets("Results").Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = strTempName & Worksheets.Count
  End With
Next lngRow
 
Worksheets(Worksheets.Count).Activate
For Each wks In Worksheets
  With wks
    If Left(.Name, Len(strTempName)) = strTempName Then .Select Replace:=False
  End With
Next wks

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="C:\Result\Total " & Format(Now, "yymmdd_hhmmss") & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False '

Application.DisplayAlerts = False
For Each wks In Worksheets
  With wks
    If Left(.Name, Len(strTempName)) = strTempName Then .Delete
  End With
Next wks
With Application
  .DisplayAlerts = True
  .ScreenUpdating = False
End With

End Sub

Ciao,
Holger

hi @HaHoBe your script worked as it was expected to. It exported all the generated results into a single pdf file on the said location, but I am sorry to inform you all the pages were blank. Attached image may explain it better to you. [Information] Your last piece of code that was exporting 11 separate files was importing results also. Maybe you should put that chunk into new code at some suitable place

 

[HaHoBe]

Hi amkkhan,

that is the reason I put in the codeline

Worksheets(Worksheets.Count).Activate

as I was getting empty sheets without it as well yesterday prior to adding the line.

You may try setting this codeline behind the grouping like

For Each wks In Worksheets
  With wks
    If Left(.Name, Len(strTempName)) = strTempName Then .Select Replace:=False
  End With
Next wks
Worksheets(Worksheets.Count).Activate

I uploaded the first bit of a test run I just did on my laptop with Windows11 and Excel2019. If the change above will not work I can only think of getting the setting of the recent printer, witch to Microsoft Print to PDF, use ActiveWindow.SelectedSheets.Printout, and switch back to the original printer thereafter.

Ciao,
Holger

 

[amkkhan]

hi @HaHoBe I did the changes but still getting blank pdf file.
Would you mind sharing the code for once completely.
the problem is your this code

Sub printPDF_mod()
Dim n As Long
For n = 5 To 15
  With Sheets("Sheet1")
    Sheets("Results").Cells(13, "M") = .Cells(n, "A")
    Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & .Cells(n, "A") & "-" & .Cells(n, "C") & ".pdf", , , False, , , False
  End With
Next n
End Sub

was iterating from Roll Number 1 to n and exporting separate pdf file for every student(that were containing real data) and the new code is not iterating all the roll numbers but exporting single pdf file but unfortunately blank sheets.

please

 

[amkkhan]

hi @HaHoBe I did the changes but still getting blank pdf file.
Would you mind sharing the code for once completely.
the problem is your this code

Sub printPDF_mod()
Dim n As Long
For n = 5 To 15
  With Sheets("Sheet1")
    Sheets("Results").Cells(13, "M") = .Cells(n, "A")
    Sheet7.ExportAsFixedFormat xlTypePDF, "C:\result\" & .Cells(n, "A") & "-" & .Cells(n, "C") & ".pdf", , , False, , , False
  End With
Next n
End Sub

was iterating from Roll Number 1 to n and exporting separate pdf file for every student(that were containing real data) and the new code is not iterating all the roll numbers but exporting single pdf file but unfortunately blank sheets.

please

the Cell M13 in my "Results" sheet is the one that is having Roll Numbers. If the Roll number changes , all the page information changes with respect to that roll number.
All I expect from script is that script may scroll from roll number 1 to 50 and keep each iterated page into Queue and once when it is done it may publish all the queued pages that are having data and ignore blank pages and publish all the data containing pages into a single PDF File at the said location with the Name of the File preceded by the word "Result".