"Batch Print" Excel Form on a worksheet
I have attached an excel workbook that contains multiple worksheets.
The first worksheet (Scorecard) contains a user form which accesses the other worksheets to get the necessary data. The scorecard worksheet contains a dropdown list box with the possible shops. (For the sake of simplicity I only included data for the first 3 shops out of a possible 1500 or so total shops)
There is also a command button that will allow a user to print the form to a pdf file using a fixed file path and a combination of shop number and current date for the file name.
I was requested to investigate a way in which all shops could be processed without having to go to the drop down and click the command button for each shop one by one.
Any ideas on how I might approach this???
Scorecard-test.xls
The first worksheet (Scorecard) contains a user form which accesses the other worksheets to get the necessary data. The scorecard worksheet contains a dropdown list box with the possible shops. (For the sake of simplicity I only included data for the first 3 shops out of a possible 1500 or so total shops)
There is also a command button that will allow a user to print the form to a pdf file using a fixed file path and a combination of shop number and current date for the file name.
I was requested to investigate a way in which all shops could be processed without having to go to the drop down and click the command button for each shop one by one.
Any ideas on how I might approach this???
Scorecard-test.xls
I just realized you have a button to print to PDF.. you could try this also.
Try adding the following SUB to your Module.
Public Sub cmdPrint_Click()
' Calls Public Function PrintSheetsToPDF which will
' Print the specified sheets to a PDF file in the order specified. Requires
' Adobe Acrobat 7.0 and a reference to Acrobat Distiller. Returns True if the
' print was successful, False otherwise.
'
' Syntax
'
' PrintSheetsToPDF(Sheets, PDFFilePath, [ReorderSheets])
'
' SheetsToPrint - Array of sheet names to be printed. The sheets included are
' sorted in that order and then printed in one print job. When the printing
' is complete the original order is restored.
'
' PDFFilePath - Full path to the PDF file.
'
' ReorderSheets - Pass True to reorder the sheets to be printed in the order
' specified, False to not sort. optional. If omitted then False is assumed.
'
' Example
'
' Print sheets "Sheet4", "Sheet10", and "Sheet1" in that order:
' PrintSheetsToPDF Array("Sheet4", "Sheet10", "Sheet1"), "C:\Output.PDF"
'PrintSheetsToPDF "Sheet1", Sheets("Sheet1").[A1] & "\" & Sheets("Sheet1").[B1]
'Note: assumes the path is in Sheet1!A1 and the file name is in Sheet1!B1.
PrintSheetsToPDF "Scorecard", "c:\scorecard" & "\" & Sheets("scorecard").[d2] & "_" & Format(Now(), "mmddyyyy")
End Sub
Public Sub runMe()
Dim rng As Range
For Each rng In Range("A1:A1923")
ActiveSheet.Range("D2").Va
'refresh the sheet
ActiveSheet.Calculate
'save sheet as workbook
Call cmdPrint_Click
Next rng
End Sub
NG,
Try adding the following SUB to your Module.
Public Sub cmdPrint_Click()
' Calls Public Function PrintSheetsToPDF which will
' Print the specified sheets to a PDF file in the order specified. Requires
' Adobe Acrobat 7.0 and a reference to Acrobat Distiller. Returns True if the
' print was successful, False otherwise.
'
' Syntax
'
' PrintSheetsToPDF(Sheets, PDFFilePath, [ReorderSheets])
'
' SheetsToPrint - Array of sheet names to be printed. The sheets included are
' sorted in that order and then printed in one print job. When the printing
' is complete the original order is restored.
'
' PDFFilePath - Full path to the PDF file.
'
' ReorderSheets - Pass True to reorder the sheets to be printed in the order
' specified, False to not sort. optional. If omitted then False is assumed.
'
' Example
'
' Print sheets "Sheet4", "Sheet10", and "Sheet1" in that order:
' PrintSheetsToPDF Array("Sheet4", "Sheet10", "Sheet1"), "C:\Output.PDF"
'PrintSheetsToPDF "Sheet1", Sheets("Sheet1").[A1] & "\" & Sheets("Sheet1").[B1]
'Note: assumes the path is in Sheet1!A1 and the file name is in Sheet1!B1.
PrintSheetsToPDF "Scorecard", "c:\scorecard" & "\" & Sheets("scorecard").[d2] & "_" & Format(Now(), "mmddyyyy")
End Sub
Public Sub runMe()
Dim rng As Range
For Each rng In Range("A1:A1923")
ActiveSheet.Range("D2").Va
'refresh the sheet
ActiveSheet.Calculate
'save sheet as workbook
Call cmdPrint_Click
Next rng
End Sub
NG,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly what I was looking for...Thanks!
For anybody that might be following this link in the future. I have included the spreadsheet with the
suggested changes
Scorecard-test.xls
For anybody that might be following this link in the future. I have included the spreadsheet with the
suggested changes
Scorecard-test.xls
Glad I was able to help :>)
NG,
NG,
Since your stores are in range A1:A1923 just loop through the range and place each store in cell D2, refresh then svae the scorecard to a directory.
This is just an example of how to accomplish what you want it will need to be coded correctly for your instance. Depending on what you plan to do with the "Scorecard" will dictate how you handle the save/print, etc.
Dim rng as Range
For each rng in range("A1:A1923")
activesheet.range("D2").va
'refresh the sheet
activesheet.Calculate
'save sheet as workbook
activeworkbook.SaveAs fileName:=folder & sht.Name, FileFormat:=xlNormal
next rng
Ng,