kingnutin
Board Regular
- Joined
- Jun 15, 2005
- Messages
- 67
I currently have and Excel file that is about 1.5mb in size. The file is too big, and I need to make it smaller so that it can be opened faster. This document has close to 80 different worksheets. I have a GUI that opens on the main page and gives a selection of what sheets the user wants to print. Based on the selection, the user can print to any printer installed on their machine.
I have seperated all the printable sheets into their own Excel file, so that my main file now only has 5 worksheets and is at 220kb.
Now the BIG problem! I have rewritten the code so that when a user selects a single sheet it prints just as before buy opening the selected sheet(now a workbook), printing and then closing that opened workbook.
Sometimes the user need to print a range of sheets at once. The method used before was that the printer would print a range of sheets that were listed in a named range.
For example: The user selects to print a New Patient Packet. In an Excel named range of New Patient Packet, would contain a list of all sheets needed to be printed.
I cannot get this to work now that I have seperated the sheets into their own workbooks. All the workbooks need to be selected and printed at the same time because if a user chooses to print to a pdf, all pages must be in the same pdf file and not multiple pdf's. Again, this code worked perfect before I split the sheets into their own workbooks.
I figure that this code has four problems:
1. If the list of documents to be opened only contains the sheetnames, how to make the path direct to the FilePath variable. i.e.Range contains book1.xls, book2.xls, book3.xls.... but open FilePath + book1.xls, FilePath + book2.xls....
2. How to make it so that multiple worksheets are opened from the range that is passed to the subroutine.
3. How to print multiple workbooks that are opened at once.
4. How to close those newly opened workbooks.
Any and all ideas and comments are greatly appreciated!
I have seperated all the printable sheets into their own Excel file, so that my main file now only has 5 worksheets and is at 220kb.
Now the BIG problem! I have rewritten the code so that when a user selects a single sheet it prints just as before buy opening the selected sheet(now a workbook), printing and then closing that opened workbook.
Code:
Sub PrintSheet(SheetName As String)
FilePath = "C:\Documents and Settings\user\My Documents\My Designs\Forms\Test Documents\"
If Application.Dialogs(xlDialogPrinterSetup).Show Then
Workbooks.Open (FilePath + SheetName)
Windows(SheetName).Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.Close SaveChanges:=False
End If
End Sub
Sometimes the user need to print a range of sheets at once. The method used before was that the printer would print a range of sheets that were listed in a named range.
For example: The user selects to print a New Patient Packet. In an Excel named range of New Patient Packet, would contain a list of all sheets needed to be printed.
I cannot get this to work now that I have seperated the sheets into their own workbooks. All the workbooks need to be selected and printed at the same time because if a user chooses to print to a pdf, all pages must be in the same pdf file and not multiple pdf's. Again, this code worked perfect before I split the sheets into their own workbooks.
Code:
Private Sub FilePrepPrint(PrepRange)
With Worksheets("File Prep Config")
temp = Join(WorksheetFunction.Transpose(PrepRange), ",")
varr = Split(temp, ",")
End With
If Application.Dialogs(xlDialogPrinterSetup).Show Then
Sheets(varr).PrintOut Copies:=1, Collate:=True
End If
End Sub
I figure that this code has four problems:
1. If the list of documents to be opened only contains the sheetnames, how to make the path direct to the FilePath variable. i.e.Range contains book1.xls, book2.xls, book3.xls.... but open FilePath + book1.xls, FilePath + book2.xls....
2. How to make it so that multiple worksheets are opened from the range that is passed to the subroutine.
3. How to print multiple workbooks that are opened at once.
4. How to close those newly opened workbooks.
Any and all ideas and comments are greatly appreciated!