VBA Excel Print with Default Printer

Solved
Mrfrize Posted messages 197 Registration date   Status Member Last intervention   -  
Mrfrize Posted messages 197 Registration date   Status Member Last intervention   -
Hello,

I've been trying for a day to print using the default printer instead of the last one selected. I found this code, but I'm running into an issue with GetProfileString, as I get a compile error saying: Compile error: Sub or Function not defined.

If anyone has any idea why this isn't working or an alternative method, I am open to any solution.

Thank you in advance

Sub PrintDefault()

ActivePrinter = WindowsPrinter
ActiveSheet.PrintOut

End Sub

Private Function WindowsPrinter()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim s1 As String
Dim r As Long
Dim Buffer As String
Dim PrinterName As String
Dim Spool As String
Dim Port As String
Buffer = Space(8192)
r = GetProfileString("Windows", "Device", "", Buffer, Len(Buffer))
Debug.Print Buffer; i = InStr(Buffer, Chr(0))
If i > 0 Then
s1 = Left(Buffer, i - 1)
j = InStr(s1, ",")
k = InStr(j + 1, s1, ",")
PrinterName = Left(s1, j - 1)
If k > j Then
Spool = Mid(s1, j + 1, k - j - 1)
Port = Mid(s1, k + 1)
End If
Else

' No default printer?
PrinterName = ""
End If
WindowsPrinter = PrinterName & " on " & Port
End Function

Configuration: Windows XP / Firefox 9.0.1

1 answer

Mrfrize Posted messages 197 Registration date   Status Member Last intervention   6
 
Here is the solution I found.

Sub Macro1()

Dim oShell As Object
Dim sRegval, sdefault

Set oShell = CreateObject("WScript.Shell")
sRegval = "HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows\Device"
sdefault = ""
sdefault = oShell.RegRead(sRegval)
sdefault = Left(sdefault, InStr(sdefault, ",") - 1)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
sdefault, Collate:=True

End Sub
1