If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
1-52 pages, 8,000 employee numbers
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 |
#2
|
|||
|
|||
1-52 pages, 8,000 employee numbers
On Apr 19, 2:17*pm, gatecrasherg13 gatecrasherg13
wrote: 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/s...ict993043.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"--Invoice_Key" '"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 |
#3
|
|||
|
|||
1-52 pages, 8,000 employee numbers
On Apr 19, 2:17*pm, gatecrasherg13 gatecrasherg13
wrote: 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 Can I configure my form using a lookup table for the week (01 - 52) and employee number (0001 - 8000)? Can I export from Excel so that it prints every combination of those two lookups? Thank you very much |
#4
|
|||
|
|||
1-52 pages, 8,000 employee numbers
I'll make an offer: I'll help you cut down a large portion of a good sized
forest to print these things if you'll get in touch with me via emal at (remove spaces): Help From @JLatham Site. com Make a reference to your posting here, or at least mention 'gatecrasher13' and remind me of what it is you need to do. We can start figuring out how to get it done. Tell me: What version of Excel you are using; What version of Windows you are using; and what do you have installed on your system as a PDF printer. If at all possible, a copy of the file to be printed would be MOST!! helpful, or at least something with the same number of sheets and layout on them even if the data is somehow dummied up. "gatecrasherg13 gatecrasherg13" wrote: On Apr 19, 2:17 pm, gatecrasherg13 gatecrasherg13 wrote: 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 Can I configure my form using a lookup table for the week (01 - 52) and employee number (0001 - 8000)? Can I export from Excel so that it prints every combination of those two lookups? Thank you very much . |
Thread Tools | |
Display Modes | |
|
|