Selecting a printer from VBA 1

[tstead]

I know how to change the active printer from within VBA, what I don't know how to do is to list all of the active printers and allow the user to select the one they want to print to.

If the printers never changed, one could hard code the exact path and name of the printer, but if printers are added or deleted, then the code would need to be changed each time which is not a good option.

Does anyone have any helpful hints of a way that I could read in the names of the active printers into an array? I can take care of the dialog boxes and everything else once I have an array of valid printers. The problem is generating that array!

Thanks,

Tim S.

 

[diamondjim]

I assume you have windows. It would seem that you
could simply call out the printer to LPT 1 or whatever
and then set the default printer to handle LPT 1.
Start Settings Printer Add a Printer and
then set it as the default.

 

[tstead]

Thank you for the reply, but apparently I may not have made my question clear.

I am writing a VBA application that will run in Excel (needs to work in both 97 and 2000). In this application, the user chooses various options and, depending on those options, certain portions of a particular worksheet are to be printed. In other words, the program will choose what range to print out depending on the options the user has chosen.

I have a button simply marked "print". WHen the user presses this button, the VBA code will output the pertinent portion to the default printer. What I want to be able to do is have the program scan the computer, determine what printers are attached, and generate a list that the user can select from in case they don't want to choose the default printer.

I do not want them to go to File|Print to change the active printer becuase that is too confusing for most of the people that will be using this program (scary, huh?).

To change the activeprinter, you need to use the "Application.ActivePrinter = XXX" where XXX is the EXACT name of the printer yo want to change to. I tried a for each loop to find all active printers - didn't work. I tried to manipulate the registry to get the list of active printers, but VBA only looks in "HKEY_USERS\DEFAULT\Software\VB and VBA program Settings" and can not look (at least I could not figure out a way) into other registry entries to get the values of certain keys.

Simply put, I need a way from within VBA for Excel to generate a list of all valid printers so that the user can choose which one he needs to print to.

Thanks

 

[Mala]

Dear Tim,
Try the following:
In the relevant portion of the VBA code, simply insert the line:

Application.Dialogs(xlDialogPrinterSetup).Show

It'll display a built-in dialog box which allows you to select the printer you wish to print to.
Of course, this command will have to be used independently of any other custom dialog box ypu may be using - u can place this command either before or after that.
Mala

 

[tstead]

Thank you, that worked just great!

Regards,
Tim S.