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 -
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
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
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
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