[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).

 

[ongke0711]

Anh ơi. Hiện tại, File của anh có thể chọn từng vùng để chạy dữ liệu. Nhưng nếu khi em dùng Filter để lọc, ẩn đi 1 vài dòng, thì khi chạy kết quả các dòng ẩn đó vẫn chạy ra kết quả. Vậy anh có thể bổ sung tính năng nếu như dùng filter để lọc, các dòng đã ẩn đi, thì khi chạy kết quả sẽ bỏ qua các dòng đó không ạ? Em xin cảm ơn ạ.

14/06/2022: Cập nhật trường hợp chọn dữ liệu trong vùng có dùng Filter để lọc.

Đã cập nhật file vào bài #215.

 

[ongke0711]

Phải có cách nào đó nhưng tạm thời tôi chưa nghĩ ra.

Cái vụ Font này theo quan điểm của tôi là sẽ không đưa vào công cụ trộn dùng chung vì có một số điểm như sau: (Nếu muốn viết riêng cho trường hợp nào đó thì cũng được thôi)
- Sẽ tốn thêm một công đoạn code là kiểm tra font từng cột dữ liệu trước khi trộn, sau đó trộn vào Word và code cho việc định dạng font phía Word.
- Nếu phát sinh trường hợp: dòng text của một cột nào đó vừa font chữ thường Arial vừa Winding2 thì xử lý như thế nào?

Nói chung là viết được hết nhưng code nhiều thì thời gian thực thi sẽ kéo dài hơn vì phải chạy thêm code kiểm tra các kiểu.

Hàm lấy tên Font:

Function getFontInfo(Rng As Range, Optional iType As Integer)
    ' iType: 1 - Name, 2- Size'
    Application.Volatile
    If iType = 2 Then
        getFontInfo = Rng.Font.Size
    Else
        getFontInfo = Rng.Font.Name
    End If
End Function

 

[Maika8008]

trường hợp font chữ cũng chưa được anh ạ.... ở bản mới em download về ở trường &Số văn bản là kiểu chữ Wingdings 2( bản excell) sang bản word vẫn chuyển về Times New Roman

File cho bạn đây. Bạn test hết các trường hợp dùm tôi. Nếu ổn thì tôi cập nhật lên bài #1.

Điều kiện là 1 cell chỉ duy nhất 1 font. Nếu trong cell có 2 font như bài #242 nói thì tôi chịu à nghe.

 

[tuan16]

Phải có cách nào đó nhưng tạm thời tôi chưa nghĩ ra.

File cho bạn đây. Bạn test hết các trường hợp dùm tôi. Nếu ổn thì tôi cập nhật lên bài #1.

Điều kiện là 1 cell chỉ duy nhất 1 font. Nếu trong cell có 2 font như bài #242 nói thì tôi chịu à nghe.

cảm ơn anh đã nhiệt tình giúp ạ

Bài đã được tự động gộp: 18/6/22

Phải có cách nào đó nhưng tạm thời tôi chưa nghĩ ra.

File cho bạn đây. Bạn test hết các trường hợp dùm tôi. Nếu ổn thì tôi cập nhật lên bài #1.

Điều kiện là 1 cell chỉ duy nhất 1 font. Nếu trong cell có 2 font như bài #242 nói thì tôi chịu à nghe.

Bù

File cho bạn đây. Bạn test hết các trường hợp dùm tôi. Nếu ổn thì tôi cập nhật lên bài #1.

Điều kiện là 1 cell chỉ duy nhất 1 font. Nếu trong cell có 2 font như bài #242 nói thì tôi chịu à nghe.

A xem giúp ạ...nó bị lỗi thế này

 

[Maika8008]

cảm ơn anh đã nhiệt tình giúp ạ

Bài đã được tự động gộp: 18/6/22

Bù

A xem giúp ạ...nó bị lỗi thế này

Bạn nên kỹ lưỡng dùm chút. Tôi thật là không thích những người hời hợt. Nói mất lòng nhưng tôi không ngại

Hồi nào mà bạn hỏi rõ ràng hơn thì tôi trả lời.

 

[batman1]

Bạn nên kỹ lưỡng dùm chút. Tôi thật là không thích những người hời hợt. Nói mất lòng nhưng tôi không ngại

Trong Sub MergeExcelToWord_AllDoc có 5 LABEL cùng tên XFontResume:. Trong mỗi sub/function chỉ được phép có 1 LABEL với mỗi tên.

Lỗi được nêu rõ trong hình đính kèm.

 

[tuan16]

Bạn nên kỹ lưỡng dùm chút. Tôi thật là không thích những người hời hợt. Nói mất lòng nhưng tôi không ngại

Hồi nào mà bạn hỏi rõ ràng hơn thì tôi trả lời.

