[Chia sẻ] Dùng VBA trộn dữ liệu Excel sang file Word mẫu (tương tự chức năng Mail Merge)

[Maika8008]

Những ai có nhu cầu và đã từng sử dụng chức năng Mail Merge của MS Word dùng dữ liệu trên bảng tính Excel đều biết nó hoạt động như thế nào. Không ít trong số đó từng gặp những rắc rối về định dạng số, ngày tháng khi trộn, hoặc khi chuyển file hoạt động tốt từ máy này sang máy khác lại sinh ra khó bảo.

Với việc dùng VBA để trộn thì:
ƯU ĐIỂM:
- Dễ làm ngay cả với người mới. Thú thật khi mới dùng Mail Merge trong mấy lần đầu tiên tôi rất lúng túng, không biết phải làm thế nào để nạp dữ liệu, chèn trường
- Không có các lỗi: định dạng number không như ý muốn, bên Excel 1 đường nhưng sang Word lại 1 nẻo dù đã chỉnh sửa định dạng của Merge Fields , đảo ngày sang tháng.
- Không phát sinh lỗi khi chuyển sang máy tính khác (trừ việc cần thêm thư viện Microsoft Word tại cửa sổ VBA)
NHƯỢC ĐIỂM:
- Chậm. Tốc độ anh này nếu so với Mail Merge cũng như so đi xe đạp với xe máy

Ứng dụng sở trường của nó không phải ở việc trộn và in hàng loạt như giấy mời, mà là ở việc trộn mỗi lần cho nhiều file mẫu khác nhau nhưng trong các file ấy dùng lặp đi lặp lại 1 số thông tin thay đổi. Ví dụ khi bạn có một bộ văn bản cho 1 công việc gồm: Hợp đồng, phụ lục, thông báo, giấy mời, quy chế, nghiệm thu, thanh lý... mỗi lần dùng cho 1 đối tác, thì bạn rất dễ nhập sai thông tin đối tác, trích yếu công việc, ngày giờ tiến hành, địa điểm... hoặc khi sửa lại từ đối tác này dùng cho đối tác khác rất dễ bị râu ông nọ cắm cằm bà kia.

CÁCH DÙNG:
- Chuẩn bị file Word mẫu (ở trạng thái đóng). Tôi đã chuẩn bị sẵn 1 file mẫu ví dụ kèm theo.
- Chuẩn bị bảng dữ liệu như file Excel đính kèm. Nhập đường dẫn thư mục file Word mẫu tại ô H2 (đường dẫn này chỉ dùng khi bạn muốn trộn toàn bộ file Word trong thư mục nhưng lại không muốn hiện hộp thoại để lựa thư mục - Nếu ô H2 trống thì hộp thoại lựa thư mục sẽ xuất hiện khi chạy code)
- Bấm nút Gửi Field sang file Word mẫu. Từ các trường nhận được ở cuối file Word mẫu (tên trường bắt đầu bằng dấu $), bạn chép chúng đến các vị trí mong muốn và định dạng theo ý thích. Chép xong xóa các trường cuối file đi, lưu, đóng lại.
- Bấm Trộn để tiến hành. Các file đã trộn được đặt tên theo tên đối tác, lưu cùng đường dẫn với file Word mẫu.

