Print to PDF with Looping

[ykobure]

Good day gurus!

First off, please take a look at the attached Excel workbook to get a good grip of the challenge I am currently facing

Now to the query.

I have a task to populate a form and save it as PDF. It is actually a resume database and all entries/fields are in Excel sheet. Easy, right? Easy to manually do it if there are only say 5 to 10 resumes, and convert them to PDF one by one. The problem arise as the there are as of the moment, close to 100 different entries/resumes that I need to convert to PDF. I will be needing a system that loops through the ID Number in "DATABASE" sheet, get the information per row that correspond to that ID number and send to "RESUME FORM" sheet, and convert that sheet to PDF. I should end up with multiple pages of PDF file.

I am quite confident that gurus here will be able to find a solution to this challenge, as I have been posting questions here before with more complicated systems. Hope you guys an help me with this.

Thanks in advance!

[Alf]

Perhaps something like this?

Please Login or Register  to view this content.

This will save the pdf file to the folder C:\Temp so you will need to change this to the folder of your choice. Don't forget to add the last backslash or the macro will not work.

This string works "C:\Temp\" but not one like this "C:\Temp"

The pdf file name is set up as "Form_ID number" so change the string fName if you like a more descriptive name.

Alf

[ykobure]

Perhaps something like this?

Please Login or Register  to view this content.

This will save the pdf file to the folder C:\Temp so you will need to change this to the folder of your choice. Don't forget to add the last backslash or the macro will not work.

This string works "C:\Temp\" but not one like this "C:\Temp"

The pdf file name is set up as "Form_ID number" so change the string fName if you like a more descriptive name.

Alf

Works like a charm! Insane skills you have there, good sir! Thanks for giving solution to my problem.

One last thing, though you opt not to do this since your code has done a great job. But if it still possible to have everything in 1 PDF, I will take it. But for now, please take my Reputation Points! :D

[Alf]

I should end up with multiple pages of PDF file.

Oooooops I missed that part but as this is New Years eve and I'm of to friends of mine to celebrate I'll have a go at it tomorrow.

Thanks for feedback and rep

A happy New Year to you Sir.

Regards

Alf

[bakerman2]

See if this is what you're looking for.

Please Login or Register  to view this content.

[Alf]

Having checked the macro posted by bakerman2 I think this is the solution you was looking for and as I have no better ideas I'll just relax.

The only thing that worries me is that in your uploaded file you use the "RANDBETWEEN" function for "AGE" and "WORK_EXPERIENCE". As both "RAND" and "RANDBETWEEN" are volatile function i.e. every time a change occur the values for "AGE" and "WORK_EXPERIENCE" will be updated with new values.

If you do wish to have fix values for these parameters you either use fixed values instead of "RANDBETWEEN" or you set calculation to "Manual".

Adding the line

Please Login or Register  to view this content.

at the start of the macro should freeze the "AGE" and ""WORK_EXPERIENCE" values during the macro run and adding the line

Please Login or Register  to view this content.

at the end of the macro should restore things to normal.

Alf

[ykobure]

Hello gurus, sorry for late replies. Had a blast this new year :D

Thanks for still taking the challenge I posted. Your inputs really helped me a lot!

@Alf, the RANDBETWEEN function is just for sample purposes. At that time I was a bit lazy to come up with random numbers for the sample, so I thought of do it automatically. The final file I am working on has fixed values for age.

And one last thing before I set this thread to SOLVED, a new direction just surfaced and in addition, they wanted to instead of multiple PDF pages, they are now requiring me to loop it and ended up with multiple sheets of "RESUME FORM". I already solved this, but my problem now is to rename the sheets with based on LAST_NAME. I attempted to do this using

Please Login or Register  to view this content.

Please Login or Register  to view this content.

But an error persist saying that the name has been used already. Hope you can help me with this one last challenge.

Thanks in advance!

[Alf]

In your uploaded file the E2 value in the sheet "RESUME FORM" is the "LASTENAME" so if you got two or more entries where the last name is the same then you will get this error message as the macro line you use for naming the sheet is correct.

You could either check all names and see to that all names are unique or you could use a combination of last name and ID add a bit of code to the macro i.e.

Please Login or Register  to view this content.

Alf

[ykobure]

In your uploaded file the E2 value in the sheet "RESUME FORM" is the "LASTENAME" so if you got two or more entries where the last name is the same then you will get this error message as the macro line you use for naming the sheet is correct.

You could either check all names and see to that all names are unique or you could use a combination of last name and ID add a bit of code to the macro i.e.

Please Login or Register  to view this content.

Alf

Thank sir for the follow-up answer. Your inputs are very helpful!

And apologies for taking this long to mark this thread as SOLVED.

[Alf]

apologies for taking this long to mark this thread as SOLVED

No problem

Alf