Vba fileprintsetup

For example, instead of using the following code:. For more information about the ActivePrinter property, click the following article numbers to view the articles in the Microsoft Knowledge Base:. Summary When you use the ActivePrinter property in Microsoft Office Word to set the current printer, you also set the printer you select to be the default printer for the system.

Application" oWord. Need more help? Expand your skills. Get new features first. You can use the information within this Tutorial to decide which statements are useful to achieve your purposes. The PrintQuality property has a single optional parameter: Index. You use index to specify to which of the following you're referring to:. If you don't use the Index argument, you can set the property to an array with 2 elements. The first element contains the horizontal print quality. The second element contains the vertical print quality.

The basic statement structure you can use to specify the PrintQuality property looks roughly as follows:. In practice, working with the PageSetup. PrintQuality property is trickier than it may seem at first glance. The main reason for this is that the precise specification depends on the printer driver. In other words, the precise VBA statement you should use usually varies from case to case. You can usually get a good idea of the exact syntax you work with by using the macro recorder.

The Draft property allows you to print as draft. The main consequence of printing as draft is that Excel doesn't print graphics. This makes the printing process faster and usually requires less ink. The following statements show the basic structure you can generally use to work with the Draft property:.

You can use the BlackAndWhite property to print in black and white. This property, however, only applies to worksheets. The PageSetup. HeaderMargin and PageSetup.

FooterMargin properties allow you to specify the margin or the header or footer as appropriate. The syntax of the HeaderMargin and FooterMargin properties is substantially the same.

To specify these margins, use the following statement structure :. However, you can choose whether Excel aligns the header and the footer with the general right and left page margins by using the PageSetup. AlignMarginsHeaderFooter property.

AlignMarginsHeaderFooter can take either of the following values:. Because of the above, you can use the following statements to determine whether Excel carries out the header and footer alignment:. The setting you choose for this property influences all headers and footers : left, right and center. The following comparison shows how Excel aligns headers and footers when they're aligned with page margins vs.

The syntax of these properties is substantially the same. In other words, to set a margin, use the following basic statement structure :. Most of the settings within the Margins tab of Excel's Page Setup dialog box deal directly with margins. I explain the most relevant margin-related VBA properties above. At the bottom of the Margins tab, there are 2 settings that allow you to specify whether Excel centers the sheet horizontally or vertically when printing.

The CenterHorizontally property allows you to determine whether Excel centers the content horizontally on the printed page. You specify this by setting the property to one of the Boolean values True or False, as follows:. The CenterVertically property is like the CenterHorizontally property above. By using CenterVertically, you can specify whether the content is centered vertically on the printed page.

The syntax you use to specify your vertical-centering setting is roughly as follows:. I explain properties 1 PageSetup. FooterMargin and 20 PageSetup. HeaderMargin above. Please refer to the appropriate section for their explanation. In addition to a description of all these properties, I include a list of the VBA codes you can use to format headers and footers, or add certain items to them.

If you want to set a header or footer , use the following properties of the PageSetup object:. In the previous section, I introduce the properties that allow you to specify a header or footer. Those properties deal with strings.

The properties I cover in this section also allow you to specify headers or footers. However, in this case, the properties deal with graphic images. In other words, you work with the following properties to specify a header or footer picture :. The reason for this is that the properties I cover in this section return a Graphic object. The Graphic object holds the properties of the header and footer pictures. In the following sections, I provide more details about the most relevant of these properties.

Property 10 Graphic. Filename is particularly important. Use the Graphic. Brightness property to specify the brightness of a footer or header picture. The following lines show the basic statement structure you can use for these purposes:. You can use the Graphic. ColorType property to apply certain color transformations to a header or footer picture. You specify the appropriate transformation by using one of the following MsoPictureColorType constants:. The Graphic. Contrast property allows you to specify the contrast of a header or footer picture.

The following list shows the basic syntax you use to work with the properties above:. If you're specifying a header or footer image, you'll need to deal with this property. Filename allows you to specify the location of the file you use as header or footer picture. The 3 properties I cover in this section Height, LockAspectRatio and Width allow you to specify the size of a header or footer image.

You can set the height and width of the header or footer picture with the Height and Width properties respectively. The statements you can use for these purposes look as follows:. The LockAspectRatio property allows you to choose which of the following ways Excel behaves when you resize a picture:.

The statement structure you can use to set the value of the LockAspectRatio property is as follows:. You specify whether any of the differences above applies by setting the relevant property to True. The following basic statements do this:. I explain how you do this in the sections covering the PageSetup.

FirstPage and PageSetup. EvenPage properties below. Use the ScaleWithDocHeaderFooter property to determine whether Excel scales the header and footer with the rest of the document when you change the size of the document.

The table below displays the special codes you can use as part of header or footer properties. These codes allow you to do the following :. Some of these codes can be very useful. You can check out an example of what you can do with them in macro example 2 below. In that case, I use codes to:. If you're dealing with codes where you must include numbers at the end for example, fontSize , be careful with how you structure the string you assign to the header or footer property.

In these cases, you generally want to avoid structuring your string in such a way that you have numbers in the following places:. Such a structure may result in Excel interpreting the whole digit combination both 1 and 2 above as a single number.

A possible solution to this issue is including a space after the special code. You do this by setting the applicable property to True. I explain how you can specify such characteristics in this section. I explain step 1, and provide the basic statement syntax you use, above. In this section, we take a closer look at the constructs involved in steps 2 to 4. You can also go through our other suggested articles —. Submit Next Question. By signing up, you agree to our Terms of Use and Privacy Policy.

Forgot Password? This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy. Popular Course in this category. Course Price View Course.

Login details for this Free course will be emailed to you. Email ID. Contact No. Kait Kait 41 1 1 gold badge 1 1 silver badge 4 4 bronze badges. Thanks for the reply! Those solutions only allow me to change PAGE specific settings That could help with the color printing, but that actually would resolve itself if I could get it to send to the right printer, which defaults to color.

And how do I tell that printer to print duplex? After looking through those, I'm definitely closer. I exported the active printer name to a cell I also added "Application. Show" before my PrintOut command so that at least allows me to go into the printer settings and set duplex before the print preview.

So this is a viable workaround, though I'd still prefer to hard-code duplex printing in so I don't have to change the settings each time. Show 4 more comments. Active Oldest Votes. Partial solution: Color settings can be done through Page Setup The ActivePrinter declaration in my PrintOut call was not working because I did not have the printer name exactly as the system wanted it.

ActivePrinter To output to a cell so you can copy-paste the text: Sheets "Sheet 1". Range "A1". ActivePrinter As a workaround for changing the printer settings to duplex: Change the active printer and open the print dialog box before the PrintOut command: Application.

Current code: 'Set print settings Application.