Generate from Excel to PDF

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Generate from Excel to PDF

Post by JERRY89 »

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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate from Excel to PDF

Post by HansV »

See the attached version.

GENERATE BARCODE STICKER PDF.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate from Excel to PDF

Post by HansV »

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
Best wishes,
Hans

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate from Excel to PDF

Post by HansV »

If you want a single file, we'll have to generate all stickers before exporting to PDF. Here is the corrected version.

GENERATE BARCODE STICKER PDF.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

Hi Hans,

Thanks a lot. It is super great fast to speed up my task. :thankyou:

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

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

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate from Excel to PDF

Post by HansV »

Where do you want the duplicate?
Best wishes,
Hans

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

Hi Hans,

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.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate from Excel to PDF

Post by HansV »

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

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

Hi Hans,

Thanks a lot for your help.Your are really a very great programmer in Microsoft. :thankyou: :cheers:

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

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?? :scratch:

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Generate from Excel to PDF

Post by HansV »

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

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Generate from Excel to PDF

Post by JERRY89 »

Hi Hans,

Yes this is exactly what i want, thanks alot Hans :cheers: :thankyou: