Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Use vba to print a specific printer Results

I have a printer named 'eCopy PaperWorks Printer' with the port showing as 'ECOPYPAPERWORKS' in the Print Preferences dialog box. What VBA code would I write if I wanted to print to this specific printer? Easy question, I know, but I'm new. Thanks.

Hi all!

My Question might seem simple on a first glance, but it isn't. (at least for
me

I made an input menu where the user can specify the pages to be printed and
some other parameters. THis is then sent to a specific printer (e.g.
"CutePDF Writer on CPW2:") which I specified in VBA as activeprinter.

The problem now ist, that we use this Excel sheet on several different Pcs
in our office - the printer is still the same but the "on CPWx:" part
changes from on to another pc.
I tried to leave it away, which gives me a nice error message.

Is there any way to get a list of all printer registered on the system and
then to select the one starting with "Cute*"?

thanks for your help

patrick

Using the VBA code, I like to print (on the default printer) an existing file called "XYZ.pdf" that resides in the same folder as the Excel file. What would be VBA code for it?

Important: I am not trying to make a pdf file out of an excel sheet/range. All I want is a VBA macro that prints a specific, existing pdf file (in the same folder as the excel file) to my default printer to print the entire pdf file on paper.
Computer OS: Windows 7
Excel Version: Office 2007

I have placed a button on my worksheet which has some VBA code behind created by recording a macro. What it does is print to a specific printer, namely a device which creates a PDF file of the worksheet.

On my machine it works fine but on others it fails and the debug steps in stopping at the printer selection code. I believe it stops here because the code uses the actual Windows device name (as seen in the Windows registry) when selecting the printer, which could be different on any machine dependant on which order the printers were added.

My question is therefore this, is it possible to select the required printer in some other manner, that is, simply by the name given to the printer.

Here is the VBA code currently assigned to the button...

Private Sub ToggleButton1_Click()
Application.ActivePrinter = "FreePDF XP on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"FreePDF XP on Ne01:", Collate:=True
End Sub

The failure point is the Ne01: as this is the devices value from the Windows registry, found here...
[HKEY_CURRENT_USERSoftwareMicrosoftWindows NTCurrentVersionDevices]

Another way around this would perhaps be to read this value from the registry and insert it in to the code. Not sure if that would be possible.

If anyone has any ideas or answers, they will be greatly appreciated.

Thanks in advance.

Hey guys,
I've posted this issue on many forums without any luck, so hopefully that'll change here!

I have an excel document with 8 tabs, each containing a chart.

I am creating a button which allows the user to print all charts, to a specific printer on our network, in color!

Here is my code so far:
Sub PrintEmbedded()

    Dim shtTemp As Worksheet
    Dim chtTemp As ChartObject
    
     
    'Application.ActivePrinter = "comdocRIC102 on ne06:"
   
    '.ColorMode = acPRCMColor
    
    'Application.Dialogs(xlDialogPrint).Show
     
    For Each shtTemp In ThisWorkbook.Worksheets
        For Each chtTemp In shtTemp.ChartObjects
            chtTemp.Chart.PrintOut
        Next
    Next
    
    
End Sub

The commented stuff is stuff I tried. The for loop goes through each worksheet, finds the chart and prints it.
That works fine, but the program needs to use the correct printer, and also set it to color (since it defaults to B&W).

The issue I'm having with selecting the printer is that the name is different on all computers (mine is ne06, my boss's is ne03), so I think that first commented command is useless.

And I also haven't been able to get the colormode command to work.

I've been trying to mess around with the Application.Dialogs(xlDialogPrint).Show, so the user could manually choose the correct printer, and set it to color. But I haven't been able to use it properly.

Maybe someone has a better idea or knows how to use my ideas to make it work! Thanks

Hi,
when I use Excel with VBA and record a macro to select a printer the result
is like this:

Application.ActivePrinter = "SWLJUNFSLjungby_1 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"SWLJUNFSLjungby_1 on Ne03:", Collate:=True

but I like to use the lower paper-tray. How to solve it?

Torbjörn Pettersson, Sweden

I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed.

The logic, not in true VBA syntax, is as follows;


	VB:
	
If  Is installed Then 
    Printout using  
Else 
    Install  
    Printout using  

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.

Any suggestions?

Regards,

Paul

Hi, this has been bugging me for ages!

We have printer mailboxes setup for all of the employees and sometimes the print settings get saved in the excel files. This means that when another employee uses the file their prints get sent to the wrong mailbox. We have hundreds of files like this and it causes all kinds of problems with prints going to wrong mailboxes!

Obviously their default print settings are all set to their own mailboxes.

Is there some way, using vba, to get an excel file to go back to using the user's default printer settings instead of the ones saved in it's file? (or get it to remove the printer settings saved in it's file). That way I could just make an application level open event to fix the problem!

Either that or some way, in vba, of changing a user's storage box number for a specific printer.

Any help would greatly be appreciated!

We're using Excel 2003, Windows XP proffesional and a Canon IR2200 printer.

Hi guys, I need a command button on a sheet that upon click marks column A and open the 'Search'-window with "Search in - values" as default.

New to Command Buttons so... :-)
Anyone who have any ideas.

