VBA: 多份Excel文件的批量顺序打印

Exploring

发布于 2022-09-20 14:28:53

2.4K0

发布于 2022-09-20 14:28:53

文章背景：测试仪器的数据有时会以Excel文件形式保存，工作量大时会选中多份文件进行批量打印。当office升级后，批量打印时可能会出现顺序错乱，这时需要手动排序，费事费力。现在以批量打印Excel文件（.xlsx格式）为例，采用VBA编程，进行任务的实现。

在批量打印文件的按钮中指定如下的宏命令代码：

Sub printFiles()

    '批量打印Excel文件
    
    Application.ScreenUpdating = False
    
    '获取默认路径
    ChDrive ThisWorkbook.Worksheets("Sheet1").Range("B2").Value
    ChDir ThisWorkbook.Worksheets("Sheet1").Range("B3").Value
    
    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
 
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    'Declare a variable to contain the path
    'of each selected item. Even though the path is aString,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
 
    'Use a With...End With block to reference the FileDialog object.
    With fd
    
        'Use the Show method to display the File Picker dialog box and return the user's action.
        
        'The user pressed the button.
        If .Show = -1 Then
 
            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems
 
                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                
                '如果是xlsx文件，则打印出来
                If Right(vrtSelectedItem, 5) = ".xlsx" Then
                
                    Workbooks.Open (vrtSelectedItem)
                    
                    '打印首张sheet,打印区域已提前设置好
                    ActiveWorkbook.Sheets(1).PrintOut
                    
                    ActiveWorkbook.Close False
                    
                End If
                
            Next vrtSelectedItem
            
        'The user pressed Cancel.
        Else
            
            Set fd = Nothing
        
            MsgBox "没有选择任何文件！"
            
            Application.ScreenUpdating = True
            
            Exit Sub
            
        End If
        
    End With
 
    'Set the object variable to Nothing.
    Set fd = Nothing
    
    MsgBox "打印结束！"
    
    Application.ScreenUpdating = True
    
    Exit Sub
    
End Sub

运行效果展示：http://mpvideo.qpic.cn/0bf2o4abmaaaj4aitbkuybqfa56dcz3qafqa.f10002.mp4?dis_k=dc14bc5b7d03765a5f967fd599a1c93c&dis_t=1663655305&vid=wxv_1787884989930340359&format_id=10002&support_redirect=0&mmversion=false

（1）由于笔者电脑上没有连接实体打印机，默认选择的是虚拟打印机（Adobe PDF）。因此，运行上述代码后，每打印一次，就会弹出对话框，选择 PDF 文档保存的位置和文件名。

（2）实际工作当中，如果连接了实体打印机，运行上述代码后会按顺序依次打印出你所选择的各个文件。

参考资料：

[1] 利用Excel VBA实现批量打印的思路（https://zhuanlan.zhihu.com/p/55800879）

[2] 你还在挨个打印word、excel文件吗（https://www.jianshu.com/p/7d62df832738）

[3] FileDialog object (Office)（https://docs.microsoft.com/en-us/office/vba/api/office.filedialog）

本文参与?腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-03-19，如有侵权请联系?cloudcommunity@tencent.com 删除

https