Converting ranges of rows into pdf from one workbook

green_world07 · Jan 3, 2022

I want to convert ranges of rows in excel to pdf file with vba.but with specific format in a different sheet. Which I have done so far. But the problem is , some how my code is ignoring the rows with similar values. I want to add all the rows with similar values under the pdf file. for example: the first image shows how the data are in the data set and second image is the format of the output and I want EmpID as input and the related rows with that EmpID will be the output and the rows will be included sequentially in the output format like the third image expected output. below I have added the code i have added in the VBA to have the pdf. The problem i am facing is it generates the output of only one row with the number for example 430 and ignores other 5 of them and it goes for the next unique number and print that because other columns related to that rows contain information which have to be included in the pdf. Thanks a lot in advance. I have tried to solve it with a dropdown list of EmpID but my file is huge(18000 list of EmpID). So i think VBA is the only option to automatize the process.

Sub pdf_test_7()

Dim i As Integer

For i = 2 To 10

Sheets("ps").Cells(1, 2) = Sheets("WD").Cells(i, 1)

Sheets("ps").Range("A1:Q25").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\Desktop\test_code_for_printing_pdf\" & Sheets("WD").Cells(i, 1) & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=True, _
OpenAfterPublish:=False

Next i

End Sub

BSALV · Jan 3, 2022

you need to use a filter somewhere on your data, but i have no idea of the structure of your data ...

green_world07 · Jan 4, 2022

BSALV said:
you need to use a filter somewhere on your data, but i have no idea of the structure of your data ...

hello,
Thanks for your reply. I am attaching the file with some employee info. in the first sheet you will find the info i have and in the second sheet the how the printed pdf should like. i was thinking to have the data from sheet one and then to put them in sheet two and then saving the pdf with the Emp_ID.

EmpID_Workday	personal_number	First_Name	Last_Name	BV	Start_Date	Contract Type	Fulltime/part-time	Working Hours
430	000043	Martin	Schütter	0	01.11.1995	Regular	Full time	39
430	000043	Martin	Schütter	0	01.07.1990	Regular	Full time	39
430	000043	Martin	Schütter	1	01.11.1995	Regular	Full time	39
430	000043	Martin	Schütter	0	01.12.2007	Regular	Full time	39
430	000043	Martin	Schütter	1	01.12.2007	Regular	Full time	39
430	000043	Martin	Schütter	0	01.12.2013	Regular	Full time	39
1750	000175	Thomas	Lorenz	0	04.10.1983	Regular	Part time	28,5
1750	000175	Thomas	Lorenz	0	04.04.2003	Regular	Part time	28,5
1750	000175	Thomas	Lorenz	0	19.03.2003	Regular	Part time	28,5
1750	000175	Thomas	Lorenz	0	31.08.2006	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	01.09.2006	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	30.11.2006	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	01.12.2006	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	28.02.2007	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	01.03.2007	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	01.06.2007	Regular	Part time	32,5
1750	000175	Thomas	Lorenz	0	31.08.2009	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	01.09.2009	Temporary	Part time	1
1750	000175	Thomas	Lorenz	0	01.05.2017	Regular	Part time	32,5
1750	10030611	Thomas	Lorenz	0	01.05.2017	Regular	Part time	32,5
2480	000248	Petra	Klein	0	01.08.1978	Regular	Part time	25
2480	000248	Petra	Klein	0	01.01.2004	Regular	Part time	35
3100	000310	Ute	Dörries	0	01.10.1983	Regular	Full time	39
3100	000310	Ute	Dörries	0	22.04.2009	Regular	Full time	39
3100	000310	Ute	Dörries	0	01.02.2010	Regular	Full time	39
3450	000345	Rosemarie	Fricke	0	01.04.1985	Regular	Part time	23
3450	000345	Rosemarie	Fricke	1	01.03.2007	Regular	Part time	36
3450	000345	Rosemarie	Fricke	0	01.03.2007	Regular	Part time	23
3450	000345	Rosemarie	Fricke	0	01.04.2007	Regular	Part time	36

green_world07 · Jan 4, 2022

