Cách sao chép và dán nhiều bảng tính Excel vào Word thông qua VBA

Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn cách sao chép và dán nhiều bảng tính Excel vào Word thông qua VBA. Dưới đây là hình minh họa:

Chuỗi lệnh VBA sử dụng để lọc qua các bảng dữ liệu Excel

Option Base 1 ‘Force arrays to start at 1 instead of 0

Sub ExcelTablesToWord()

‘PURPOSE: Copy/Paste An Excel Table Into a New Word Document
‘NOTE: Must have Word Object Library Active in Order to Run _
  (VBE > Tools > References > Microsoft Word 12.0 Object Library)

Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table
Dim TableArray As Variant
Dim BookmarkArray As Variant

‘List of Table Names (To Copy)
  TableArray = Array(“Table1”, “Table2”, “Table3”, “Table4”, “Table5”)
  
‘List of Word Document Bookmarks (To Paste To)
  BookmarkArray = Array(“Bookmark1”, “Bookmark2”, “Bookmark3”, “Bookmark4”, “Bookmark5”)

‘Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False

‘Set Variable Equal To Destination Word Document
  On Error GoTo WordDocNotFound
    Set WordApp = GetObject(class:=”Word.Application”)
    WordApp.Visible = True
    Set myDoc = WordApp.Documents(“Excel Table Word Report.docx”)
  On Error GoTo 0
    
‘Loop Through and Copy/Paste Multiple Excel Tables
  For x = LBound(TableArray) To UBound(TableArray)

    ‘Copy Table Range from Excel
      Set tbl = ThisWorkbook.Worksheets(x).ListObjects(TableArray(x)).Range
      tbl.Copy
    
    ‘Paste Table into MS Word (using inserted Bookmarks -> ctrl+shift+F5)
      myDoc.Bookmarks(BookmarkArray(x)).Range.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=False
    
    ‘Autofit Table so it fits inside Word Document
      Set WordTable = myDoc.Tables(x)
      WordTable.AutoFitBehavior (wdAutoFitWindow)

  Next x

‘Completion Message
  MsgBox “Copy/Pasting Complete!”, vbInformation
  GoTo EndRoutine
  
‘ERROR HANDLER
WordDocNotFound:
  MsgBox “Microsoft Word file ‘Excel Table Word Report.docx’ is not currently open, aborting.”, 16

‘Put Stuff Back The Way It Was Found
EndRoutine:
‘Optimize Code
  Application.ScreenUpdating = True
  Application.EnableEvents = True

‘Clear The Clipboard
  Application.CutCopyMode = False

End Sub

Đánh dấu điểm cần dán nội dung trong một tệp tài liệu Word

Nếu bạn muốn sao chép và dán bất cứ thứ gì vào Word, cách tốt nhất là khởi động sẵn 1 cửa sổ soạn thảo mới trong đó đã chừa sẵn khoảng trống cố định trước. Bạn có thể làm được điều này thông qua chức năng Bookmarks có sẵn trong Word. Bằng cách này bạn hoàn toàn có thể sử dụng các lệnh VBA để truy cập điểm đến được đánh dấu trước trong Bookmarks, và sau đó có thể dán nội dung cần sao chép vào chính xác vị trí tài liệu mà bạn chọn sẵn. Bookmark có thể được cố định theo vị trí con trỏ soạn thảo hoặc vị trí một đoạn văn bản nào đó. Dưới đây là ví dụ khi sử dụng điểm đánh dấu theo vị trí con trỏ soạn thảo (nằm cách 2 dòng phía dưới đoạn tiêu đề bài viết)

Cách sử dụng và thêm Bookmark như sau:

• Đặt con trỏ soạn thảo vào vị trí mà bạn muốn đánh dấu
• Tìm đến thanh tác vụ Insert trên thanh công cụ Ribbon
• Trong mục Links, click chọn vào nút Bookmark
• Đặt tên cho điểm đánh dấu (lưu ý tên được có khoảng cách)
• Bấm nút “Add” nằm ở phía dưới hộp thoại

Sử dụng lệnh VBA để tham chiếu đến điểm đánh dấu trong Word

Việc tham chiếu đến điểm đánh dấu trong Word rất đơn giản. Nó tương tự như cách tham chiếu một dải ô được đặt tên trong Excel. Bạn có thể sử dụng câu lệnh dưới đây:

ActiveDocument.Bookmarks(“MyBookmarkName”).Select

Liệt kê những nội dung cần chỉnh sửa thêm

Một cách đơn giản để có thể lọc qua tất cả các dải ô, bảng biểu, đồ thị hay các ký tự khác trong Excel là thông qua việc liệt kê đầy đủ danh sách các “names” (tên gọi) của chúng. Trong ví dụ này, tên gọi của các bảng dữ liệu trong Excel sẽ được thêm vào tập hợp (hay còn gọi là danh sách). Còn tên gọi các Bookmark cũng sẽ được thêm vào một danh sách riêng khác. Chú ý rằng mỗi tên gọi của từng bảng đều được gắn với tên vị trí đánh dấu tương ứng mà lát nữa ta sẽ sao chép và dán vào. Việc này giúp cố định thứ tự các mục trong quá trình chạy lệnh.

Trong đây ta không đặt một danh sách riêng để lưu tên gọi của các bảng tính bởi vì vị trí của các bảng dữ liệu được gắn liền với từng bảng tính một. Tuy vậy nếu như có sự khác biệt về vị trí thứ tự của bảng tính và các bảng dữ liệu (chẳng hạn bạn chỉ muốn lọc các bảng tính thứ 1,3 và 7) thì bạn bắt buộc phải lưu tên gọi của bảng tính vào một danh sách riêng, theo cách tham chiếu tương tự với bảng dữ liệu và điểm đánh dấu.

‘List of Table Names (To Copy)
  TableArray = Array(“Table1”, “Table2”, “Table3”, “Table4”, “Table5”)
  
‘List of Word Document Bookmarks (To Paste To)
  BookmarkArray = Array(“Bookmark1”, “Bookmark2”, “Bookmark3”, “Bookmark4”, “Bookmark5”)