Dạ...em gõ nhầm chữ bù vào ạ... Em download file excell anh gửi về và phát sinh lỗi như ảnh em gửi ạ

 

[Maika8008]

Dạ...em gõ nhầm chữ bù vào ạ... Em download file excell anh gửi về và phát sinh lỗi như ảnh em gửi ạ

Bạn tải lại file. Hôm qua thử chỉ 1 trường hợp tưởng ổn, hóa ra còn nhiều việc cho mấy cái nhãn nhảy lên nhảy xuống đó thì mới chạy ổn được cho tất cả các trường hợp.

Sau bạn có chèn hình thì bấm vào cái hình chọn chèn/ hình đầy đủ hoặc copy hình dán thẳng vào bài viết.

 

[ppicatru]

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.

sao không hiểu cách làm vậy bạn

 

[Maika8008]

sao không hiểu cách làm vậy bạn

Có nhiều thứ thay đổi nhưng tôi lại không cập nhật hướng dẫn ở bài #1. Do đó, bạn lướt qua các bình luận trong chủ đề và thử chút là nắm được cách làm hoặc chờ tôi cập nhật cách sử dụng.

 

[thuy2022]

Xin Anh MaiKa Sửa lỗi giúp em với. em đang bị lỗi khi chạy trộn 1 dòng nhiều văn bản
em cảm ơn anh nhiều

 

[tuan16]

Bạn tải lại file. Hôm qua thử chỉ 1 trường hợp tưởng ổn, hóa ra còn nhiều việc cho mấy cái nhãn nhảy lên nhảy xuống đó thì mới chạy ổn được cho tất cả các trường hợp.

Sau bạn có chèn hình thì bấm vào cái hình chọn chèn/ hình đầy đủ hoặc copy hình dán thẳng vào bài viết.

Bạn tải lại file. Hôm qua thử chỉ 1 trường hợp tưởng ổn, hóa ra còn nhiều việc cho mấy cái nhãn nhảy lên nhảy xuống đó thì mới chạy ổn được cho tất cả các trường hợp.

Sau bạn có chèn hình thì bấm vào cái hình chọn chèn/ hình đầy đủ hoặc copy hình dán thẳng vào bài viết.

Dạ cảm ơn anh ạ.... em kiểm tra file mới anh gửi chưa phát sinh lỗi gì ạ

 

[thuy2022]

Nhờ Anh MaiKa sửa giúp em với Anh. em cảm ơn anh nhiều lắm

 

[Maika8008]

Xin Anh MaiKa Sửa lỗi giúp em với. em đang bị lỗi khi chạy trộn 1 dòng nhiều văn bản
em cảm ơn anh nhiều

Tôi thấy bạn đi sửa code của tôi dẫn đến chạy bị lỗi rồi nhờ tôi sửa là sao?
Code của tôi: sPathNew = Left(sPath(1), InStrRev(sPath(1), "\")) & Trim(Range("A" & Rng.Row).Value) & "\"
Còn bạn đi sửa lại thành: sPathNew = Left(sPath(1), InStrRev(sPath(1), "\")) & Trim(Cells(Rng.Row, Col).Value) & "\" ' THAY CODE
Lúc đó biến Col chưa được gán giá trị (đang = 0) thì lấy gì cho nó chạy.
Còn nhiều dòng liên quan đến biến Col bạn cũng tự sửa mà tôi không hiểu sửa để làm gì vì dữ liệu của bạn có gì đặc biệt hơn dữ liệu ví dụ của tôi đâu?

 

[thuy2022]

Dạ như anh lấy cố định là cột A làm tên file thì chuẩn rồi.

 

[Maika8008]

Dạ như anh lấy cố định là cột A làm tên file thì chuẩn rồi. mục đich Em làm vậy sau này chèn thêm trường dữ liệu thì tên file luôn luôn lấy theo cột cuối cùng nên không ảnh hưởng gì anh à. Anh xem sửa giúp em lỗi đó các chức năng khác dùng thì được rồi a à

Trim(Cells(Rng.Row, Col).Value) & "\" Col đã có đâu. Bạn sửa được mà.

 

[thuy2022]

Huhu. Mong anh sửa giúp em với em e không sửa được anh à

 

[Maika8008]

Huhu. Mong anh sửa giúp em với em e không sửa được anh à

Gợi ý vậy mà cũng bất lực là sao, chưa có Col thì gán cho nó. Công thức Col bạn sửa loạn lên đó mà không biết?

 

[thuy2022]

Em không biết sửa đoạn nào nữa anh à mong anh sửa trên file e đính kèm với anh

 

[ongke0711]

sửa quá loạn code luôn, giờ không đâu là bờ...