Batch exporting of dynamic page to PDF

**Stormbringer** · 05-17-2017, 06:33 PM

I'm looking in ways of making PDFs of a single page of Excel, whose content changes dynamically depending on a variable. I can't imagine I'm the first looking for this, but I couldn't find any information...

More concretely, let's say I have a huge table where I put in details of an order of a client (personal information, what he has ordered, how many, costs, etc.). On a different page I have a nicely formatted invoice, which I want to export to a PDF to send to the client. I just fill in the InvoiceID and the whole page is ready for export.

However, let's say I need to do this weekly for hundreds of invoices. This means that I need to start with InvoiceID_000, export to PDF, change the corresponding cell to InvoiceID_001, export to PDF, change the cell InvoiceID_002, export to PDF, etc. A lot of work. So I'd need something that can loop through all InvoiceIDs and export the page per InvoiceID.

I'd say something like:

for low(InvoiceID) to high(InvoiceID) do
beginexport page to PDF with name "InvoiceID" in location XXX;
end;

Is this doable? Can someone guide me in the right direction? I have no experience with VBA, but I'm sufficiently experienced with mathematical programming and the "normal" Excel functions. Thanks!

**AlphaFrog** · 05-17-2017, 09:06 PM

Try something like this.

Change the red items to suit;

invoice start and end ID numbers
the "corresponding cell" sheet name and cell address
Sheet Name to save as PDF
File path for the PDF file

Sub Save_Invoice_PDFs()
    
    Dim IDStart As Long, IDEnd As Long, ID As Long, strID As String
    
    IDStart = 1 'Invoice start number
    IDEnd = 10  'Invoice end number
    
    For ID = IDStart To IDEnd
    
        'Format ID number
        strID = "InvoiceID_" & Format(ID, "000")
        
        'Write ID number to 'corresponding cell'.
        Sheets("Sheet1").Range("A1").Value = strID
        
        'Save sheet as PDF
        Sheets("Sheet1").ExportAsFixedFormat _
                            Type:=xlTypePDF, _
                            Filename:="C:\MyFolder\" & strID & ".pdf", _
                            Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, _
                            OpenAfterPublish:=False
                            
    Next ID
    
    MsgBox "Invoices saved as PDF.", , "Invoice Save Complete"
    
End Sub

**Stormbringer** · 07-04-2017, 06:48 AM

Sorry for the late response, it took a while for me to test it in Windows. It doesn't work on the Mac, but perfectly on Windows! Thanks for making an entire working script!

Excel on Mac says

Print
Error while printing

and then, in an additional message box

Run-time error '1004':
Application-defined or object-defined error

If there is a suggestion what the problem might be, that would be great and very practical for me.

**AlphaFrog** · 07-04-2017, 07:15 AM

I can't help much with Mac code. Perhaps the example code from this site mite help.

Make and Mail PDF files with VBA code on your Mac

Batch exporting of dynamic page to PDF

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Batch exporting of dynamic page to PDF

Re: Batch exporting of dynamic page to PDF

Re: Batch exporting of dynamic page to PDF

Re: Batch exporting of dynamic page to PDF

Thread Information

Users Browsing this Thread

Similar Threads

Exporting the first page as PDF

Simplify Page 1 refrence Page 2 that updates from a txt via a batch file program

Exporting from web page via VBA

Exporting as text only, in page order

Exporting Charts on to a Web Page

Exporting conditional formatting to a web page

Batch Inserting Row Sheets in between page breaks

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions