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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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/gener...r-path-using-vba-post5290362.html#post5290362.
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top