VBA 一键打印功能(当前文件中的工作表)

时间：2017-03-06 17:31:47

Sub 一键打印()
    If MsgBox("确认要打印所有不含NO的工作表吗？", vbOKCancel, "温馨提示") = vbCancel Then
        End
    End If
    On Error Resume Next '这里最好加上，有时总报错。
    Dim Sht As Worksheet
    For Each Sht In ThisWorkbook.Worksheets
         Debug.Print Sht.Name
            Sht.PrintOut Copies:=1
'            Sleep 400
'            DoEvents
    Next
 
End Sub

添加打印机选择

Sub 一键打印()
    If MsgBox("确认要打印所有不含NO的工作表吗？", vbOKCancel, "温馨提示") = vbCancel Then
        End
    End If
     
     
    Application.Dialogs(9).Show                     '调用打印设置窗体
    Current = Application.ActivePrinter             '把打印设置窗体赋给变量,则取到的打印名中含有端口名,如（在 Ne01:)
     
    Debug.Print Current '默认
     
     
    Current = Mid(Current, 1, Len(Current) - 8)     '截去（在 Ne01:）
    SetDefaultPrinter Current                       '更改默认打印
     
    On Error Resume Next
    Dim Sht As Worksheet
    For Each Sht In ThisWorkbook.Worksheets
        If Sht.Name <> "基本信息" And Sht.Name <> "通信线缆" And Sht.Name <> "电力电缆" And Sht.Name <> "服务费明细" And (InStr(Sht.Name, "NO") = 0 And InStr(Sht.Name, "no") = 0) Then
            Debug.Print Sht.Name
            Sht.PrintOut Copies:=1
'            Sleep 400
'            DoEvents
        End If
    Next
     
     
    Sheets(Array("基本信息")).Select
    Application.StatusBar = "批量打印完成。"
End Sub
 
 
 
 
'返回本机打印机对象集合
Function getPrinters()
  '取得WMI服务
  Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
  '查找系统所有打印服务(这是集合)
  Set getPrinters = objWMI.ExecQuery("Select * from Win32_Printer")
End Function
 
'_______________________
'以字符名为依据设置默认打印机
'成功返回TRUE,失败返回FALSE
Function SetDefaultPrinter(strName)
    SetDefaultPrinter = False
    Set cols = getPrinters()
    For Each prnx In cols
        If UCase(prnx.Name) = UCase(strName) Then
          prnx.SetDefaultPrinter
          SetDefaultPrinter = True
          Exit Function
        End If
    Next
End Function