set Excel ActivePrinter from Access

[JunglesMcBeef]

G'day,

Does anyone know how to set the ActivePrinter from Access? I am trying to print a few sheets in Excel but I am not sure how to change the ActivePrinter property. Please help, there is no other way around this so I need know if there is a way or not. If you can tell me it can't be done I will be happy, at least that will stop me from tearing my hair out trying to work it out! I am using Office 2000 and Win2k Prof.

 

[BrainB]

FWIW this works in Excel 97 but NOT Access 97 :_

'------------------------------------------------
Sub SET_PRINTER()
Dim MyPrinter As String
MyPrinter = "\\CTLU-LU0-PR01\MainPrinter on Ne02:"
Application.ActivePrinter = MyPrinter
End Sub
'-------------------------------------------------


Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[JunglesMcBeef]

Thanks for the reply, but the problem was that I wasn't referencing the Excel object library and using specific declarations instead of what I was doing, which was declaring the Excel objects as just Objects. All sorted now, ta.

 

[ploeryan]

I use the following code:

to call simply use getdefaultprinter


Private Declare Function GetProfileString Lib "kernel32" _
Alias "GetProfileStringA" _
(ByVal lpAppName As String, _
ByVal lpKeyName As String, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long

Function GetDefaultPrinter() As String
Dim strDefault As String
Dim lngbuf As Long

strDefault = String(255, Chr(0))
lngbuf = GetProfileString("Windows", "Device", "", strDefault, Len(strDefault))

If lngbuf > 0 Then
GetDefaultPrinter = fstrDField(strDefault, ",", 1)
Else
GetDefaultPrinter = ""
End If

End Function

Private Function fstrDField(mytext As String, delim As String, groupnum As Integer) As String

Dim startpos As Integer, endpos As Integer
Dim groupptr As Integer, chptr As Integer

chptr = 1
startpos = 0
For groupptr = 1 To groupnum - 1
chptr = InStr(chptr, mytext, delim)
If chptr = 0 Then
fstrDField = ""
Exit Function
Else
chptr = chptr + 1
End If
Next groupptr
startpos = chptr
endpos = InStr(startpos + 1, mytext, delim)
If endpos = 0 Then
endpos = Len(mytext) + 1
End If

fstrDField = Mid$(mytext, startpos, endpos - startpos)

End Function

Hope this Helps