Another question as well. I have a worksheet that is printed frequently - and it is printed on a color printer that manages duplex. But for each time I have to go to 'Advanced setting' choose paper source, duplex ('side binding' and if it should be printed in black/white or color.

Is it possible to use VBA/Command to choose the specific printer and settings?

Morning to you all,
/D

I have a macro that prints out several worksheets. My problem is that I want to print these worksheets to a specific color printer. There are many different users and most of them will have a black-and-white printer setup as their default. I want to be able to print using the ip address of the color printer.

I have tried setting the active printer using:


	VB:
	
Application.ActivePrinter = "WP010110.117.3.36" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
with little luck... This seems like such a simple thing! I have done a lot of research and can't find a good solution although I have seen the question raised several times!

Ozgrid has been a great source of information and solutions in the past. Please help again!

Thanks,

William

Hi All:

I hope I can explain this correctly...

I was wondering if there is a code in VBA that instead of printing out a sheet it stores it and then prints at the end of a code

For Example: I am currently using something like this Code:
Sheets("MiscRecon").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Instead of printing the MiscRecon sheet when it get to this part of my code can VBA remember that I want this sheet printed and then print it AFTER the code has finished running?

Why I ask is that on my spreadsheet I run through a large code and the sheets are printed out as the code runs. I wanted to have the code run and then print the sheets at the end in a specific order. I know the best way was probably to write my code in the order I wanted the print outs but that isn't how I wrote it

The problem with printing the sheets as the code runs is that often others are using the printer and then the user has to sort through the printouts looking for their package. If the printouts came out at the end altogether the user can pick up the complete package from the printer without having to sort through other print jobs.

I HOPE I am making sense to someone

THANKS,
Mark

Hello,

I am new in Excel VBA. So I will need yr. expertises and helps. Thanks in advance.

Every day, I receive a bulk XLS file containing bulk invoices. The bulk invoice containing more than 500 invoices which we ship to different stores. I would like to extract individual invoice base on First Row VALUE (CUSTOMER NAME) which repeat for each individual invoice and base on the last row of the invoice which are 3 BLANK ROWS. The extracted data will be paste on NEW worksheet and then PRINT using a SPECIFIC PRINTER & SPECIFIC TRAY. The below is how is look likes

A1
CUSTOMER NAME
DATA
DATA
DATA
BLANK ROW
BLANK ROW
BLANK ROW
CUSTOMER NAME
DATA
DATA
DATA
BLANK ROW
BLANK ROW
BLANK ROW

This is urgent, so any input is very appreciated.

Regards,

In my office system, I am running XPPro SP2. I have one printer attached
locally to the computer but I have set it up as two printers, each with a
different "profile". One "profile" prints on a single side and the other
prints double sided.

I'm having a problem printing to a selected printer. If I manually set one
of the printers as the default in "Printers and Faxes" and set the
ActivePrinter to the other printer in VBA, Excel prints to the one I
initially specified as default. The ActivePrinter variable is set without
any errors so the printer name must be correct. I obtained the printer names
by setting the default printer manually and typing ?ActivePrinter in the
immediate window. It just doesn't printer to the ActivePrinter.

What I would like to do is have the user click a command button which sets
the ActivePrinter to a specific printer. Then, all subsequent print jobs
print to that printer until a different command button is clicked setting the
ActivePrinter to a different printer.

In other words, Excel prints to whichever printer is specified as the
default regardless if I set the ActivePrinter in VBA. I would like to avoid
using Application.Dialogs(xlDialogPrint).Show.

Any suggestions would be greatly appreciated.

Thank you for your time and expertise.

Hi..

I wonder if some one who could give me a bit of enlightenment.

I have 6 machines in a xp workgroup and have a document which i need to
place a print button and send page to different printers.

Each machine has had the printer drivers installed in no particular order so
the ports can be different but they are all networked using tcpip ports.. so
i cant hard code the printers in vba....

But all the names of the printers are numbered in there name all the same...
ie

01 Upstairs Printer
02 Colour Printer
03 ... etc

So I would like to do two things...

To read the list of printer and then match the printer number and send the
page to that ...
and then eventually have a print button which will load the list of printers
off the machine at runtime and give the user a way of selecting the printer
out the list..

Can ppl discuss the pro & cons and way of doing this...

Thanks

Nick

I have a client who developed a very simple MS Access application where he loads data for about 250 stores in a table and prints a report for each store. He wanted to print 250 different reports in pdf form, one for each store, and put the reports in the same folder with a different name for each store. I solved the problem by using VBA code to open the report in design mode, changing the Report caption to the desired report file name, and saving the report. Then I opened the report for print (he had set his PDF printer as the default printer) and applied the filter to limit the report to the desired store. I looped through all the stores till they were all "printed".

Now, I have another department with a similar request, however, his "database" is in Excel. Does anyone know how I can "print" the selected Print Area to the default printer with a specific file name.

Thanks, Eddie

I have an access report formatted to print to a datamax printer. This printer prints tags on 3x5 tag stock that comes on rolls. The problem is that the Brother 1440 laser printer will steal and reformat the tag even though the page setup has the margins and specific printer selected. I have read a little bit on PrtDevMode and PrtDevName, but really don't know anything about them. I am running Access 2K under. I have done some VBA in Excel but nothing too heavy in Access. I could use a little advice.
Thanks!

Background to problem: I run a PDF converter out of an Access database. It was built before I started working here, and I'm none to familiar with how it works. The code is not commented very well, and I'm also unfamiliar with the PDF printer software. Anyway, this PDF Converter (Using Adobe Distiller 5.0) seems to print the excel sheets using a printer named Adobe Postscript Printer. The files move to an "In" folder where they are somehow put together as PDFs, then moved to an "Out" folder, then archived. Somewhere in there, the pdf reader's registry which controls how the Reader is opened (in terms of Maximize or Restore as described here: http://forums.adobe.com/thread/435552) seems to be reset to restore. This bugs me like crazy because immediately after converting, I open a sample of the files and if it is in restore mode I get a bunch of files cascading across my desktop. I'd like to prevent this registry from changing, but I'm not sure how.

Instead, I figured it would be easy to just change the registry value from the 0 to 1 via some vba. The path to the registry is: HKEY_CURRENT_USERSoftwareAdobeAcrobat Reader9.0SDIbNullDocMaximized. Does anyone know how to do this?

Thanks

I wavered on posting this to the noob forum, but this needs to be done programmatically, so here goes. I know precious little about VBA other than what I have learned from various other users in several forums. (thanks y’all)

I already have an input dialog box to set the weight and age used in all the formulas. I want to add another dialog box that will allow the user to specify which sections of the sheet they may actually need. I would like the user to be able to use check boxes to specify a section (range?) and have those selected sections print one following another. Each section is headed by a merged/centered cell.

Although this is essentially ready to go, I would like to enhance it so the user can print only what is needed based on the flight they are dispatched (air ambulance). The current form they are using (hand calculated) is two-sided. When they use this SS, they won’t have access to a duplex printer, so this should print on a single sheet if at all possible.

I’m not looking for someone to do my coding for me, just some direction on what to search and/or maybe a little help with the logic flow. I won’t learn it if I just cut-n-paste!

Hi,

I am a n00b with programming VBA for Excel. There are three issues that I seem to be having with my code. I am writing code that will ultimately create multiple worksheet graphs at the click of a button to save time in the future.

(1)

My first issue is deleting worksheets that are ALREADY in existence in the workbook. These worksheets have been created earlier by the code I wrote. I have triple checked and the worksheet names are not misspelled. I wrote this code so that if there is a problem with any of the graphs, the values can be changed and then the graphs be remade. Here is the code:

Private Sub delete_graph_sheets_Click()

' delete worksheets
    Application.DisplayAlerts = False
    Worksheets("3.1 - Blender Speed").Delete
    Worksheets("3.2 - Hot Cup Density").Delete
    Worksheets("3.3 - Viscosity").Delete
    Application.DisplayAlerts = True

End Sub
I get the error:
Run-time error '9':
Subscript out of range

... with the line "Worksheets("3.1 - Blender Speed").Delete" highlighted yellow during debug. An "ActiveX" button is used to initiate the code from the worksheet called "3.0 - Surface - Emulsion".

(2)

My second issue is more or less one of no information. I read up on how to print files off. However, I was wondering if it is possible to have the VBA code print the file as an Adobe file, printing off every worksheet at once. Also, when printing the file as an adobe file (by selecting "Adobe PDF" as the printer, as opposed to selecting "print to file") is it possible to choose "Adobe PDF Document Properties" and change the "Default Settings" of the "Adobe PDF Settings" tab all using the VBA code?

(3)

My final issue seems to be documented well, but I cannot seem to get it to work. I am attempting to use the following code to move specific worksheets (the active one) to a NEW workbook (order doesn't matter as I can use code to sort them when they have all been added):

' copy chart to new workbook
    ActiveSheet.Move Before:=Workbooks("Book1.xlsx").Sheets(1)
Where Book1.xlsx is the default "new", yet uncreated and unopened workbook I wish to send the newly created worksheets to. Also, is it possible to just create all of my charts and then have them sent to the new workbook at once? Once again, the charts are not embedded and take their own worksheet up.

Thanks to everyone for their time and help.

I am looking for some help with creating a macro button in excel that would be used to print out a specific range of pdf documents via hyperlinks.

The macro steps would be as follows:

1. Open pdf file by selecting hyperlink
2. Print open file to specific printer
3. Close open pdf file
4. Repeat steps 1 to 3 for remaining specified files

I've tried recording the process in Excel but I am only able to get the 1st pdf file open (which I could have done by cliking on it!)

If anyone has any suggestions or tips then please get in touch. I can upload example files if required.

My knowledge of macros & VBA is very limited...

Regards,

Bryan


No luck finding an answer? You could always try Google.