VBA batch print to PDF

[okvals48]

Hi,

i need help with this VBA code, i would like to print PDF files as one document and not separated. I did try to use CHAT GPT but i always receive error that active x component can not create object.
I have two sheet's one is for data and other one is template from where i would like to print PDF document. As i work in accounting i need this PDF as attachment to invoice we have all sort of data on it.

So idea was that VBA automatically changes invoice ID in field A3, after i give the range that i want to be printed out and then saves all the attachments as one PDF.
Cell A3 changes data in template supplier name, account, date etc.

I tried also printing them directly to printer but i receive the same error.

Thanks in advance for help

Sub Print_to_PDF()
    Dim startInvoice As Long
    Dim endInvoice As Long
    Dim wsSource As Worksheet
    Dim printRange As Range
    Dim invoiceID As Long
    
    ' Prompt the user to input the start of the range
    startInvoice = InputBox("Enter the starting invoice ID:")
    
    ' Prompt the user to input the end of the range
    endInvoice = InputBox("Enter the ending invoice ID:")
    
    ' Link the start of the range to cell A3 on Sheet2
    Set wsSource = ThisWorkbook.Sheets("Sheet2")
    
    ' Loop through each invoice ID within the specified range
    For invoiceID = startInvoice To endInvoice
        ' Set the invoice ID
        wsSource.Range("A3").Value = invoiceID
        
        ' Define the print range with updated invoice data
        Set printRange = wsSource.Range("C3:J20") ' Adjust this range as needed
        
        ' Print the current invoice as a PDF document
        printRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\slavk\Desktop\Printed_Invoice_" & invoiceID & ".pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next invoiceID
End Sub

Template for printing

Test.xlsm
	A	B	C	D	E	F	G	H	I	J
3	5		INVOICE NUMBER	G1
4			SUPPLIER	G
5			ID	5
6			DATE I	30.4.2024
7			INVOICE DATE	20.4.2024
8			MATURITY DATE	5.5.2024
9
10			AMOUNT	VAT	VAT KEY	VIN	COST CENTER	REMARK	ACCOUNT	INVOICE TYP
11			AMOUNT	VAT	VAT KEY	VIN	COST CENTER	REMARK	ACCOUNT	INVOICE TYP
12			100	25	5U	_XXXXXX	11204	5	440001	1200
13			100	25	5R	_XXXXXX	11204	5	440001	1200
Sheet2

Cell Formulas
Range	Formula
D3	D3	=+XLOOKUP(A3,Sheet1!D:D,Sheet1!F:F)
D4:D8	D4	=+XLOOKUP($D$3,Sheet1!$F$4:$F$11,XLOOKUP(Sheet2!$C4,Sheet1!$D$3:$R$3,Sheet1!$D$4:$R$11))
C10:J10	C10	=+CHOOSECOLS(Sheet1!$D$3:$R$3,XMATCH(C11,Sheet1!$D$3:$R$3))
C12:J13	C12	=+CHOOSECOLS(FILTER(Sheet1!$D$4:$R$11,Sheet1!$F$4:$F$11=Sheet2!$D$3,""),XMATCH(C11,Sheet1!$D$3:$R$3))
Dynamic array formulas.

Data tabel

Test TCZ.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
3	ID	SUPPLIER	INVOICE NUMBER	POSITION	DATE I	INVOICE DATE	MATURITY DATE	AMOUNT	VAT	VAT KEY	VIN	COST CENTER	REMARK	ACCOUNT	INVOICE TYP
4	1	A	A1	1	30.4.2024	15.4.2024	30.4.2024	100,00	25	5U	_XXXXXX	11204	1	440001	1200
5	2	B	B1	1	30.4.2024	16.4.2024	1.5.2024	100,00	25	5U	_XXXXXX	11204	2	440001	1200
6	3	C	C1	1	30.4.2024	17.4.2024	2.5.2024	100,00	25	5U	_XXXXXX	11204	3	440001	1200
7	4	D	D1	1	30.4.2024	18.4.2024	3.5.2024	100,00	25	5U	_XXXXXX	11204	4	440001	1200
8	5	G	G1	1	30.4.2024	20.4.2024	5.5.2024	100,00	25	5U	_XXXXXX	11204	5	440001	1200
9	6	G	G1	2	30.4.2024	20.4.2024	5.5.2024	100,00	25	5R	_XXXXXX	11204	5	440001	1200
10	7	C	C2	1	30.4.2024	17.4.2024	45414	100	25	5U	_XXXXXX	11204	6	440002	1200
11	8	D	D2	1	30.4.2024	18.4.2024	45415	100	25	5U	_XXXXXX	11204	7	440001	1200
Sheet1

Cell Formulas
Range	Formula
J4:J9	J4	=+I4+15
L4:L9	L4	=+K4*1.25-K4

 

[John_w]

The code here should work for you with changes to the sheet names and cell references:

VB Script to Save Results to a single PDF Fila

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

www.mrexcel.com

 

[okvals48]

The code here should work for you with changes to the sheet names and cell references:

VB Script to Save Results to a single PDF Fila

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

www.mrexcel.com

Thx