Dear Excel,
I have the ultimate challenge: to do job books for 8,000 employees.
The form is completed, but the request is to make booklets of 52 pages
for each employee.
Each page of the booklet must also have each employee's number on it
(0001 to 8000). Each page o fthe booklet must be numbered with the
week number from 01 to 52. Therefore I need to auto generate 416,000
pages. Ridiculous I know but nobody listens to us!
How can I export the above scenario to PDF from Excel?
Thanks
I found the following code from
http://help.lockergnome.com/office/sequential--ftopict993043.html
However, 1) the code doesn't work for me and 2) that registry folder
does not exist on my computer (if I create it, what exact path would
it go in anyway?)
Isn't there any simpler solution to my question above? Surely this
must be a common request?! Thanks for all feedback.
Yours
In the Thisworkbook section of the VB is the code
Public Sub Workbook_Open()
Worksheets("invoice").Range("d1") = False
End Sub
' Disable the general sheet printing function so its all controlled by
the print button macro
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Worksheets("invoice").Range("D1") = False Then
Cancel = True
End If
End Sub
Then create a button on the worksheet with the code
Private Sub CommandButton1_Click()
Dim CopiesCount As Long
Dim CopieNumber As Long
Dim nNumber As Long
' Set the location of the registry entry to hold the sequential number
outside Excel
'use Start--> run--> regedit to open the registry
'registry location is 'Software-->VB and VDA Program Settings-->Excel--
'"Invoice" is the sheet name used in this example it needs to be your
sheet name.
'"invoice Key" is just a registry entry name - it can be anything you
want for your application
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) 'retreive
the last sheet number printed from
' the registry
' Ask the user how many copies of the template to print
'You can modify this line to say default print 100 copies or whatever
you need
CopiesCount = Application.InputBox("How many Copies do you want to
print?", , 1, Type:=1)
For CopieNumber = nNumber To (nNumber + (CopiesCount - 1))
With ThisWorkbook.Sheets("Invoice")
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
..NumberFormat = "@"
..Value = Format(nNumber, "0000")
Else
..Value = Format(CopieNumber, "0000")
End If
End With
Worksheets("invoice").Range("D1") = True
'Print the sheet
..PrintOut
End With
Next
nNumber = CopieNumber
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber&
Worksheets("invoice").Range("D1") = False
End Sub
