Excel VBA - Export the same range changed by a function to a PDF file

supertitovic

New Member
Joined
Oct 17, 2017
Messages
5
I have tried to find the solution everywhere, but I only find functions to export several sheets in a single PDF.
I want to export the same sheet and the same range, which is changing through a function, in a single PDF file.

For example, in the following code there is a loop that changes the values of a range 3 times.
How can I do to save a single PDF file that contains the range in triplicate and changed?

I have tried using Arrays but I don't know how to implement it in this specific case.

Code:
Dim i As Long
For i = 1 To 3
    Call function_to_change_values 'this function change the values of range A1 to D5
    'config printing:
    ActiveSheet.PageSetup.PrintArea = "$A$1:$D$5" 'print area
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = 77
        .Draft = False
        .PaperSize = xlPaperA4
    End With
    'create PDF:
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\myfile.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
Next i
Thank you very much for the help
 

felipemcandido

New Member
Joined
Oct 31, 2019
Messages
4
I don't understand exactly what you want.
So, let's try to help:

1. If you want to create one PDF showing the three transformation, then you need to create 3 ranges (for example, copying A1:D5 to A7:D11 and A13:D17, skip one line to see better the values) and export those after the loop. Your Export is inside the loop.
2. If you want to create more than 1 file with each result, then you need export them changing the filename (for example: FileName := "C:\MyFile" & i & ".pdf").

I hope it will help you.
 

supertitovic

New Member
Joined
Oct 17, 2017
Messages
5
Thank you, but the answer doesn't help me.
What I intend is for a certain range to change its values, print it, change its values again, print it again ... And all that in a single PDF file.
I do not know if I explain myself, since I have found solutions to print different ranges and sheets in a single PDF file, but I have not found a solution for what I specify.
 

felipemcandido

New Member
Joined
Oct 31, 2019
Messages
4
If understand correctly, my answer 1 is almost what you want. After exporting to PDF, clear the another regions, keeping just the first one (original or changed).
If it isn't what you want, paste your expected (or example) result here, so it will be easier to help you.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,954
Try this macro, which adds a temporary sheet and copies A1:D5 to the next row, three times. It also puts a page break between each block of cells. Just change the PDFFullName string value to the folder and file name of PDF file you want to create.

Code:
Public Sub Create_PDF()
     
    Dim dataCells As Range
    Dim PDFsheet As Worksheet
    Dim PDFdestCell As Range, lastRow As Long
    Dim PDFFullName As String
    Dim i As Long
    
    With ThisWorkbook
        Set dataCells = .ActiveSheet.Range("A1:D5")
        PDFFullName = "C:\path\to\Data.pdf"     'CHANGE THIS
        'Add temporary sheet for PDF data
        Set PDFsheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
    End With
    
    Set PDFdestCell = PDFsheet.Range("A1")
    
    For i = 1 To 3
    
        Call function_to_change_values 'this function change the values of range A1 to D5
        
        'Copy data cells to temporary sheet
        
        dataCells.Copy PDFdestCell
        
        'Set next destination cell and insert page break in temporary sheet
        
        With PDFsheet
            Set PDFdestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            .HPageBreaks.Add Before:=PDFdestCell
        End With
        
    Next
        
    'Set page settings on temporary sheet
    
    Application.PrintCommunication = False
    With PDFsheet.PageSetup
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .Zoom = 100
    End With
    Application.PrintCommunication = True
    
    'Create PDF
    
    PDFsheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFullName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
     
    'Delete temporary sheet
    
    Application.DisplayAlerts = False
    PDFsheet.Delete
    Application.DisplayAlerts = True
    
End Sub
 

supertitovic

New Member
Joined
Oct 17, 2017
Messages
5
Thanks for the answers, but it is not what I am looking for, since the sheet has formats, colors and modifications which have to appear on the printed page.
I already know that you can print different sheets in a single PDF file, as well as do the latter (copy and paste in another new sheet), but, my question is if what I want to do, can it be done through Arrays? Without having to create new sheets.
Thank you and I hope to explain myself well, I use a translator. :)
 

supertitovic

New Member
Joined
Oct 17, 2017
Messages
5
Explaining it in a simpler way:
Imagine a loop of 1 to 3.
In step 1 a random number is generated in cell A1 and a PDF file of that cell is saved.
In step 2 another random number is generated in cell A1 and another PDF file is saved again.
In step 3 exactly the same.
Can you save in a single PDF instead of saving 3 PDF files? Without having to copy on a different sheet.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,954
Thanks for the answers, but it is not what I am looking for, since the sheet has formats, colors and modifications which have to appear on the printed page.
I already know that you can print different sheets in a single PDF file, as well as do the latter (copy and paste in another new sheet), but, my question is if what I want to do, can it be done through Arrays? Without having to create new sheets.
Thank you and I hope to explain myself well, I use a translator. :)
My code copies the cell values and formats for the specified range to the temporary sheet, so the temporary sheet and therefore the PDF should look exactly like the range on the original source sheet.

No, I don't think it can be done with arrays, since arrays can only store values. How would the array reproduce the visual cell formats? These can only be represented on a sheet.

Explaining it in a simpler way:
Imagine a loop of 1 to 3.
In step 1 a random number is generated in cell A1 and a PDF file of that cell is saved.
In step 2 another random number is generated in cell A1 and another PDF file is saved again.
In step 3 exactly the same.
Can you save in a single PDF instead of saving 3 PDF files? Without having to copy on a different sheet.
You don't have to copy A1 to a different sheet. You could copy A1 3 times (temporarily) to different cells on the same sheet, away from your existing data, e.g. A1001:A1003, and save those as a single PDF. So yes, you could save in a single PDF instead of 3 PDFs.

With your loop above creating 3 PDFs, you could merge (join) these files into a single PDF using an API, for example the Acrobat API. However the Acrobat API is only available to VBA if you install Acrobat Pro, not the free Adobe Reader. There is example code which uses the Acrobat API to merge multiple PDFs at https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1100300-combine-two-pdf-into-one-separate-pdf-save-particular-path-using-vba-post5290362.html#post5290362.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,887
Office Version
2007
Platform
Windows
You can reproduce your cell format "$A$1:$D$5" in "$A$6:$D$10" and in "$A$11:$D$15"


Then your code: function_to_change_values, you must update the 3 ranges.


So:
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$15"
 

Forum statistics

Threads
1,078,347
Messages
5,339,681
Members
399,319
Latest member
JOSEILLO

Some videos you may like

This Week's Hot Topics

Top