green_world07 said:
hello,
Thanks for your reply. I am attaching the file with some employee info. in the first sheet you will find the info i have and in the second sheet the how the printed pdf should like. i was thinking to have the data from sheet one and then to put them in sheet two and then saving the pdf with the Emp_ID. It will do so untill the last row. I have 1400000 rows in main data.

Sheet 1:

green_world07 said:
EmpID_Workday personal_number First_Name Last_Name BV Start_Date Contract Type Fulltime/part-time Working Hours
430
000043 Martin Schütter
0
01.11.1995
Regular Full time
39
430
000043 Martin Schütter
0
01.07.1990
Regular Full time
39
430
000043 Martin Schütter
1
01.11.1995
Regular Full time
39
430
000043 Martin Schütter
0
01.12.2007
Regular Full time
39
430
000043 Martin Schütter
1
01.12.2007
Regular Full time
39
430
000043 Martin Schütter
0
01.12.2013
Regular Full time
39
1750
000175 Thomas Lorenz
0
04.10.1983
Regular Part time
28,5
1750
000175 Thomas Lorenz
0
04.04.2003
Regular Part time
28,5
1750
000175 Thomas Lorenz
0
19.03.2003
Regular Part time
28,5
1750
000175 Thomas Lorenz
0
31.08.2006
Temporary Part time
1
1750
000175 Thomas Lorenz
0
01.09.2006
Temporary Part time
1
1750
000175 Thomas Lorenz
0
30.11.2006
Temporary Part time
1
1750
000175 Thomas Lorenz
0
01.12.2006
Temporary Part time
1
1750
000175 Thomas Lorenz
0
28.02.2007
Temporary Part time
1
1750
000175 Thomas Lorenz
0
01.03.2007
Temporary Part time
1
1750
000175 Thomas Lorenz
0
01.06.2007
Regular Part time
32,5
1750
000175 Thomas Lorenz
0
31.08.2009
Temporary Part time
1
1750
000175 Thomas Lorenz
0
01.09.2009
Temporary Part time
1
1750
000175 Thomas Lorenz
0
01.05.2017
Regular Part time
32,5
1750
10030611 Thomas Lorenz
0
01.05.2017
Regular Part time
32,5
2480
000248 Petra Klein
0
01.08.1978
Regular Part time
25
2480
000248 Petra Klein
0
01.01.2004
Regular Part time
35
3100
000310 Ute Dörries
0
01.10.1983
Regular Full time
39
3100
000310 Ute Dörries
0
22.04.2009
Regular Full time
39
3100
000310 Ute Dörries
0
01.02.2010
Regular Full time
39
3450
000345 Rosemarie Fricke
0
01.04.1985
Regular Part time
23
3450
000345 Rosemarie Fricke
1
01.03.2007
Regular Part time
36
3450
000345 Rosemarie Fricke
0
01.03.2007
Regular Part time
23
3450
000345 Rosemarie Fricke
0
01.04.2007
Regular Part time
36

Sheet 2:

EmpID_Workday	430	First_Name	Martin	Last_Name	Schütter

Tätigkeiten:
Personal_number:	BV	Start_Date	Contract Type	Fulltime/part-time	Working Hours
000043	0	01.11.1995	Regular	Full time	39
000043	0	01.07.1990	Regular	Full time	39
000043	1	01.11.1995	Regular	Full time	39
000043	0	01.12.2007	Regular	Full time	39
000043	1	01.12.2007	Regular	Full time	39
000043	0	01.12.2013	Regular	Full time	39

BSALV · Jan 4, 2022

define the max number of rows to be exported at once in 1st line of MyLoop.
Adjust the right cell addresses, because for me it's a guess.

VBA Code:

Option Compare Text

Public Arr(), shPDF, Rijen, bFlag                                     'the array and pdfsheet that is used in both macros must be public

Sub MyLoop()
     Rijen = 15                                                 'max number of rows to be exported at once, adjust if necessary

     Dim sPreviousID
     Set Source = Sheets("blad3").ListObjects("TBL_data").DataBodyRange     'range of all your +100.000 rows of data (i hope it's a list otherwise small difference  !)
     Set shPDF = Sheets("blad4")                                'name of the sheet where you copy your data

     With Source                                                'in this range
     'CAUTION : all the addresses of the cells(i,j) are relative to the topleftcell of this range !!!!!!
