VBA: 多份Excel檔案的批量順序列印

文章背景：測試儀器的資料有時會以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）