别再手动改打印机了！用VBA一键获取所有打印机名字和端口号（附完整代码）
用VBA实现打印机智能管理：从基础查询到自动化配置
每次在办公室需要打印文件时，你是否也经历过这样的困扰？面对十几台网络打印机，手动查找每台设备的名称和端口号简直是一场噩梦。更糟的是，当系统更新或网络调整后，原先配置好的打印机突然无法工作，只能重新开始漫长的排查过程。作为一位长期与Excel打交道的财务分析师，我曾经每周都要浪费至少两小时在这种重复劳动上，直到发现了VBA这个强大的自动化工具。
1. 为什么需要自动化管理打印机？
在日常办公环境中，打印机管理往往被忽视，却实际消耗着大量时间。根据一项针对500名办公室职员的调查，平均每人每月要花费3-5小时处理打印机相关问题。常见痛点包括：
- 设备识别困难：网络环境中存在多台同名或名称相似的打印机
- 端口号混乱：系统自动分配的端口号缺乏规律，难以记忆
- 配置易丢失：系统重装或更新后，所有自定义设置需要重新配置
- 批量操作繁琐：需要为不同部门或文件类型指定不同打印机时操作复杂
VBA提供的WScript.Network对象能够直接与Windows打印系统交互，获取最准确的设备信息。相比手动查看控制面板或注册表，这种方式不仅更快，还能将数据直接用于后续自动化流程。
2. 构建打印机信息查询系统
2.1 核心对象解析
WScript.Network是Windows脚本宿主提供的网络操作接口，通过它可以访问三类关键资源：
- 打印机连接：枚举所有已配置的打印机及其端口
- 网络驱动器：映射和管理网络共享文件夹
- 用户信息：获取当前登录用户的域和用户名
在VBA中使用前，需要创建对象实例：
Dim WshNetwork As Object Set WshNetwork = CreateObject("WScript.Network")2.2 完整打印机枚举方案
以下代码不仅能列出所有打印机，还会智能分类端口和名称，并标注默认打印机：
Sub GetAllPrinters() Dim WshNetwork As Object Dim oPrinters As Object Dim i As Long Dim result As String Set WshNetwork = CreateObject("WScript.Network") Set oPrinters = WshNetwork.EnumPrinterConnections result = "当前系统打印机列表：" & vbCrLf & vbCrLf For i = 0 To oPrinters.Count - 1 Step 2 result = result & "端口: " & oPrinters(i) & vbCrLf result = result & "名称: " & oPrinters(i + 1) & vbCrLf ' 检查是否为默认打印机 If oPrinters(i + 1) = WshNetwork.GetDefaultPrinter() Then result = result & "(系统默认打印机)" & vbCrLf End If result = result & "------------------------" & vbCrLf Next i ' 显示在立即窗口并弹出消息框 Debug.Print result MsgBox result, vbInformation, "打印机信息报告" End Sub执行后会得到如下格式的输出：
端口: Ne01: 名称: HP LaserJet Pro MFP M428fdw (系统默认打印机) ------------------------ 端口: Ne02: 名称: Canon imageCLASS LBP6230dw ------------------------2.3 数据存储与再利用
单纯显示信息还不够实用，我们可以将结果保存到Excel工作表，建立打印机数据库：
Sub SavePrintersToSheet() Dim ws As Worksheet Dim WshNetwork As Object Dim oPrinters As Object Dim i As Long Dim rowIndex As Integer ' 创建或选择"打印机列表"工作表 On Error Resume Next Set ws = ThisWorkbook.Sheets("打印机列表") On Error GoTo 0 If ws Is Nothing Then Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ws.Name = "打印机列表" ' 设置表头 ws.Range("A1:D1") = Array("序号", "端口号", "打印机名称", "是否默认") Else ' 清空旧数据（保留表头） ws.Range("A2:D1000").ClearContents End If Set WshNetwork = CreateObject("WScript.Network") Set oPrinters = WshNetwork.EnumPrinterConnections rowIndex = 2 For i = 0 To oPrinters.Count - 1 Step 2 ws.Cells(rowIndex, 1) = rowIndex - 1 ' 序号 ws.Cells(rowIndex, 2) = oPrinters(i) ' 端口 ws.Cells(rowIndex, 3) = oPrinters(i + 1) ' 名称 ' 检查默认状态 If oPrinters(i + 1) = WshNetwork.GetDefaultPrinter() Then ws.Cells(rowIndex, 4) = "是" Else ws.Cells(rowIndex, 4) = "否" End If rowIndex = rowIndex + 1 Next i ' 自动调整列宽和格式化 ws.Columns("A:D").AutoFit ws.Range("A1:D1").Font.Bold = True ws.ListObjects.Add(xlSrcRange, ws.Range("A1:D" & rowIndex - 1), , xlYes).Name = "PrinterTable" MsgBox "成功保存 " & (rowIndex - 2) & " 台打印机信息到工作表！", vbInformation End Sub提示：定期运行此代码可以建立打印机配置历史记录，便于追踪变化和排查问题
3. 高级打印机管理技巧
3.1 智能打印机选择器
基于收集的打印机信息，我们可以构建交互式选择界面：
Sub SmartPrinterSelector() Dim WshNetwork As Object Dim oPrinters As Object Dim printerList() As String Dim i As Long, choice As Long Dim defaultPrinter As String Set WshNetwork = CreateObject("WScript.Network") Set oPrinters = WshNetwork.EnumPrinterConnections defaultPrinter = WshNetwork.GetDefaultPrinter() ' 准备打印机列表数组 ReDim printerList(oPrinters.Count / 2 - 1) For i = 0 To oPrinters.Count - 1 Step 2 printerList(i / 2) = oPrinters(i + 1) & " (" & oPrinters(i) & ")" If oPrinters(i + 1) = defaultPrinter Then printerList(i / 2) = printerList(i / 2) & " [默认]" End If Next i ' 显示选择对话框 choice = Application.InputBox( _ Prompt:="请选择要使用的打印机：" & vbCrLf & vbCrLf & Join(printerList, vbCrLf), _ Title:="打印机选择器", _ Type:=1) ' 类型1表示数字输入 If choice > 0 And choice <= UBound(printerList) + 1 Then Application.ActivePrinter = printerList(choice - 1) MsgBox "已设置为使用：" & printerList(choice - 1), vbInformation Else MsgBox "选择已取消或无效", vbExclamation End If End Sub3.2 按条件自动选择打印机
结合工作场景实现智能切换，例如根据文档类型选择打印机：
Sub AutoSelectPrinterByContent() Dim currentSheet As Worksheet Dim colorPages As Integer Dim isFinancialReport As Boolean Dim desiredPrinter As String Set currentSheet = ActiveSheet ' 分析文档特征（简化示例） colorPages = Application.CountIf(currentSheet.UsedRange, "<>""""") ' 简单页数估算 isFinancialReport = InStr(1, currentSheet.Name, "财务报表") > 0 ' 根据规则选择打印机 If isFinancialReport Then desiredPrinter = "财务专用打印机" ElseIf colorPages > 10 Then desiredPrinter = "彩色高速打印机" Else desiredPrinter = "默认黑白打印机" End If ' 尝试设置打印机 On Error Resume Next Application.ActivePrinter = desiredPrinter If Err.Number = 0 Then Debug.Print "已自动选择打印机：" & desiredPrinter Else Debug.Print "指定的打印机不可用，保持当前设置" Err.Clear End If On Error GoTo 0 End Sub3.3 打印机状态监控与预警
通过定期检查确保关键打印机可用：
Sub CheckPrinterAvailability() Dim criticalPrinters As Variant Dim printer As Variant Dim WshNetwork As Object Dim oPrinters As Object Dim printerExists As Boolean Dim msg As String ' 定义需要监控的关键打印机列表 criticalPrinters = Array("财务专用打印机", "合同专用打印机", "彩色打印机") Set WshNetwork = CreateObject("WScript.Network") Set oPrinters = WshNetwork.EnumPrinterConnections msg = "打印机状态检查报告：" & vbCrLf & vbCrLf For Each printer In criticalPrinters printerExists = False ' 检查打印机是否存在 For i = 1 To oPrinters.Count - 1 Step 2 If oPrinters(i) = printer Then printerExists = True Exit For End If Next i If printerExists Then msg = msg & "✓ " & printer & " 可用" & vbCrLf Else msg = msg & "✗ " & printer & " 未连接！请检查网络或配置" & vbCrLf End If Next printer ' 添加默认打印机信息 msg = msg & vbCrLf & "当前默认打印机: " & WshNetwork.GetDefaultPrinter() ' 发送邮件提醒（可选） If InStr(msg, "✗") > 0 Then SendAlertEmail "打印机异常警报", msg End If ' 显示结果 MsgBox msg, vbInformation, "打印机状态检查" End Sub Private Sub SendAlertEmail(subject As String, body As String) ' 实际项目中实现邮件发送逻辑 Debug.Print "模拟发送警报邮件：" & vbCrLf & subject & vbCrLf & body End Sub4. 企业级打印解决方案设计
4.1 集中式打印配置管理
对于大型组织，可以建立中央控制的打印配置系统：
Sub ApplyDepartmentPrinterSettings() Dim userDepartment As String Dim settingsSheet As Worksheet Dim settingRange As Range Dim foundSetting As Range ' 获取用户部门信息（实际项目中可能从AD或其他系统获取） userDepartment = GetUserDepartment() ' 从配置表读取设置 Set settingsSheet = ThisWorkbook.Sheets("打印配置") Set settingRange = settingsSheet.Range("A2:D100") ' 假设配置存储在A:D列 ' 查找匹配的部门设置 Set foundSetting = settingRange.Find(What:=userDepartment, LookIn:=xlValues) If Not foundSetting Is Nothing Then ' 应用打印机设置 On Error Resume Next Application.ActivePrinter = foundSetting.Offset(0, 1).Value ' B列为打印机名称 If Err.Number = 0 Then ' 设置打印参数 With ActiveSheet.PageSetup .Orientation = IIf(foundSetting.Offset(0, 2).Value = "横向", xlLandscape, xlPortrait) .Zoom = foundSetting.Offset(0, 3).Value End With MsgBox "已应用" & userDepartment & "部门打印设置", vbInformation Else MsgBox "指定的打印机不可用", vbExclamation Err.Clear End If On Error GoTo 0 Else MsgBox "未找到" & userDepartment & "部门的打印配置", vbExclamation End If End Sub Private Function GetUserDepartment() As String ' 简化示例 - 实际项目中可能从AD或其他系统获取 GetUserDepartment = "财务部" ' 模拟返回值 End Function4.2 打印任务队列管理系统
对于高频打印环境，可以实现任务队列控制：
Sub PrintQueueManager() Dim printQueue As Collection Dim queueItem As Variant Dim printSettings As Variant Dim startTime As Double ' 初始化打印队列 Set printQueue = New Collection ' 添加打印任务到队列（实际项目中可能从数据库或其他来源加载） printQueue.Add Array("销售报表.xlsx", "Sales!A1:G20", "销售部打印机", 2) ' 文件名, 范围, 打印机, 份数 printQueue.Add Array("库存报告.xlsx", "Inventory!A1:D30", "仓库打印机", 1) ' 处理队列 startTime = Timer For Each queueItem In printQueue printSettings = queueItem ' 打开文档（简化示例，实际项目中使用Workbooks.Open） Debug.Print "正在打印: " & printSettings(0) ' 设置打印机 On Error Resume Next Application.ActivePrinter = printSettings(2) & " 在 Ne00:" If Err.Number <> 0 Then Debug.Print " 错误: 打印机" & printSettings(2) & "不可用" Err.Clear GoTo NextItem End If On Error GoTo 0 ' 模拟打印（实际项目中使用Worksheet.PrintOut） Debug.Print " 使用打印机: " & Application.ActivePrinter Debug.Print " 打印范围: " & printSettings(1) Debug.Print " 份数: " & printSettings(3) Debug.Print " 任务完成" NextItem: Next queueItem Debug.Print "所有打印任务完成，耗时 " & Format(Timer - startTime, "0.00") & " 秒" End Sub4.3 打印审计与成本控制
实现打印记录跟踪，用于资源管理和成本核算：
Sub TrackPrintJob(documentName As String, pages As Integer, printerName As String) Dim logSheet As Worksheet Dim newRow As Long ' 获取或创建日志工作表 On Error Resume Next Set logSheet = ThisWorkbook.Sheets("打印日志") On Error GoTo 0 If logSheet Is Nothing Then Set logSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) logSheet.Name = "打印日志" ' 设置表头 logSheet.Range("A1:E1") = Array("时间戳", "文档名称", "页数", "打印机", "用户名") End If ' 添加新记录 newRow = logSheet.Cells(logSheet.Rows.Count, 1).End(xlUp).Row + 1 logSheet.Cells(newRow, 1) = Now logSheet.Cells(newRow, 2) = documentName logSheet.Cells(newRow, 3) = pages logSheet.Cells(newRow, 4) = printerName logSheet.Cells(newRow, 5) = Environ("USERNAME") ' 自动保存日志（可选） ThisWorkbook.Save End Sub ' 示例调用（通常在打印前后执行） Sub PrintWithTracking() Dim docName As String Dim pageCount As Integer docName = "2023年度财务报告" pageCount = 45 ' 记录打印前 TrackPrintJob docName, pageCount, Application.ActivePrinter ' 执行打印（此处为模拟） Debug.Print "正在打印 " & docName ' 可以添加实际打印代码 ' ActiveSheet.PrintOut ' 记录打印完成 TrackPrintJob docName & " (完成)", pageCount, Application.ActivePrinter End Sub