How to export excel file to pdf

[icytuvi]

Hi,

I need to send the notice on new price list in .pdf format to various customers, and I really appreciate if you could help advise on how to do it at one go without having to export file for each and every customer.

I have the database in two sheets as below:

Sheet 1: the price list by region

Example.xlsx
	A	B	C	D	E
2	PRICE LIST BY REGION
3	Brand	SKU	Selangor	Sarawak	Mallaka
4	Samsung	Galaxy Note 10	$ 800	$ 820	$ 830
5	Samsung	Galaxy Note 10 Lite	$ 700	$ 720	$ 730
6	Samsung	Galaxy Ultra Note 20	$ 1,000	$ 1,020	$ 1,030
7	Apple	Iphone 13	$ 1,200	$ 1,220	$ 1,230
8	Apple	Iphone 12 Pro Max	$ 1,100	$ 1,120	$ 1,130
9	Apple	Iphone 12	$ 1,000	$ 1,020	$ 1,030
10	Apple	Iphone 11	$ 800	$ 820	$ 830
11	Oppo	Reno 5	$ 600	$ 620	$ 630
12	Xiaomi	Redmi Note 5	$ 300	$ 320	$ 330
Price list

Cell Formulas
Range	Formula
D4:D12	D4	=C4+20
E4:E12	E4	=C4+30

Sheet 2: Customer list by region

Example.xlsx
	A	B	C	D
3	#	Code	Customer	Region
4	1	SE0001	Marina	Selangor
5	2	SA0001	Tracy	Sarawak
6	3	MA0001	Wil C.	Mallaka
7	4	SE0002	Areen	Selangor
8	5	SA0002	Chris	Sarawak
9	6	MA0002	Mark	Mallaka
10	7	SE0003	Taylor	Selangor
11	8	SA0003	Stephen	Sarawak
12	9	MA0003	Chloe	Mallaka
13	10	SE0004	Yoko	Selangor
14	11	SA0004	Leonora	Sarawak
15	12	MA0004	Phil	Mallaka
16	13	SE0005	Lana	Selangor
17	14	SA0005	Sam	Sarawak
18	15	MA0005	Theodora	Mallaka
Customer list

And I would like to export the notification to all customer based on this template:

Example.xlsx
	A	B	C	D	E	F	G	H	I
3	Dear Mr./Ms:	Taylor	7
4	Location:	Selangor
5
6	Kindly be informed that from effective 18th Oct 2021, the follow price list will be applied at your region:
7
8	Brand	SKU	Price
9	Samsung	Galaxy Note 10	$ 800
10	Samsung	Galaxy Note 10 Lite	$ 700
11	Samsung	Galaxy Ultra Note 20	$ 1,000
12	Apple	Iphone 13	$ 1,200
13	Apple	Iphone 12 Pro Max	$ 1,100
14	Apple	Iphone 12	$ 1,000
15	Apple	Iphone 11	$ 800
16	Oppo	Reno 5	$ 600
17	Xiaomi	Redmi Note 5	$ 300
18
19	Regards
Notice

Cell Formulas
Range	Formula
B3	B3	=VLOOKUP(C3,'Customer list'!$A$4:$C$18,3,0)
B4	B4	=VLOOKUP(B3,'Customer list'!$C$4:$D$18,2,0)
C9:C17	C9	=SUMPRODUCT(('Price list'!$C$3:$E$3=Notice!B$4)*('Price list'!$B$4:$B$12=Notice!B9)*('Price list'!$C$4:$E$12))

Thank you very much.

 

[icytuvi]

This is similar to the function mail merge in MS Word, but I don't know how to do it in Excel. Any help would be greatly appreciated.

 

[John_w]

Try this macro:

Public Sub Create_PDFs()

    Dim r As Long
    Dim PDFfile As String
       
    With Worksheets("Customer list")
        For r = 4 To .Cells(.Rows.Count, "A").End(xlUp).Row
            PDFfile = ActiveWorkbook.Path & "\" & Join(Array(.Cells(r, "A").Value, .Cells(r, "B").Value, .Cells(r, "C").Value, .Cells(r, "D").Value)) & ".pdf"
            Worksheets("Notice").Range("C3").Value = .Cells(r, "A").Value
            Worksheets("Notice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, Quality:=xlQualityStandard
        Next
    End With
    
    MsgBox "Done"
    
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to export pdf from excel file
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[icytuvi]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to export pdf from excel file
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hi Moderator, thank you for reminding and I'm really sorry for this. Will not repeat next time

 

[icytuvi]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to export pdf from excel file
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Try this macro:

Public Sub Create_PDFs()

    Dim r As Long
    Dim PDFfile As String
      
    With Worksheets("Customer list")
        For r = 4 To .Cells(.Rows.Count, "A").End(xlUp).Row
            PDFfile = ActiveWorkbook.Path & "\" & Join(Array(.Cells(r, "A").Value, .Cells(r, "B").Value, .Cells(r, "C").Value, .Cells(r, "D").Value)) & ".pdf"
            Worksheets("Notice").Range("C3").Value = .Cells(r, "A").Value
            Worksheets("Notice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, Quality:=xlQualityStandard
        Next
    End With
   
    MsgBox "Done"
   
End Sub

Hi @John_w, thank you very much. Your code for exporting to multiple pdf files with file name set is brilliant!!! Could you please help incorporate the lookup function as well? Since in my excel file the data for each customer may be different from the others. I have been stuck with it for quite a long time but could not figure out how to do it

Appreciate if any one could help. Thanks a lot.

 

[John_w]

Could you please help incorporate the lookup function as well? Since in my excel file the data for each customer may be different from the others.

You need to explain more. Which lookup function? Do you want the code to create the lookup formula (which one?), instead of you putting it in a cell in the "Notice" sheet? Which cell? Please describe what you want in more detail, and be specific.

 

[icytuvi]

You need to explain more. Which lookup function? Do you want the code to create the lookup formula (which one?), instead of you putting it in a cell in the "Notice" sheet? Which cell? Please describe what you want in more detail, and be specific

Thank you for taking your time to help look at my matter. To understand more, could you please go and take a look at the excel file in the link below?

How to export pdf from excel file

Hi, I have a file as attached, in which I have to send notice to each customer in the customer list to inform them of the new price to be applied for their area. Could you please help take a look and advise me if there's any way to export the excel file to pdf to all customers in the list...

www.excelforum.com

In short, let's say I have 100 customers in 20 regions, and to each region I may apply a price list different to other regions. I have the database containing all the customer name, region, and the corresponding price list. I want to send the price announcement to all of them in pdf format. The question is, how to do that without having to create each and every excel sheet for each customer, then export it manually to pdf. Your code above solve the matter of exporting to many pdf files at one go. It's will be perfect if the price totally match with the customer.

 

[Fluff]

Please do not ask members to look at other sites for data. Apart from anything else only members on that site will be able to access it.

If you want to share a file, you need to upload it to a share site, mark for sharing & then post the link you get to the thread.
Thanks