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

 

[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".

 

[amkkhan]

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".

I want to get Excel VBA Code to save each iteration of List of 50 students into a single PDF File, ignoring blank sheets.
Where cell M13 is playing most important role. because if one changes the roll No, the entire page changes its information with respect to that roll number of cell M13. you may better see it from attached images.