Minor Problem with Macro

Hello,

I have a macro that works. The issue I am having is it changes my default
printer. In the macro, I call upon the Microsoft Document driver to speed up
the macro. By doing this, it changes the printer for Excel to this Document
Driver. What code can I use to call upon the previous printer (what ever the
user's default printer is) so that when they print through Excel it will be
back to the default printer. Here is my code (condensed for easier reading):

Thanks in advance.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Addon()
'
' Addon Macro
' Addon recorded 2/6/2004 by bhodge
'

'
Application.EnableEvents = False
On Error Resume Next
Application.ActivePrinter = "Microsoft Office Document Image Writer on
Ne02:"
If Err.Number = 1004 Then
Application.ActivePrinter = "Microsoft Office Document Image Writer on Ne01:"
Err.Clear
End If
Dim AWB As Workbook
Set AWB = ActiveWorkbook
Application.ScreenUpdating = False
AWB.Activate
Workbooks.Open Filename:= _
"G:\Contract QuoteTemplates\Email Template Macro3l.xls"
AWB.Activate
Windows("Email Template Macro3l.xls").Activate
Sheets("Features").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Move after:=Sheets(2)
Sheets("Sheet1").Name = "Terms and Conditions"
AWB.Activate
Sheets("Terms and Conditions").Select
Cells.Select
Selection.Copy
Application.ScreenUpdating = True
Range("a25").Select
Application.EnableEvents = True
RemoveAllCode
End With
End Sub

Sub Addon()
'
' Addon Macro
' Addon recorded 2/6/2004 by bhodge
'

'
Dim sStr as String
sStr = Application.ActivePrinter
Application.EnableEvents = False
On Error Resume Next
Application.ActivePrinter = "Microsoft Office Document Image Writer on
Ne02:"
If Err.Number = 1004 Then
Application.ActivePrinter = "Microsoft Office Document Image Writer on
Ne01:"
Err.Clear
End If
Dim AWB As Workbook
Set AWB = ActiveWorkbook
Application.ScreenUpdating = False
AWB.Activate
Workbooks.Open Filename:= _
"G:\Contract QuoteTemplates\Email Template Macro3l.xls"
AWB.Activate
Windows("Email Template Macro3l.xls").Activate
Sheets("Features").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Move after:=Sheets(2)
Sheets("Sheet1").Name = "Terms and Conditions"
AWB.Activate
Sheets("Terms and Conditions").Select
Cells.Select
Selection.Copy
Application.ScreenUpdating = True
Range("a25").Select
Application.EnableEvents = True
RemoveAllCode
End With
' NOW change it back
Application.ActiveWorkbook = sStr
End Sub

--
Regards,
Tom Ogilvy


"Brad" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a macro that works. The issue I am having is it changes my default
> printer. In the macro, I call upon the Microsoft Document driver to speed
up
> the macro. By doing this, it changes the printer for Excel to this
Document
> Driver. What code can I use to call upon the previous printer (what ever
the
> user's default printer is) so that when they print through Excel it will
be
> back to the default printer. Here is my code (condensed for easier
reading):
>
> Thanks in advance.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Sub Addon()
> '
> ' Addon Macro
> ' Addon recorded 2/6/2004 by bhodge
> '
>
> '
> Application.EnableEvents = False
> On Error Resume Next
> Application.ActivePrinter = "Microsoft Office Document Image Writer on
> Ne02:"
> If Err.Number = 1004 Then
> Application.ActivePrinter = "Microsoft Office Document Image Writer on
Ne01:"
> Err.Clear
> End If
> Dim AWB As Workbook
> Set AWB = ActiveWorkbook
> Application.ScreenUpdating = False
> AWB.Activate
> Workbooks.Open Filename:= _
> "G:\Contract QuoteTemplates\Email Template Macro3l.xls"
> AWB.Activate
> Windows("Email Template Macro3l.xls").Activate
> Sheets("Features").Select
> Sheets.Add
> Sheets("Sheet1").Select
> Sheets("Sheet1").Move after:=Sheets(2)
> Sheets("Sheet1").Name = "Terms and Conditions"
> AWB.Activate
> Sheets("Terms and Conditions").Select
> Cells.Select
> Selection.Copy
> Application.ScreenUpdating = True
> Range("a25").Select
> Application.EnableEvents = True
> RemoveAllCode
> End With
> End Sub
>
>
>

Tom, thanks for replying, however it still isn't working. I stepped into the
macro with your code and had the Printer Panel up while stepping in. It looks
like the macro never changes the OS default printer, it is only changing
Excel's Default Printer. Any ideas? Thanks.

"Tom Ogilvy" wrote:

> Sub Addon()
> '
> ' Addon Macro
> ' Addon recorded 2/6/2004 by bhodge
> '
>
> '
> Dim sStr as String
> sStr = Application.ActivePrinter
> Application.EnableEvents = False
> On Error Resume Next
> Application.ActivePrinter = "Microsoft Office Document Image Writer on
> Ne02:"
> If Err.Number = 1004 Then
> Application.ActivePrinter = "Microsoft Office Document Image Writer on
> Ne01:"
> Err.Clear
> End If
> Dim AWB As Workbook
> Set AWB = ActiveWorkbook
> Application.ScreenUpdating = False
> AWB.Activate
> Workbooks.Open Filename:= _
> "G:\Contract QuoteTemplates\Email Template Macro3l.xls"
> AWB.Activate
> Windows("Email Template Macro3l.xls").Activate
> Sheets("Features").Select
> Sheets.Add
> Sheets("Sheet1").Select
> Sheets("Sheet1").Move after:=Sheets(2)
> Sheets("Sheet1").Name = "Terms and Conditions"
> AWB.Activate
> Sheets("Terms and Conditions").Select
> Cells.Select
> Selection.Copy
> Application.ScreenUpdating = True
> Range("a25").Select
> Application.EnableEvents = True
> RemoveAllCode
> End With
> ' NOW change it back
> Application.ActiveWorkbook = sStr
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brad" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have a macro that works. The issue I am having is it changes my default
> > printer. In the macro, I call upon the Microsoft Document driver to speed
> up
> > the macro. By doing this, it changes the printer for Excel to this
> Document
> > Driver. What code can I use to call upon the previous printer (what ever
> the
> > user's default printer is) so that when they print through Excel it will
> be
> > back to the default printer. Here is my code (condensed for easier
> reading):
> >
> > Thanks in advance.
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > Sub Addon()
> > '
> > ' Addon Macro
> > ' Addon recorded 2/6/2004 by bhodge
> > '
> >
> > '
> > Application.EnableEvents = False
> > On Error Resume Next
> > Application.ActivePrinter = "Microsoft Office Document Image Writer on
> > Ne02:"
> > If Err.Number = 1004 Then
> > Application.ActivePrinter = "Microsoft Office Document Image Writer on
> Ne01:"
> > Err.Clear
> > End If
> > Dim AWB As Workbook
> > Set AWB = ActiveWorkbook
> > Application.ScreenUpdating = False
> > AWB.Activate
> > Workbooks.Open Filename:= _
> > "G:\Contract QuoteTemplates\Email Template Macro3l.xls"
> > AWB.Activate
> > Windows("Email Template Macro3l.xls").Activate
> > Sheets("Features").Select
> > Sheets.Add
> > Sheets("Sheet1").Select
> > Sheets("Sheet1").Move after:=Sheets(2)
> > Sheets("Sheet1").Name = "Terms and Conditions"
> > AWB.Activate
> > Sheets("Terms and Conditions").Select
> > Cells.Select
> > Selection.Copy
> > Application.ScreenUpdating = True
> > Range("a25").Select
> > Application.EnableEvents = True
> > RemoveAllCode
> > End With
> > End Sub
> >
> >
> >
>
>
>

Sorry, didn't recognize what you are asking:

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

--
Regards,
Tom Ogilvy

"Brad" <[email protected]> wrote in message
news:[email protected]...
> Tom, thanks for replying, however it still isn't working. I stepped into
the
> macro with your code and had the Printer Panel up while stepping in. It
looks
> like the macro never changes the OS default printer, it is only changing
> Excel's Default Printer. Any ideas? Thanks.
>
> "Tom Ogilvy" wrote:
>
> > Sub Addon()
> > '
> > ' Addon Macro
> > ' Addon recorded 2/6/2004 by bhodge
> > '
> >
> > '
> > Dim sStr as String
> > sStr = Application.ActivePrinter
> > Application.EnableEvents = False
> > On Error Resume Next
> > Application.ActivePrinter = "Microsoft Office Document Image Writer
on
> > Ne02:"
> > If Err.Number = 1004 Then
> > Application.ActivePrinter = "Microsoft Office Document Image Writer on
> > Ne01:"
> > Err.Clear
> > End If
> > Dim AWB As Workbook
> > Set AWB = ActiveWorkbook
> > Application.ScreenUpdating = False
> > AWB.Activate
> > Workbooks.Open Filename:= _
> > "G:\Contract QuoteTemplates\Email Template Macro3l.xls"
> > AWB.Activate
> > Windows("Email Template Macro3l.xls").Activate
> > Sheets("Features").Select
> > Sheets.Add
> > Sheets("Sheet1").Select
> > Sheets("Sheet1").Move after:=Sheets(2)
> > Sheets("Sheet1").Name = "Terms and Conditions"
> > AWB.Activate
> > Sheets("Terms and Conditions").Select
> > Cells.Select
> > Selection.Copy
> > Application.ScreenUpdating = True
> > Range("a25").Select
> > Application.EnableEvents = True
> > RemoveAllCode
> > End With
> > ' NOW change it back
> > Application.ActiveWorkbook = sStr
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Brad" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > >
> > > I have a macro that works. The issue I am having is it changes my
default
> > > printer. In the macro, I call upon the Microsoft Document driver to
speed
> > up
> > > the macro. By doing this, it changes the printer for Excel to this
> > Document
> > > Driver. What code can I use to call upon the previous printer (what
ever
> > the
> > > user's default printer is) so that when they print through Excel it
will
> > be
> > > back to the default printer. Here is my code (condensed for easier
> > reading):
> > >
> > > Thanks in advance.
> > >
> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > > Sub Addon()
> > > '
> > > ' Addon Macro
> > > ' Addon recorded 2/6/2004 by bhodge
> > > '
> > >
> > > '
> > > Application.EnableEvents = False
> > > On Error Resume Next
> > > Application.ActivePrinter = "Microsoft Office Document Image
Writer on
> > > Ne02:"
> > > If Err.Number = 1004 Then
> > > Application.ActivePrinter = "Microsoft Office Document Image Writer on
> > Ne01:"
> > > Err.Clear
> > > End If
> > > Dim AWB As Workbook
> > > Set AWB = ActiveWorkbook
> > > Application.ScreenUpdating = False
> > > AWB.Activate
> > > Workbooks.Open Filename:= _
> > > "G:\Contract QuoteTemplates\Email Template Macro3l.xls"
> > > AWB.Activate
> > > Windows("Email Template Macro3l.xls").Activate
> > > Sheets("Features").Select
> > > Sheets.Add
> > > Sheets("Sheet1").Select
> > > Sheets("Sheet1").Move after:=Sheets(2)
> > > Sheets("Sheet1").Name = "Terms and Conditions"
> > > AWB.Activate
> > > Sheets("Terms and Conditions").Select
> > > Cells.Select
> > > Selection.Copy
> > > Application.ScreenUpdating = True
> > > Range("a25").Select
> > > Application.EnableEvents = True
> > > RemoveAllCode
> > > End With
> > > End Sub
> > >
> > >
> > >
> >
> >
> >

Hi Brad

Try this

Dim DPrinter As String
DPrinter = Application.ActivePrinter
Application.ActivePrinter = "Microsoft Office Document Image Writer on Ne01:"

'print code

Application.ActivePrinter = DPrinter


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Brad" <[email protected]> wrote in message news:[email protected]...
> Hello,
>
> I have a macro that works. The issue I am having is it changes my default
> printer. In the macro, I call upon the Microsoft Document driver to speed up
> the macro. By doing this, it changes the printer for Excel to this Document
> Driver. What code can I use to call upon the previous printer (what ever the
> user's default printer is) so that when they print through Excel it will be
> back to the default printer. Here is my code (condensed for easier reading):
>
> Thanks in advance.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Sub Addon()
> '
> ' Addon Macro
> ' Addon recorded 2/6/2004 by bhodge
> '
>
> '
> Application.EnableEvents = False
> On Error Resume Next
> Application.ActivePrinter = "Microsoft Office Document Image Writer on
> Ne02:"
> If Err.Number = 1004 Then
> Application.ActivePrinter = "Microsoft Office Document Image Writer on Ne01:"
> Err.Clear
> End If
> Dim AWB As Workbook
> Set AWB = ActiveWorkbook
> Application.ScreenUpdating = False
> AWB.Activate
> Workbooks.Open Filename:= _
> "G:\Contract QuoteTemplates\Email Template Macro3l.xls"
> AWB.Activate
> Windows("Email Template Macro3l.xls").Activate
> Sheets("Features").Select
> Sheets.Add
> Sheets("Sheet1").Select
> Sheets("Sheet1").Move after:=Sheets(2)
> Sheets("Sheet1").Name = "Terms and Conditions"
> AWB.Activate
> Sheets("Terms and Conditions").Select
> Cells.Select
> Selection.Copy
> Application.ScreenUpdating = True
> Range("a25").Select
> Application.EnableEvents = True
> RemoveAllCode
> End With
> End Sub
>
>
>