Macro to generate documents from database

[katrlow]

Scenario
Have attached a spreadsheet ? hopefully it?s easier to understand. For easier review, I?ve put both worksheets into one.

I have an excel database (Database worksheet) for all employees (1 row for 1 employee) & many columns of data fields. Each employee has a unique identifier (Employee ID).

I have another template form (Form worksheet) which I need to draw out all the data from the database. Currently I would have to enter the employee ID for each employee in cell B2 & formulas (match/ index etc) would draw out the information from the database worksheet into the form and subsequently print out the form and repeat this process 500 manually!!

Does anyone know how to create a macro to insert the unique identifier for an employee, print the document and repeat for the rest of the population?

Many thanks!
katrina

[Charlize]

Something like this ?Sub Print_Form_All_Employees()
Dim cell As Range
'Worksheet with form
Worksheets(1).Select
'Define what we want to print
ActiveSheet.PageSetup.PrintArea = "$A$2:$C$19"
'Area with data in it
For Each cell In Worksheets(1).Range("E3:E" & Worksheets(1).Range("E" & _
Rows.Count).End(xlUp).Row)
Worksheets(1).Range("B2") = cell.Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'A little wait to give the printer time to print
Application.Wait (Now + TimeValue("0:0:5"))
Next cell
End SubCharlize

[katrlow]

Charlize

Thanks!!! Super!!!

Just realised a few more issues from this:-
What should I do if I want to stop printing the documents at anytime?
Is there a way to print pdf & save in a certain folder?

Thanks!
katrina

[Charlize]

Charlize

Thanks!!! Super!!!

Just realised a few more issues from this:-
What should I do if I want to stop printing the documents at anytime?
Is there a way to print pdf & save in a certain folder?

Thanks!
katrinaTake a look at this link to create pdf files within excel from vba : http://excelguru.ca/node/21

Charlize

[Charlize]

Because it could be usefull for me, I've created :igiveup: this little thing. You have to select the macro's with Alt+F8. Later you could create a menubar with the macro's already in (or rightclick menu)

1. Create separate worksheets for every employee
2. Print the pdf
3. Remove the worksheets

Tested this with excel xp (2002) and works fine. You'll have to install that pdf printer and set a reference in the vbe at pdfcreator.

The pdf-files will be created in the same directory as the workbook.

Charlize

ps.: I don't know the limit of the number of worksheets a workbook can hold. According to the limits it depends on the memory ??? Anyone ever had to have 500 worksheets in a workbook ?

[Charlize]

Did it the trick for you ? Just curious.

[katrlow]

Hi Charlize
Thanks for following up. Sorry I didnt get back to you earlier. Wasnt sure if the file would be able to handle all 500 sheets. After a few more researching have found an alternative instead. But it needs Acrobat Distiller on computer.

First you need to split the calc & the database into 2 sheets ('Calc' & 'Database'. The run this:-

Sub Print_Form_All_Employees()

For Each cell In Sheets("Database").Range("A2:A" & Sheets("Database").Range("A2").End(xlDown).Row)
Sheets("Calc").Range("B1") = cell.Value
Printfiles cell.Value
' this call the "printfiles" function below
Application.Wait (Now + TimeValue("0:0:1"))
Next cell
End Sub
Private Function Printfiles(ps_name As String)
' Ensure the following:-
' Adobe pdf settings UNTICK "Rely on system fonts only do not use document fonts"; TICK "delete log files for successful jobs"
' Change the filename below
' Run the macro
' Once all files are printed Open Adobe distiller separately. Settings -> Security -> enter a password - This will then applied to all the new Pdf conversions.
' Final step is to drag the ps files onto the Distillier Window
'************************************************************************** ****************************
'This is to set the destination folder for the PS outputs
Dim Strpath As String
Strpath = "C:\My Documents\"
'************************************************************************** ****************************
ps_name = Strpath & "Employee Calc - " & ps_name & ".ps"
ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="Adobe Pdf", copies:=1, PrintToFile:=True, _
PrToFileName:=ps_name
End Function


Let me know if it works for you.

I've only managed to find out how to create one password for ALL files. Would you know how to create unique passwords for each files?

Cheers
katrina

[Charlize]

Have you tried it with 500 id numbers ? Anyway, take a look at this link. It could be interesting : http://www.igetit.net/SampleCode/Excel_To_PDF_vba_code.asp . I don't have that acrobat distiller program (is it freeware ?) so I can't test it, sorry.

[Charlize]

This is for the security with pdf creator : http://www.excelguru.ca/node/31