bFlag = False
          For i = 1 To .Rows.Count                              'loop trough all the rows
               If .Cells(i, 2) <> sPreviousID Then              'new personal number, then export and prepare for new records
                    Export_MyPDF                                'export previous employee and redim array
                    sPreviousID = .Cells(i, 2)                  'new personal ID number
                    ptr = 0                                     'reset pointer
                    shPDF.Range("B2").Value = .Cells(i, 1).Value     'new empID_Workday
                    shPDF.Range("D2").Value = .Cells(i, 3).Value     'new first name
                    shPDF.Range("F2").Value = .Cells(i, 4).Value     'new last name
               End If
               If ptr = Rijen Then MsgBox "max number of rows", vbCritical, UCase("Warning")
               ptr = Application.Min(Rijen, ptr + 1)
               Arr(ptr, 1) = .Cells(i, 2)                       '1st column personal number
               Arr(ptr, 2) = .Cells(i, 5)                       'BV
               Arr(ptr, 3) = CDbl(.Cells(i, 6))                 'start date, hopefully no problems with format
               Arr(ptr, 4) = .Cells(i, 7)                       'contract type
               Arr(ptr, 5) = .Cells(i, 8)                       'FT/PT
               Arr(ptr, 6) = .Cells(i, 9)                       'working hours
          Next
          Export_MyPDF                                          'export last employee

     End With
End Sub

Sub Export_MyPDF(Optional b)

     If bFlag Then
          With shPDF
               .Range("A10").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr     'write to collected data to that range, A10 is topleftcell of the data !!!
               .Range("A1:Q25").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Desktop\test_code_for_printing_pdf\" & Sheets("WD").Cells(i, 1) & ".pdf", _
                                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
          End With
     End If
     bFlag = True
     ReDim Arr(1 To Rijen, 1 To 6)                              'suppose there are max 10 rows of data per employee (adjust if necessary)
End Sub

green_world07 · Jan 4, 2022

BSALV said:

define the max number of rows to be exported at once in 1st line of MyLoop.
Adjust the right cell addresses, because for me it's a guess.

VBA Code:

Option Compare Text

Public Arr(), shPDF, Rijen, bFlag                                     'the array and pdfsheet that is used in both macros must be public

Sub MyLoop()
     Rijen = 15                                                 'max number of rows to be exported at once, adjust if necessary

     Dim sPreviousID
     Set Source = Sheets("blad3").ListObjects("TBL_data").DataBodyRange     'range of all your +100.000 rows of data (i hope it's a list otherwise small difference  !)
     Set shPDF = Sheets("blad4")                                'name of the sheet where you copy your data

     With Source                                                'in this range
     'CAUTION : all the addresses of the cells(i,j) are relative to the topleftcell of this range !!!!!!
bFlag = False
          For i = 1 To .Rows.Count                              'loop trough all the rows
               If .Cells(i, 2) <> sPreviousID Then              'new personal number, then export and prepare for new records
                    Export_MyPDF                                'export previous employee and redim array
                    sPreviousID = .Cells(i, 2)                  'new personal ID number
                    ptr = 0                                     'reset pointer
                    shPDF.Range("B2").Value = .Cells(i, 1).Value     'new empID_Workday
                    shPDF.Range("D2").Value = .Cells(i, 3).Value     'new first name
                    shPDF.Range("F2").Value = .Cells(i, 4).Value     'new last name
               End If
               If ptr = Rijen Then MsgBox "max number of rows", vbCritical, UCase("Warning")
               ptr = Application.Min(Rijen, ptr + 1)
               Arr(ptr, 1) = .Cells(i, 2)                       '1st column personal number
               Arr(ptr, 2) = .Cells(i, 5)                       'BV
               Arr(ptr, 3) = CDbl(.Cells(i, 6))                 'start date, hopefully no problems with format
               Arr(ptr, 4) = .Cells(i, 7)                       'contract type
               Arr(ptr, 5) = .Cells(i, 8)                       'FT/PT
               Arr(ptr, 6) = .Cells(i, 9)                       'working hours
          Next
          Export_MyPDF                                          'export last employee

     End With