CẬP NHẬT 29/07/2021: Sửa code để tăng tốc độ khi trộn hàng loạt toàn bộ dòng
CẬP NHẬT 07/12/2021: Giữ số 0 đầu số chứng minh nhân dân hoặc số điện thoại
CẬP NHẬT 17/05/2022: Trộn thông tin 1 người vào nhiều mẫu .docx khác nhau bằng hộp thoại chọn file. Văn bản đã trộn được lưu ở thư mục con có tên của người trộn. Nếu thư mục chưa tồn tại thì tự động được tạo mới. Tải file đính kèm tại bài #97
CẬP NHẬT 12/06/2022: Làm gọn code và dùng userform để chọn tham số đáp ứng tất cả yêu cầu của các thành viên từ trước đến nay trong chủ đề.
CẬP NHẬT 14/06/2022: Ô chứa text >255 ký tự vẫn trộn đầy đủ thông tin và không thi hành với các dòng ẩn.
CẬP NHẬT 18/06/2022: Tăng tốc với trường hợp trộn nhiều dòng nhiều văn bản và sửa lỗi lấy sai vùng làm việc với trường hợp trộn tất cả file Word trong folder.
CẬP NHẬT 03/03/2023: Thêm chức năng chuyển sang trộn theo kiểu Mail Merge truyền thống với file Word mẫu có sẵn các trường theo cách bên trên.
CẬP NHẬT 29/06/2023: Tách riêng mỗi thủ tục cho 1 tùy chọn để dễ bảo trì code. Sửa lỗi không mở được đường dẫn mặc định.
CẬP NHẬT 07/08/2023: Thêm thao tác chèn bảng Excel vào Word. Thêm các cột $Table1$, $Table2$ ... vào bảng dữ liệu trộn và bố trí bảng cần chèn kèm trên trên đầu bảng như file đính kèm.
CẬP NHẬT 08/08/2023: Tạo bảng Word rồi chép dữ liệu của bảng Excel vào bảng Word. Bảng Word giữ được màu font chữ của trường giữ chỗ.
CẬP NHẬT 13/08/2023: Hoàn thiện code, thêm chức năng ghép file, và gộp các công việc chung 1 file
CẬP NHẬT 15/08/2023: Định dạng phân cách hàng nghìn cho bảng Word dựa vào định dạng các cột của bảng Excel.
CẬP NHẬT 30/09/2023: Mở luôn file Word kết quả khi trộn 1 dòng 1 file. Có thể chép 1 bảng vào nhiều vị trí khác nhau của file kết quả (như yêu cầu tại bài #432).

 

[Maika8008]

Vâng, vậy là nếu muốn dùng cho nhiều file word thì phải đổi tên file word và đường dẫn cho file word đó đúng không anh?
Anh có hướng dẫn "Có thể đặt nhiều bảng trong sheet hiện hành, mỗi bảng trộn cho 1 file Word"
Có phải là mỗi bảng đó sẽ dùng lựa chọn "trộn 1 dòng" để trộn sang file cần lấy dữ liệu phải không anh?

Có 2 nút trộn toàn bộ và trộn 1 dòng đó, tùy bạn chọn. Nút nào cũng dùng cái bảng hiện hành để trộn nhưng khác là kết quả ra 1 hay là tất cả dòng trong bảng

 

[QuangMinhtb]

Có 2 nút trộn toàn bộ và trộn 1 dòng đó, tùy bạn chọn. Nút nào cũng dùng cái bảng hiện hành để trộn nhưng khác là kết quả ra 1 hay là tất cả dòng trong bảng

Anh cho em hỏi "nhiều bảng trong sheet hiện hành" là nhiều dữ liệu trộn (nhiều dòng trộn) trong một sheet?

 

[Maika8008]

Anh cho em hỏi "nhiều bảng trong sheet hiện hành" là nhiều dữ liệu trộn (nhiều dòng trộn) trong một sheet?

Đừng quan tâm đến sheet. File tôi có 1 bảng, bạn chạy thử từng chức năng rồi hình dung.

 

[QuangMinhtb]

Đừng quan tâm đến sheet. File tôi có 1 bảng, bạn chạy thử từng chức năng rồi hình dung.

Vâng em cảm ơn anh đã hướng dẫn!

 

[Maika8008]

Vâng em cảm ơn anh đã hướng dẫn!

Hiện giờ, nếu chạy trộn toàn bộ thì nó sẽ trộn bảng đầu tiên đang có đó. Chạy 1 dòng thì sẽ cho chọn dòng nào của bảng đó (và nếu có bảng khác thì sẽ lấy bảng có ô chọn đó)

Tôi sẽ sửa lại thành cả 2 chức năng đều có chức năng chọn bảng.

Cập nhật: file đã sửa lại

 

[QuangMinhtb]

Hiện giờ, nếu chạy trộn toàn bộ thì nó sẽ trộn bảng đầu tiên đang có đó. Chạy 1 dòng thì sẽ cho chọn dòng nào của bảng đó (và nếu có bảng khác thì sẽ lấy bảng có ô chọn đó)

Tôi sẽ sửa lại thành cả 2 chức năng đều có chức năng chọn bảng.

Cập nhật: file đã sửa lại

Vâng, rất cảm ơn anh!

 

[QuangMinhtb]

Hiện giờ, nếu chạy trộn toàn bộ thì nó sẽ trộn bảng đầu tiên đang có đó. Chạy 1 dòng thì sẽ cho chọn dòng nào của bảng đó (và nếu có bảng khác thì sẽ lấy bảng có ô chọn đó)

Tôi sẽ sửa lại thành cả 2 chức năng đều có chức năng chọn bảng.

Cập nhật: file đã sửa lại

Anh ơi! em xin có ý kiến và mong anh giúp:
Anh có thể bổ sung giúp em cột A là tên biên bản mới được tạo ra được không ạ?
Cho dễ quản lý, thay vì lấy tên biên bản ở cột G.
Các loại biên bản do mình tự đặt và khi nhìn vào có thể biết được nó là loại biên bản gì.
Xin cảm ơn anh.

 

[Maika8008]

Anh ơi! em xin có ý kiến và mong anh giúp:
Anh có thể bổ sung giúp em cột A là tên biên bản mới được tạo ra được không ạ?
Cho dễ quản lý, thay vì lấy tên biên bản ở cột G.
Các loại biên bản do mình tự đặt và khi nhìn vào có thể biết được nó là loại biên bản gì.
Xin cảm ơn anh.
View attachment 260340

Nếu bạn chỉ chèn từ A4:A6 thì ít gây ra vấn đề gì (chỉ tên file tạo ra bị dời đi 1 cột) nhưng bạn chèn nguyên cả cột A thì địa chỉ lấy đường dẫn và tên file sai hết rồi vì sai cơ bản. Sau này nếu muốn tùy biến nhớ là điền thông tin từ cột sau cột cuối cùng để không xảy ra tình trạng bứt dây động rừng.

 

[QuangMinhtb]

Nếu bạn chỉ chèn từ A4:A6 thì ít gây ra vấn đề gì (chỉ tên file tạo ra bị dời đi 1 cột) nhưng bạn chèn nguyên cả cột A thì địa chỉ lấy đường dẫn và tên file sai hết rồi vì sai cơ bản. Sau này nếu muốn tùy biến nhớ là điền thông tin từ cột sau cột cuối cùng để không xảy ra tình trạng bứt dây động rừng.

Em cảm ơn anh đã giúp!

 

[QuangMinhtb]

Nếu bạn chỉ chèn từ A4:A6 thì ít gây ra vấn đề gì (chỉ tên file tạo ra bị dời đi 1 cột) nhưng bạn chèn nguyên cả cột A thì địa chỉ lấy đường dẫn và tên file sai hết rồi vì sai cơ bản. Sau này nếu muốn tùy biến nhớ là điền thông tin từ cột sau cột cuối cùng để không xảy ra tình trạng bứt dây động rừng.

Vậy cột A như em đã nêu ở #27 nên chuyển về vị trí liền ngay sau cột cuối cùng thì tốt hơn ạ?
Em chèn thêm một bảng excel sử dụng file #28 anh giúp:
- Nếu để nguyên cột A sẽ chèn dữ liệu thiếu:
Chỉ chèn được $OK mà không có chèn $KKKKKK.
- Nếu xóa cột A đi sẽ báo lỗi (Như hình)
Để quản lý các file mới được tạo ra, có thể di chuyển các file mới đó vào một Foder mới nằm trong Foder chứa file word đó được không anh?
Hy, lúc ứng dụng có nhiều vấn đề mà lúc đầu em chưa định hướng được, nên làm phiền anh.

 

[Maika8008]

Vậy cột A như em đã nêu ở #27 nên chuyển về vị trí liền ngay sau cột cuối cùng thì tốt hơn ạ?
Em chèn thêm một bảng excel sử dụng file #28 anh giúp:
- Nếu để nguyên cột A sẽ chèn dữ liệu thiếu:
Chỉ chèn được $OK mà không có chèn $KKKKKK.
- Nếu xóa cột A đi sẽ báo lỗi (Như hình)
Để quản lý các file mới được tạo ra, có thể di chuyển các file mới đó vào một Foder mới nằm trong Foder chứa file word đó được không anh?
Hy, lúc ứng dụng có nhiều vấn đề mà lúc đầu em chưa định hướng được, nên làm phiền anh.
View attachment 260366

Đưa file bạn lên đây đi!

 

[QuangMinhtb]

Đưa file bạn lên đây đi!

Em điền bảng dữ liệu hàng 8 trong excel vào file word "Cong van chap thuan.docx"
thì kết quá như hình.
Khi ấn hủy bỏ để thực hiện code trên giao diện thì luôn báo lỗi chứ không hủy bỏ được luôn (hình thứ 3)


Anh xem giúp em!

 

[Maika8008]

Đừng nói các chuyện lớn lao, việc đọc kỹ hướng dẫn sử dụng trước khi dùng mà chưa làm được thì tệ quá.
=> Trong công văn đã chèn cái trường nào đâu mà trộn? Cái này gọi là chưa đi đã chạy.

 

[QuangMinhtb]

Đừng nói các chuyện lớn lao, việc đọc kỹ hướng dẫn sử dụng trước khi dùng mà chưa làm được thì tệ quá.
=> Trong công văn đã chèn cái trường nào đâu mà trộn? Cái này gọi là chưa đi đã chạy.

Ý em là chỉ lấy được một trường $OK (Ô B8 từ excel sang Word) không có trường $KKKKKK được lấy sang.
Trường lấy sang bị thiếu không hiểu nguyên nhân, em chưa trộn anh ạ!
Anh xem file em đính kèm đó ạ!

 

[Maika8008]

Dữ liệu phải bắt đầu từ cột A. Còn không thì bạn thay sub InsertField cũ bằng:

Sub InsertField()
Dim WordApp As Object
Dim wDoc As Object
Dim i&, k&, rw&, Col&
Dim sPath$, sFile$
Dim Rng As Range

Set Rng = Application.InputBox("Chon 1 cell bat ky cua bang can chen truong sang Word", Type:=8)
rw = Rng.CurrentRegion.Cells(1).Row
Col = Rng.CurrentRegion.Cells(Rng.CurrentRegion.Cells.Count).Column
sPath = Trim(Range("F1"))
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
sFile = Trim(Range("F2"))

Set WordApp = CreateObject("Word.Application")
Set wDoc = WordApp.Documents.Open(sPath & sFile)
WordApp.Visible = True

For k = 1 To Col
    wDoc.Range.InsertAfter Cells(rw, k) & vbCrLf
Next
WordApp.Activate
Set wDoc = Nothing
Set WordApp = Nothing
End Sub

P/S; File bạn gửi cho tôi bị lỗi gì đó rồi. Đừng dùng file đó nữa, tải lại file về dùng

 

[QuangMinhtb]

Dữ liệu phải bắt đầu từ cột A. Còn không thì bạn thay sub InsertField cũ băng:

Sub InsertField()
Dim WordApp As Object
Dim wDoc As Object
Dim i&, k&, rw&, Col&
Dim sPath$, sFile$
Dim Rng As Range

Set Rng = Application.InputBox("Chon 1 cell bat ky cua bang can chen truong sang Word", Type:=8)
rw = Rng.CurrentRegion.Cells(1).Row
Col = Rng.CurrentRegion.Cells(Rng.CurrentRegion.Cells.Count).Column
sPath = Trim(Range("F1"))
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
sFile = Trim(Range("F2"))

Set WordApp = CreateObject("Word.Application")
Set wDoc = WordApp.Documents.Open(sPath & sFile)
WordApp.Visible = True

For k = 1 To Col
    wDoc.Range.InsertAfter Cells(rw, k) & vbCrLf
Next
WordApp.Activate
Set wDoc = Nothing
Set WordApp = Nothing
End Sub

Vâng em cảm ơn anh,
Như được nhờ anh giúp ở #30: xin được anh giúp em để những file mới tạo ra trong quá trình trộn được nằm vào một Foder mới, Foder mới này nằm trong Foder của file Word và Foder này ở vị trí tương đương với vị trí của file Word đó được không ạ!

 

[Maika8008]

Vâng em cảm ơn anh, xin được anh giúp em để những file mới tạo ra trong quá trình trộn được nằm vào một Foder mới, Foder mới này nằm trong Foder của file Word và Foder này ở vị trí tương đương với vị trí của file Word đó được không ạ!

Giả sử tên Folder là FileTron thì:
Ở dòng wDoc.SaveAs2 sPath .... gì đó, bạn nối thêm: wDoc.SaveAs2 sPath & "FileTron\"

 

[QuangMinhtb]

Giả sử tên Folder là FileTron thì:
Ở dòng wDoc.SaveAs2 sPath .... gì đó, bạn nối thêm: wDoc.SaveAs2 sPath & "FileTron\"

Anh xem giúp em em có làm sai ở quá trình nào không ạ? Em thấy dữ liệu không được trộn
Em vẫn sử dụng code #28 anh giúp.

 

[Maika8008]

Chọn 1 ô chứ không chọn nguyên dòng

 

[QuangMinhtb]

Chọn 1 ô chứ không chọn nguyên dòng

Vẫn không trộn được anh, chọn 1 ô hay chọn cả dòng, hoặc chọn những ô cần trộn trong dòng đó đều không được ạ!