Access VBA Print DoCmd

StacyRacca

New Member
Joined
Nov 2, 2006
Messages
3
I'm a begginer at Access VBA and I'm trying to right simple code to get access to print a report to a specific network printer. Can anyone help with a simple VBA print command for hitting a network printer?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Stacy

I have tested the following code over a couple of network printers and it seems to work ok. I have seen all sorts of complicated code over the internet but what I have written seems to be the simplest solution - so hopefully this works for you too.

You need to have the other network printer installed on the your PC and you refer to the printer by its name (that you would see under the 'Printers' screen in Windows).

I invoked this code by placing a button on a form and attaching this code to that button:

Code:
Private Sub MyButton_Click()

Dim defPrinter As String, NewPrinter As Printer

'Get the default printer name
defPrinter = Application.Printer.DeviceName

'Create a new printer object
Set NewPrinter = Application.Printers("MyPrinter") 'use your printer name here

'Set the default printer to the new printer
Set Application.Printer = NewPrinter

'Open the report
DoCmd.OpenReport "MyReport", acViewNormal  'use your actual report name here

'Print the report (the next line seems to print the report and the form so I removed it, if your report doesn't print then remove the apostrophe on the next line)
'DoCmd.PrintOut

'Close the report
DoCmd.Close acReport, "MyReport", acSaveNo  'report name again!

'Reset the printer back to the default printer
Set NewPrinter = Application.Printers(defPrinter)
Set Application.Printer = NewPrinter

HTH, Andrew
 

cooksc

New Member
Joined
Jul 2, 2010
Messages
5
You're great!!! I loaded your code, followed the VERY clear instructions and the code ran perfectly. Thanks so much

My question is: I need to print a PDF file, which worked great using your code, however I would like to know how have the file/report print without prompting me for a location and file name. Can this be written into the code and have the file overwrite the existing file. Also, would need to have code that allows the enough time for the file to print before executing the rest of code.

Thanks in advance for you help.
Steve
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top