End Sub

Sub Export_MyPDF(Optional b)

     If bFlag Then
          With shPDF
               .Range("A10").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr     'write to collected data to that range, A10 is topleftcell of the data !!!
               .Range("A1:Q25").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Desktop\test_code_for_printing_pdf\" & Sheets("WD").Cells(i, 1) & ".pdf", _
                                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
          End With
     End If
     bFlag = True
     ReDim Arr(1 To Rijen, 1 To 6)                              'suppose there are max 10 rows of data per employee (adjust if necessary)
End Sub

what do you mean by "Set Source = Sheets("blad3").ListObjects("TBL_data").DataBodyRange " this line? The TBL_data...what should be here? the range of the total number of column I have?

green_world07 · Jan 4, 2022

green_world07 said:
what do you mean by "Set Source = Sheets("blad3").ListObjects("TBL_data").DataBodyRange " this line? The TBL_data...what should be here? the range of the total number of rows and column I have? Also in your comment you have said that if it is not a list there should be little change. What do you mean by that? Thanks

BSALV · Jan 4, 2022

i made a small worksheet myself with your data and in the "set Source=sheets("blad3").listobjects("TBL_Data").databodyrange rename "blad3" and "Tbl_Data" with your names.
As it's a table, excel knows the number of rows and columns, you don't have to do anything.

The same for Set shPDF = Sheets("blad4"), rename "Blad4"

If you don't use a table (=listobject), you can use a defined name that refers to the range of your data instead.
then this changes

VBA Code:

before
  Set Source = Sheets("blad3").ListObjects("TBL_data").DataBodyRange     'as listobject 
2 options
  Set Source = Range("Name of that defined range")     'as defined range
  Set Source = Sheets("blad3").range("A2:Z150000")     'straight

green_world07 · Jan 4, 2022

BSALV said:
i made a small worksheet myself with your data and in the "set Source=sheets("blad3").listobjects("TBL_Data").databodyrange rename "blad3" and "Tbl_Data" with your names.
As it's a table, excel knows the number of rows and columns, you don't have to do anything.

The same for Set shPDF = Sheets("blad4"), rename "Blad4"

If you don't use a table (=listobject), you can use a defined name that refers to the range of your data instead.
then this changes

VBA Code:

before Set Source = Sheets("blad3").ListObjects("TBL_data").DataBodyRange 'as listobject 2 options Set Source = Range("Name of that defined range") 'as defined range Set Source = Sheets("blad3").range("A2:Z150000") 'straight

it works but the code gives an error in the second portion:

as you can see I have changed the range from "A1:Q25" to "A2:I49"(I think this is the range of my main data set , right?). I am getting run time error 9 "Script out of range." I am pretty new to VBA so my question my sound stupid. I am sorry for that.

BSALV · Jan 4, 2022

there is an error in assigning the pdf-name and cell(i,1) is the reason.
i is used in the other macro, but is unknown here.
Add i in the 2nd row of the module like this

VBA Code:

Public Arr(), shPDF, Rijen, bFlag, i

But you have to add an offset in Sheets("WD").Cells(i, 1) & ".pdf".
There is a headerrow above so i think you must do ....Cells(i+1,1), the trial and error method, try +1, +2, ...

VBA Code:

  .Range("A1:Q25").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Desktop\test_code_for_printing_pdf\" & Sheets("WD").Cells(i+1, 1) & ".pdf", _

Converting ranges of rows into pdf from one workbook

green_world07

New Member

Excel Facts

BSALV

Banned user

green_world07

New Member

green_world07

New Member

BSALV

Banned user

green_world07

New Member

green_world07

New Member

BSALV

Banned user

green_world07

New Member

BSALV

Banned user

Similar threads

Forum statistics

Share this page

Converting ranges of rows into pdf from one workbook

New Member

Excel Facts

Banned user

New Member

New Member

Banned user

New Member

New Member

Banned user

New Member

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock