Hi there,

I have a various workbooks that when I open I would like
to be assigned to a particular printer, is it possible to
write a macro for this and also to have the macro
automatically run when the file is opened?

Any help would be greatly appreciated
Thanks
Gordon

Gordon,

You can use VBA to change the name of the ActivePrinter.

Private Sub Workbook_Open()
Application ActivePrinter:="Canon Bubble-Jet BJC-4300 on LPT1:"
End Sub

the default printer remains the same. If you want to change this, check out
this article

http://support.microsoft.com/default...b;en-us;266767
HOWTO: Set Which Printer Is the System Default Printer

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Forbes" wrote in message
...
Hi there,

I have a various workbooks that when I open I would like
to be assigned to a particular printer, is it possible to
write a macro for this and also to have the macro
automatically run when the file is opened?

Any help would be greatly appreciated
Thanks
Gordon

Hi Bob,

thanks for the reply, I've tried the code you suggested
but it doesn't seem to change from my default printer to
the one I want to specify. Any further suggestions??

Thanks
Gordon


-----Original Message-----
Gordon,

You can use VBA to change the name of the ActivePrinter.

Private Sub Workbook_Open()
Application ActivePrinter:="Canon Bubble-Jet BJC-4300
on LPT1:"
End Sub

the default printer remains the same. If you want to
change this, check out
this article

http://support.microsoft.com/default.aspx?scid=kb;en-
us;266767
HOWTO: Set Which Printer Is the System Default Printer

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Forbes"
wrote in message
...
Hi there,

I have a various workbooks that when I open I would like
to be assigned to a particular printer, is it possible
to
write a macro for this and also to have the macro
automatically run when the file is opened?

Any help would be greatly appreciated
Thanks
Gordon


.

Gordon,

Sorry, I slipped in a syntax error. Try this instead

Private Sub Workbook_Open()
Application.ActivePrinter = "Canon Bubble-Jet BJC-4300 on LPT1:"
End Sub

Also, make sure that you get the printer name right. You can find it by
setting that printer, and then going into the immediate window in VBE and
typing ?Application.ActivePrinter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Forbes" wrote in message
...
Hi Bob,

thanks for the reply, I've tried the code you suggested
but it doesn't seem to change from my default printer to
the one I want to specify. Any further suggestions??

Thanks
Gordon


-----Original Message-----
Gordon,

You can use VBA to change the name of the ActivePrinter.

Private Sub Workbook_Open()
Application ActivePrinter:="Canon Bubble-Jet BJC-4300
on LPT1:"
End Sub

the default printer remains the same. If you want to
change this, check out
this article

http://support.microsoft.com/default.aspx?scid=kb;en-
us;266767
HOWTO: Set Which Printer Is the System Default Printer

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Forbes"
wrote in message
...
Hi there,

I have a various workbooks that when I open I would like
to be assigned to a particular printer, is it possible
to
write a macro for this and also to have the macro
automatically run when the file is opened?

Any help would be greatly appreciated
Thanks
Gordon


.

Thanks again for the prompt reply Bob.

The code now works fine although I have to step into it
and run it before the printer changes. Am I right in
thinking I need to do this all the time or should it
actually update when the file is opened? Or can I set up a
button to run this.

Many thnaks for all your help on this.
Regards
Gordon

-----Original Message-----
Gordon,

Sorry, I slipped in a syntax error. Try this instead

Private Sub Workbook_Open()
Application.ActivePrinter = "Canon Bubble-Jet BJC-4300
on LPT1:"
End Sub

Also, make sure that you get the printer name right. You
can find it by
setting that printer, and then going into the immediate
window in VBE and
typing ?Application.ActivePrinter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Forbes"
wrote in message
...
Hi Bob,

thanks for the reply, I've tried the code you suggested
but it doesn't seem to change from my default printer to
the one I want to specify. Any further suggestions??

Thanks
Gordon

Gordon,

If you put the code in the ThisWorkbook code module, it will automatically
run when you open the workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Forbes" wrote in message
...
Thanks again for the prompt reply Bob.

The code now works fine although I have to step into it
and run it before the printer changes. Am I right in
thinking I need to do this all the time or should it
actually update when the file is opened? Or can I set up a
button to run this.

Many thnaks for all your help on this.
Regards
Gordon

-----Original Message-----
Gordon,

Sorry, I slipped in a syntax error. Try this instead

Private Sub Workbook_Open()
Application.ActivePrinter = "Canon Bubble-Jet BJC-4300
on LPT1:"
End Sub

Also, make sure that you get the printer name right. You
can find it by
setting that printer, and then going into the immediate
window in VBE and
typing ?Application.ActivePrinter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gordon Forbes"
wrote in message
...
Hi Bob,

thanks for the reply, I've tried the code you suggested
but it doesn't seem to change from my default printer to
the one I want to specify. Any further suggestions??

Thanks
Gordon