Changing the Printer in Excel VBA

**Kyle123** · 11-04-2011, 08:28 AM

If you've tried to change the printer in Excel VBA, no doubt you've realised that it needs the printer port in order to work, this looks something like "NE01:". As this changes from computer to computer it can't be hard coded.

Here's a solution that will get the printer port without looping over all possible versions until one works.

Please Login or Register  to view this content.

Below is an example for calling it:

Please Login or Register  to view this content.

Hope this helps someone

**snb** · 11-04-2011, 10:49 AM

Wouldn't this be sufficient ?

Please Login or Register  to view this content.

**Kyle123** · 11-04-2011, 11:03 AM

Unfortunately no, it won't work for network printers as the slashes in the printer name will be appended to the registry key

But if you can find a way to make it work, I'm all ears, much shorter

**snb** · 11-04-2011, 01:52 PM

Maybe this will do (in analogy to SQL strings)

Please Login or Register  to view this content.

**Kyle123** · 11-04-2011, 02:25 PM

Maybe and I'd love to try it but I've no network printers at home and I'm going to Mexico for 2 weeks tomorrow so I'm prying myself away from Excel for a bit

I'll just have to wait in suspense

**snb** · 11-04-2011, 04:40 PM

I don't think you have to.

I found a simple solution using Word' VBA-library (although you dislike Word, I like it's VBA-library).
It can be done simply

Please Login or Register  to view this content.

**romperstomper** · 11-07-2011, 05:37 AM

FWIW, I use:

Please Login or Register  to view this content.

**Kyle123** · 11-24-2011, 05:57 AM

Thanks both, they are good solutions. In terms of efficiency, my long code is the fastest, followed by rompers and then snb's. Though it's unlikely this will make much of a difference as generally the function is only called once.

@snb, I don't dislike word, I just dislike the time it takes to open on my PC

Thanks for your input