• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating Back to Back PDF based on Row Data in Excel using VBA (Loop)

amitsaha85

New Member
Hello,


I am trying to write a code which will generate payslips for the employees in PDF format.

I have a excel work book with 2 sheets, 1 is payroll data (details of employees with salary data) & the another is Payslip (Format)in which I want to generate the payslips.


I need to generate all Payslips based on data available in payroll sheet & save them as employee name in a specified folder in C drive with one click.


Please help me out to solve this as I am a beginner in VBA & the help will definitely be appreciated.


PFB the link of the file till I have made.


http://sdrv.ms/QU2hqF


Thanks & regards,
 
Hi Amit,


Kindly go through the file. It should be ready to use


I have input comments in the code.


https://skydrive.live.com/redir?resid=3E1509A4CCE38540!123&authkey=!AMnvq2WBGFkVD8I


kindly let me know if it works at your end.


Anil M
 
Hi Anil,


Firstly I would like to thank you for your help & spending your valuable time to solving my problem.


The file is working fine in first time.


But whenever I was trying to generating for the second time it was not working even after deletion the entire folder "Feb'12".


The generated payslips are not in single page.


Lastly one more request please make list of the payslips along with the "Emp Name","Emp Code" & the payslip link in next sheet.


For sample file of the PDF payslip & Payslip list please refer to the below link


http://sdrv.ms/RMYcYi


Lastly I would like to convey my gratitude for all your support & kind help.


Regards,


Amit Saha
 
Hi Amit,


Thanks for your encouragement... I will check into this and revert to you by evening today.


For -- whenever I was trying to generating for the second time it was not working even after deletion the entire folder "Feb'12". -- in the Payroll sheet i have added a helper column in column AP which gets filled with "Done" after the particular payslip is generated. Before trying it for 2nd time, we need to delete it. Once deleted, we can try again...


Regarding the 2 other points, I will revert by evening today...


Have a great day..
 
Hi Anil,


Firstly would like to wish you & your family a very happy dushera. After the durga puja vacation for 4 days I am back in office.


Regarding your last post I have checked the link is available now.


Please check & revert.


http://sdrv.ms/RMYcYi


Regards,


Amit
 
Hi Amit,


Thanks for the wishes... and same to you..


I have attached the reworked file... This now lists the emp code, emp name & the file path also..


However, i am still working on the formatting of the pdf file to get the payslip in a single page format.. I will post the file once i am able to crack it..


https://skydrive.live.com/redir?resid=3E1509A4CCE38540!126&authkey=!AA8IFghXAFTj5gg


Hi All ninjas... need your help please...
 
Hi, majay1973!


Tried using this?

Workbook.ExportAsFixedFormat Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr


Where...

Type: xlTypePDF or xlTypeXPS

Quality: xlQualityStandard or xlQualityMinimum

IncludeDocProperties: True/False

IgnorePrintAreas: True/False

From: page number

To: page number

OpenAfterPublish: True/False

FixedFormatExtClassPtr: not used


Regards!


EDIT: maybe you find this useful too:

http://chandoo.org/forums/topic/how-to-compile-the-excel-file#post-50055
 
Hi SirJB7,


Thanks for the response.


In the above code, you mention about "From & to" to pick up the pages for printing.


However the situation here is when the excel sheet gets saved as PDF, the print area gets broken into multiple pages while it should come ideally as a single page. i.e. all the contents in the print area selected should come on 1 page.


Let me reword the problem statement.


Out friend has an excel file with 3 sheets:

Payroll

Payslip

List


Payroll sheet has a table containing the information like emp name, emp code, salary components etc.

Payslip sheet is formatted to look like a payslip. He had already used vlookup to get the details in the payslip from payroll sheet.

List sheet is where our friend wants the list of emp name, emp code and the file paths of the pdfs of the payslips.


In my first attempt, I was able to get all excep that the print area was getting broken into 4 pages (i.e. pdf file was showing to have 4 pages - breaking the contents).


If we to do it manually, then we have to adjust the vertical and horizontal pagebreaks to get all the contents in a single page and then do the save as pdf type to get the contents in a single page.


I tried recording the steps and i got the following code:

Range("B5:F54").Select

ActiveSheet.PageSetup.PrintArea = "$B$5:$F$54"

ActiveWindow.View = xlPageBreakPreview

ActiveWindow.SmallScroll Down:=18

ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1

ActiveWindow.SmallScroll Down:=-33

ChDir "C:Usersamana001Desktop"

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"C:Usersamana001Desktop" & Range("D15").Value & ".pdf",Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False

Range("B3").Select

ActiveWindow.View = xlNormalView


then i faced these issues:

1. The above code works only if the sheet is new i.e. data copied into new sheet and macro is run.

2. Here the line - ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1 - gives me an error if i try to use macro again on the same sheet. If i comment it then the rest of the code works fine and i get a pdf file with only 1 page.

3. Then i made the changes and with the below code, i was able to get it number of pages in the pdf file down to 2.

newWB.ActiveSheet.PageSetup.PrintArea = "$B$5:$F$54"

ActiveWindow.View = xlPageBreakPreview

ActiveWindow.SmallScroll down:=18


I am at a loss of how to code the manual adjustments for vertical & horizontal pagebreaks.


PS. Sorry if the description is too long.
 
Back
Top