|
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Method for Excel Macros and VBA in Excel. All of the arguments are explained below as well as how to use each argument and why you might want to use each argument.
After reading this page, you will be able to create your own printing macros in Excel. One thing to note is that you only need to include the arguments which you want and they do not have to be in sequential order. For example, this means that if you don't need the "PrintToFile" argument, simply don't include it in your macro.
The PrintOut Method in Excel is how you print anything and everything from Excel within a Macro. This however, does not provide all of the formatting options that are possible in Excel. There are many other things you can do in terms of formatting for printing if you use the PageSetup features in a Macro. However, you will still have to actually print the Excel worksheet or workbook and this is why you need to PrintOut Method in Excel Macros.
PrintOut Arguments and Parameters Listed Below
From
This argument allows you to designate the number of the page from which you would like to start printing. This allows you to more specifically narrow down the section of the Excel workbook or file which you would like to print. If you don't include this argument the printing will, by default, start with the first page of the file, simply the beginning of the file.
- Set this argument equal to the number of the page from which you would like to start printing the Excel file.
- Don't include this argument if you want to start printing from the very first page of the file.
To
This argument allows you to determine the number of the last page to be printed from Excel. If you don't include this argument Excel will print everything to the last page.
- Set this argument equal to any integer that is within or up to the number of pages to be printed in order to tell Excel up to what page you want printed.
- Don't include this argument if you want all pages up to the last page (starting from where designated in the "From" argument if it is included) to be printed.
Copies
This argument allows you to tell Excel how many copies of a file you would like to have printed. If you don't include this argument Excel will print one copy of the Excel file.
- Set this to any number (integer) that corresponds with the number of copies which you would like to have printed.
- Don't include this argument if you simply want one copy of the Excel file to be printed.
Preview
If you want the macro to display the Print Preview window before the file is actually printed, you can use this argument. If you activate this argument, the file will not automatically print. Once the Print Preview window has been reached, the user will have to click the print button to actually make the document print. This is a good option to use if you need to verify the look and feel of the printed version of the file before it actually prints.
- Set this argument to True to make the file appear in Print Preview mode before it is able to be printed.
- Don't include this argument or set it to False if you want the file to automatically and immediately print.
ActivePrinter
This is where you determine to what printer you would like to print. You can print to a regular paper printer, network printer, PDF printer, or any other printer that is already connected to the computer. The value for this argument is the actual name of the printer as it appears in the Print Dialog - to find out the name hit Ctrl + P in Excel and look to the Printer section at the very top and to the right of where it says "Name:" you will see a drop-down menu which lists all of the printers available to your computer. The exact text that appears there as the name for the printer is what you need to enter for this argument; remember that the name must go inside of quotation marks.
- Put the name of the desired printer inside of quotation marks for this argument.
- Don't include this argument if you want to use the default printer.
PrintToFile
If you would like to print the Excel file to a file instead of from a printer onto paper, you can use this argument. This is most often used when converting an Excel file into a PDF file because you have to 'print' to the PDF file.
- Set this argument to True if you want to print to a file.
- Don't include this argument or set it to False if you want to print the Excel file to paper.
Collate
This option allows you to determine whether or not you would like the printed copies of the workbook or worksheet to be collated. This argument is irrelevant if you are only printing one copy.
COLLATE: This means that if you have a five page document, which you want to print 2 times, and you choose to collate the document, it will print in this order: 12345 12345. If you choose not to collate the document will print in this order: 11 22 33 44 55.
- Set this to True to collate copies of the printed Excel file.
- Set this to False in order not to collate multiple copies of the Excel file.
PrToFileName
If PrintToFile is set to True, this will specify the name to which you would like to save the file. Depending on the type of file you are printing and the software used, this may or may not actually set the saved file to the name defined here.
- Make sure to enclose the name in quotes or leave this argument blank or with empty quotes (i.e. quotes with nothing in between.
IgnorePrintAreas
This determines whether or not to respect prescribed print areas within the worksheet or to simply print the entire worksheet or workbook and not just the selected print areas.
- Set this to True to ignore print areas in Excel.
- Don't include the argument or set it to False to respect print areas in Excel.
Where to install the macro: Module
Macro to Print in Excel - Complete PrintOut Method with All Arguments Listed
Sub PrintOut_Method_All_Parameters_Listed()
'List of all of the optional parameters for printing an Excel spreadsheet using the PrintOut method
Worksheets.PrintOut _
From:=1, _
To:=10, _
Copies:=1, _
Preview:=False, _
ActivePrinter:="", _
PrintToFile:=False, _
Collate:=True, _
PrToFileName:="", _
IgnorePrintAreas:=False
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hi All,
I'm trying to print my Excel worksheet to a File, using the PrintOut method. My VBA satement is as follows:
Worksheets("WorksheetName").PrintOut Preview:=False, PrintToFile:=True, PrToFileName:=strFilename
I have tried different FileName extensions for my strFileName, such as ".doc" (for Word), ".pdf" (for Adobe PDF), and ".ps" (for PostScript). I can save these different file types, but I have problems opening and printing these files.
I suspect it may be due to Printer types and their drivers installed on my PC? However if I choose ".doc" (for Word) as an extension, then surely most printer types can handle this type of file? Ideally I would not like to resort to File Converter programs.
Any help or guidance on this will be most appreciated.
Thanks,
Pete
i need my sheet to be a picture (jpg,png,bmp. etc) in order to beable to print it. don't ask why
how can i do that from excel?
Hello,
I'm having problems with using the printout method in a vba procedure. The code I have is....
Code:
With Worksheets("Master")
.PageSetup.PrintArea = "A1:O" & lr
.PrintOut Copies:=1, ActivePrinter:="AYVP0012", collate:=True
End With
The problem is with the networked HP printers we have in our office, these are setup to print with a pin.
When I normally print from excel I have no problems with the pin. When using the above code to print my pin is not accepted, which I find very odd.
Has anyone experienced this behaviour before? Any help or suggestions would be welcomed as this has me stumped.
Hi,
I am trying to use PrintOut methos to save an excel file
to pdf file and It creates file which I cannot open .
code is
Sheets(Array("009_report")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True, PrintToFile:=True, ActivePrinter:="Acrobat
Distiller on Ne00:", prToFileName:="c:\test.pdf"
it actually saves file there but I cannot open that file
can someone pls help.
I really appreciate your help
Jagan
I would like to have an Excel macro print to a printer defined in a
variable so I dont have to hard code the printer name in the code.
I have been successful with the following code:
ThisWorkbook.PrintOut ActivePrinter:="Printer Name"
What I would like to do is something like:
Set PrinterNameVariable = "Printer Name"
ThisWorkbook.PrintOut ActivePrinter:=PrinterNameVariable
Any ideas?
Thanks
I have a colleague who is the only one in our group who is unable to print PDFs automatically with a macro on the network. His excel returns a 1004 on ...
Code:
'print PS
Worksheets(ShtsToPrt).PrintOut _
Copies:=1, _
Preview:=False, _
PrintToFile:=True, _
Collate:=True, _
PrToFileName:=temp_PS
ShtsToPrt is an array of selected sheets passed from another module used for printing automation (I use the same print modules on the network for all of my workbooks), and temp_PS is the full postScript file name/path.
I originally thought that it was because he had XL97 installed on his computer along with XL03 (XL97 doesn't support "PrToFileName"), but we had it removed and it still gives the same 1004 error. I am stuck, any suggestions? TIA
I am using the following code to printout a worksheet and am having
some problems.
1. I cannot get it to print in color. Is there a way to specify this
inside of the .Printout method.
2. I cannot get it to duplex. Is there a way to specify this? I have
my printing preferences set to both of these settings.
ActiveSheet.Range("A1:G53").PrintOut , Copies:=2, preview:=False,
ActivePrinter:="\\HaddonSrvr\Ricoh 2232C", printtofile:=False,
Collate:=True, prtofilename:=PSFileName
Thanks in advance.
Good morning. I am new to this site but was hoping someone might be able to help me.
I have an application written in VBA in Excel. I have created a new report (workbook) that has various radio buttons on it. These buttons have VBA code behind them to hide certain rows & columns based on certain criteria. Everything works well except after I print the workbook, preview the workbook or or do anything that has to do with printing. Once I do anything having to do with printing the macros that get run from the click events of the radio buttons slow down to a snails pace. They still work properly but literally take 5 to 6 times the amount of time as they did prior to printing. If I shut down Excel and bring it back up the macros start flying again. Is anyone aware of this issue or ever experienced this. I am not sure why printing would permanently slow down my macros until I close and re-open the workbook. Any help or advice anyone could give would be greatly appreciated.
Thanks.
MRAAdam
Here's the code:
Sub optResComOnly_Click()
Dim Cell As Range
Dim sOverallView As String
Dim sLocation As String
sLocation = ActiveCell.Address
application.ScreenUpdating = False
Worksheets("Hidden").Range("SelectedBusSeg").Value = "ResComOnly"
sOverallView = Worksheets("Hidden").Range("OverAllView").Value
For Each Cell In Range(sOverallView)
Cell.EntireRow.Hidden = False
If Cell.Value <> "Visible" Then _
Cell.EntireRow.Hidden = True
End If
Next
Set Cell = Nothing
HideZeroRows
application.ScreenUpdating = True
ActiveWorkbook.ActiveSheet.Range(sLocation).Select
End Sub
---------------------
Sub HideZeroRows()
Dim Cell As Range
For Each Cell In Range("ZeroRows")
If Cell.Value = "X" Then
Cell.EntireRow.Hidden = True
End If
Next
Set Cell = Nothing
End Sub
Hey All!
I have a sheet in my very large workbook that allows the end user to click the active x image tool and upload a picture into it. The problem that I am having is that the page gives me the error "PrintOut method of worksheet class failed" when I use a print button that I created. When I delete all of the image boxes, the code to print works fine! What should I do?
Hello, I have been trying to track this down, but have been unable to find anything simular to what I want to do.
What I have is a worksheet with A to D columns listing peoples names, the rest of the columns list classes that they need to take, split up into weeks (ie Week 1, week 2, week 3, etc, 12 weeks total) and when printed, each week will have it's own page. See example below. I have the frames frozen at E6 so the names will stay in view when moved to the right and left and the top will stay in view when moved up and down.
Is there a way to make the Names print with each Week without having to put the name list next to each week?
class1.jpg
|
|
