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

 

[amkkhan]

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

@John_w . It is exporting pdf file on the same path where Excel file is located.
Can we save it on "C:\result\" and name of the pdf file should be same that is the name of Excel file. Preceded by word "Result"
For example, name of Excel file is "4th-B" then the output pdf file should be saved in "C:\result\" location and the name of file should be "Result-4th-B"

 

[HaHoBe]

Hi amkkhan,

instead of

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\All Results.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

use

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\result\Result-" & ThisWorkbook.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Holger

 

[John_w]

Can we save it on "C:\result\" and name of the pdf file should be same that is the name of Excel file. Preceded by word "Result"

Change Filename:=ThisWorkbook.Path & "\All Results.pdf" to Filename:="C:\Result\Result " & Replace(ThisWorkbook.Name, ".xlsm", ".pdf", Compare:=vbTextCompare) and the MsgBox line similarly.

 

[amkkhan]

Hi amkkhan,

instead of

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\All Results.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

use

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\result\Result-" & ThisWorkbook.Name & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Holger

Done and working. Thanks a lot

 

[amkkhan]

Change Filename:=ThisWorkbook.Path & "\All Results.pdf" to Filename:="C:\Result\Result " & Replace(ThisWorkbook.Name, ".xlsm", ".pdf", Compare:=vbTextCompare) and the MsgBox line similarly.

Done and working. Thanks a lot