Generate from Excel to PDF
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Generate from Excel to PDF
Dear All,
Is there any way to generate sticker from sheet (Barcode Sticker) in the excel file to PDF base on the data in the Master Data.I need to generate a duplicate copy for each of the sticker to be in PDF. Example, sticker A in page 1 , Duplicate in page 2 & sticker B page 3 , Duplicate in page 4 and etc until end of the data.
Is there any way to generate sticker from sheet (Barcode Sticker) in the excel file to PDF base on the data in the Master Data.I need to generate a duplicate copy for each of the sticker to be in PDF. Example, sticker A in page 1 , Duplicate in page 2 & sticker B page 3 , Duplicate in page 4 and etc until end of the data.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate from Excel to PDF
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Hi Hans,
I notice the Batch No won't change while running the code & Material Group. In between can it directly generate out as PDF. Usually i have more than 400 Batch No of sticker which will generate around 800 sticker and i afraid this file become very bulky so i guess direct generate as PDF will be better.Example 800 sticker in one PDF (800 pages).
I notice the Batch No won't change while running the code & Material Group. In between can it directly generate out as PDF. Usually i have more than 400 Batch No of sticker which will generate around 800 sticker and i afraid this file become very bulky so i guess direct generate as PDF will be better.Example 800 sticker in one PDF (800 pages).
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate from Excel to PDF
I made a mistake, I can correct that.
But what do you want?
1) a single PDF file with all stickers
2) a PDF file for each batch number (with 2 stickers per file)
3) a separate PDF for each sticker
But what do you want?
1) a single PDF file with all stickers
2) a PDF file for each batch number (with 2 stickers per file)
3) a separate PDF for each sticker
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Hi Hans,
You are right. I wan a single PDF file with all the sticker, a pdf file for each batch number with 2 sticker. Can combine all sticker in one PDF with different page.Example Sticker for BAtch No YTD8888 in PDF Pg 1 & Copy in Pg 2, Batch No YTD88996 in Pg 3 & Copy in Pg 4 and so on.
You are right. I wan a single PDF file with all the sticker, a pdf file for each batch number with 2 sticker. Can combine all sticker in one PDF with different page.Example Sticker for BAtch No YTD8888 in PDF Pg 1 & Copy in Pg 2, Batch No YTD88996 in Pg 3 & Copy in Pg 4 and so on.
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate from Excel to PDF
If you want a single file, we'll have to generate all stickers before exporting to PDF. Here is the corrected version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Hi Hans,
Thanks a lot. It is super great fast to speed up my task.
Thanks a lot. It is super great fast to speed up my task.
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Dear Hans,
May i ask which line code i shall amend if i don't want to create a duplicate of the sticker in this vba
May i ask which line code i shall amend if i don't want to create a duplicate of the sticker in this vba
Code: Select all
Option Explicit
Sub GenerateStickers()
Dim w1 As Worksheet
Dim w2 As Worksheet
Dim r As Long
Dim m As Long
Dim s As Long
Dim i As Long
Set w1 = Worksheets("Master Data")
Set w2 = Worksheets("Barcode Sticker")
w2.Range("12:" & w2.Rows.Count).Clear
w2.PageSetup.PrintArea = "B2:C11"
m = w1.Range("A" & w1.Rows.Count).End(xlUp).Row
For r = 2 To m
s = 20 * r - 38
For i = 1 To 2
w2.Range("B2:D11").Copy Destination:=w2.Range("B" & s + 10 * (i - 1))
w2.Range("C" & s + 6 + 10 * (i - 1)).Value = w1.Range("D" & r).Value
w2.Range("B" & s + 8).Resize(2, 2).Merge Across:=True
Next i
Next r
m = s + 19
For s = 12 To m Step 10
w2.HPageBreaks.Add Before:=w2.Range("A" & s)
Next s
w2.PageSetup.PrintArea = "B2:C" & m
w2.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Barcode.pdf"
End Sub
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Hi Hans,
Now this barcode will generate additional one sticker for each barcode so i want to have one Barcode only for each item.
Now this barcode will generate additional one sticker for each barcode so i want to have one Barcode only for each item.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate from Excel to PDF
My apologies, I misread your question. Here is the revised code.
Code: Select all
Sub GenerateStickers()
Dim w1 As Worksheet
Dim w2 As Worksheet
Dim r As Long
Dim m As Long
Dim s As Long
Set w1 = Worksheets("Master Data")
Set w2 = Worksheets("Barcode Sticker")
w2.Range("12:" & w2.Rows.Count).Clear
w2.PageSetup.PrintArea = "B2:C11"
m = w1.Range("A" & w1.Rows.Count).End(xlUp).Row
For r = 2 To m
s = 10 * r - 18
w2.Range("B2:D11").Copy Destination:=w2.Range("B" & s)
w2.Range("C" & s + 6).Value = w1.Range("D" & r).Value
w2.Range("B" & s + 8).Resize(2, 2).Merge Across:=True
Next r
m = s + 9
For s = 12 To m Step 10
w2.HPageBreaks.Add Before:=w2.Range("A" & s)
Next s
w2.PageSetup.PrintArea = "B2:C" & m
w2.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Barcode.pdf"
End Sub
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Hi Hans,
Thanks a lot for your help.Your are really a very great programmer in Microsoft.
Thanks a lot for your help.Your are really a very great programmer in Microsoft.
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Hi Hans,
I need to seek for your help, is it possible to having a flexibility option where i can generate copy of the sticker, for example ,input 0 it will not generate a duplicate copy of the sticker , if input 5 it will generate 5 duplicate copy of sticker. Is it possible??
I need to seek for your help, is it possible to having a flexibility option where i can generate copy of the sticker, for example ,input 0 it will not generate a duplicate copy of the sticker , if input 5 it will generate 5 duplicate copy of sticker. Is it possible??
-
- Administrator
- Posts: 78437
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Generate from Excel to PDF
The following macro lets you specify how many copies of each sticker should be generated. If you don't want duplicates, enter 1.
Code: Select all
Sub GenerateStickers()
Dim w1 As Worksheet
Dim w2 As Worksheet
Dim r As Long
Dim m As Long
Dim s As Long
Dim i As Long
Dim n As Long
n = Val(InputBox(Prompt:="How many copies of each sticker do you want?", Default:=1))
If n < 0 Then
Beep
Exit Sub
End If
Set w1 = Worksheets("Master Data")
Set w2 = Worksheets("Barcode Sticker")
w2.Range("12:" & w2.Rows.Count).Clear
w2.PageSetup.PrintArea = "B2:C11"
m = w1.Range("A" & w1.Rows.Count).End(xlUp).Row
s = 2
For r = 2 To m
For i = 1 To n
w2.Range("B2:D11").Copy Destination:=w2.Range("B" & s)
w2.Range("C" & s + 6).Value = w1.Range("D" & r).Value
w2.Range("B" & s + 8).Resize(2, 2).Merge Across:=True
s = s + 10
Next i
Next r
m = s - 1
For s = 12 To m Step 10
w2.HPageBreaks.Add Before:=w2.Range("A" & s)
Next s
w2.PageSetup.PrintArea = "B2:C" & m
w2.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Barcode.pdf"
End Sub
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 516
- Joined: 21 Feb 2016, 02:52
Re: Generate from Excel to PDF
Hi Hans,
Yes this is exactly what i want, thanks alot Hans
Yes this is exactly what i want, thanks alot Hans