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

 

[Manhhung90]

Với Option 1 thì sau khi có kết quả chỉ đơn giản là dùng tay chép file kết quả sang chỗ mong muốn. Chuyện đơn giản như thế mà phải viết riêng cho yêu cầu của bạn thì tôi không muốn làm.

Anh giúp em giúp cho trót với ạ. Do em không biết gì VBA nên không biết viết như thế nào mặc dù biết với anh là dễ ạ. Hiện tại là tên file word lưu là tên file trộn ạ.

 

[Manhhung90]

Với Option 1 thì sau khi có kết quả chỉ đơn giản là dùng tay chép file kết quả sang chỗ mong muốn. Chuyện đơn giản như thế mà phải viết riêng cho yêu cầu của bạn thì tôi không muốn làm.

Giúp em chỉnh code đoạn lưu file là tên file trộn mình chọn và lưu tại thư mục như của option 3 được không ạ. Nhờ anh giúp em với. Em cảm ơn

 

[SaoKyZayTa]

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.

Em đang gặp khó chỗ này lại tìm thấy đúng bài này, Cảm ơn Chủ Topic rất nhiều ạ

 

[ngochienbmt]

Ang ơi có cách nào mà mình chèn 1 cái bản biểu bên excel or 1 vùng dữ liệu qua đc word được không nhi, có thể cái bản đó mình đã đặt tên cố định rồi, hoặc đại loại mình phải chọn vị trí bản, từ ô này đến ô kia, gán ị trí đó vào word như các trường khác được không anh

 

[Maika8008]

Ang ơi có cách nào mà mình chèn 1 cái bản biểu bên excel or 1 vùng dữ liệu qua đc word được không nhi, có thể cái bản đó mình đã đặt tên cố định rồi, hoặc đại loại mình phải chọn vị trí bản, từ ô này đến ô kia, gán ị trí đó vào word như các trường khác được không anh

Việc đó là dễ nhưng phải làm riêng, không viết chung trong code bài này được

 

[ngochienbmt]

Việc đó là dễ nhưng phải làm riêng, không viết chung trong code bài này được

Vâng anh nhưng làm riêng vậy chỉ chạy đc phần đó thôi hay sao ak, không kết hợp cùng một lúc đc hả anh,

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

Việc đó là dễ nhưng phải làm riêng, không viết chung trong code bài này được

Nếu anh có thời gian giúp anh em ko ak, còn phần chương trình kia với em là quá tuyệt rồi, e chỉ còn xử lý cách chèn thông tin của bản nữa là ổn, vì tạo 1 bản trcs bên word rồi chèn vào cũng đc nhưng dữ liệu có thể thay đổi nhiều dòng hơn or ít dòng hơn, do đó em thấy dùng kiểu table của excel thấy cũng tiện. Mong anh giúp đỡ

 

[Maika8008]

Vâng anh nhưng làm riêng vậy chỉ chạy đc phần đó thôi hay sao ak, không kết hợp cùng một lúc đc hả anh,

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

Nếu anh có thời gian giúp anh em ko ak, còn phần chương trình kia với em là quá tuyệt rồi, e chỉ còn xử lý cách chèn thông tin của bản nữa là ổn, vì tạo 1 bản trcs bên word rồi chèn vào cũng đc nhưng dữ liệu có thể thay đổi nhiều dòng hơn or ít dòng hơn, do đó em thấy dùng kiểu table của excel thấy cũng tiện. Mong anh giúp đỡ

Thôi được! Bạn gửi file Excel đang chạy và file Word mẫu của bạn lên đây tôi xem thử.

 

[ngochienbmt]

Thôi được! Bạn gửi file Excel đang chạy và file Word mẫu của bạn lên đây tôi xem thử.

Anh xem giúp cách để đưa cái table từ excel chèn vào word giúp em

 

[Maika8008]

Tôi chưa xem nhưng hỏi trước: bạn trộn dạng nào vậy? Một file Word trộn nhiều dòng khác nhau?

 

[ngochienbmt]

Tôi chưa xem nhưng hỏi trước: bạn trộn dạng nào vậy? Một file Word trộn nhiều dòng khác nhau?

HIện tại thì minhg chỉ trộn 1 dòng cho nhiều file word

 

[Maika8008]

HIện tại thì minhg chỉ trộn 1 dòng cho nhiều file word

Tôi chưa biết bạn muốn lấy thông tin từ Table "BanVL" vào file Word như thế nào vì Table chỉ có 3 cột trong khi bảng Word có đến 4 cột (chưa kể cột số thứ tự). Do vậy, tôi tạm thời xóa bớt 2 cột của bảng Word để chạy code cho bạn thấy trước đã. Từ từ tính tiếp.

Về cái bảng Word thì trong file mẫu bảng đó chỉ cần 2 dòng thôi: 1 dòng tiêu đề và 1 dòng trắng. Khi thực thi, code sẽ chèn thêm dòng tùy thuộc vào số dòng của Table "BanVL" của Excel.

Chạy code ChenTableVaoWord trong file Excel rồi xem kết quả tại file Word. (Lưu ý: Không nên đặt tên tiếng Việt có dấu cho file Word mẫu vì khó xử lý khi viết code)

 

[ngochienbmt]

Tôi chưa biết bạn muốn lấy thông tin từ Table "BanVL" vào file Word như thế nào vì Table chỉ có 3 cột trong khi bảng Word có đến 4 cột (chưa kể cột số thứ tự). Do vậy, tôi tạm thời xóa bớt 2 cột của bảng Word để chạy code cho bạn thấy trước đã. Từ từ tính tiếp.

Về cái bảng Word thì trong file mẫu bảng đó chỉ cần 2 dòng thôi: 1 dòng tiêu đề và 1 dòng trắng. Khi thực thi, code sẽ chèn thêm dòng tùy thuộc vào số dòng của Table "BanVL" của Excel.

Chạy code ChenTableVaoWord trong file Excel rồi xem kết quả tại file Word. (Lưu ý: Không nên đặt tên tiếng Việt có dấu cho file Word mẫu vì khó xử lý khi viết code)

Cam on bac để em chạy thử như thế nào

 

[ngochienbmt]

Tôi chưa biết bạn muốn lấy thông tin từ Table "BanVL" vào file Word như thế nào vì Table chỉ có 3 cột trong khi bảng Word có đến 4 cột (chưa kể cột số thứ tự). Do vậy, tôi tạm thời xóa bớt 2 cột của bảng Word để chạy code cho bạn thấy trước đã. Từ từ tính tiếp.

Về cái bảng Word thì trong file mẫu bảng đó chỉ cần 2 dòng thôi: 1 dòng tiêu đề và 1 dòng trắng. Khi thực thi, code sẽ chèn thêm dòng tùy thuộc vào số dòng của Table "BanVL" của Excel.

Chạy code ChenTableVaoWord trong file Excel rồi xem kết quả tại file Word. (Lưu ý: Không nên đặt tên tiếng Việt có dấu cho file Word mẫu vì khó xử lý khi viết code)

Chèn được như vậy là ok rồi anh ơi. Vì do làm em chọn option là chèn 1 dòng cho nh văn bản ( để code nó tạo ra thư mục riêng) nên thường sẽ quét hết các file word or nh hơn 1 file. Nên anh tuỳ biến giúp em để ko cố định tên file nha or vị trí file gốc nha.

 

[tuan16]

Anh xem giúp cách để đưa cái table từ excel chèn

Anh xem giúp cách để đưa cái table từ excel chèn vào word giúp em

chào anh. em xin file excell nghiệm thu đầy đủ biên bản của anh tham khảo với ạ

Chèn được như vậy là ok rồi anh ơi. Vì do làm em chọn option là chèn 1 dòng cho nh văn bản ( để code nó tạo ra thư mục riêng) nên thường sẽ quét hết các file word or nh hơn 1 file. Nên anh tuỳ biến giúp em để ko cố định tên file nha or vị trí file gốc nha.

em chào bác... bác có thể cho em xin file nghiệm thu đầy đủ các biên bản ở như ở sheet " data" của các để tham khảo được không ạ

 

[Maika8008]

Chèn được như vậy là ok rồi anh ơi. Vì do làm em chọn option là chèn 1 dòng cho nh văn bản ( để code nó tạo ra thư mục riêng) nên thường sẽ quét hết các file word or nh hơn 1 file. Nên anh tuỳ biến giúp em để ko cố định tên file nha or vị trí file gốc nha.

Tôi đã chêm lệnh copy bảng vào trong code trộn 1 dòng 1 văn bản và code trộn 1 dòng nhiều văn bản. Nếu muốn sửa code để đồng bộ cái bảng Word với dữ liệu trong Table "BanVL" thì từ từ tính tiếp.

Lưu ý: code chỉ định cứng là copy vào bảng thứ 2 của file Word mẫu (Set oTbl = Wdoc.Tables(2)). Nếu không có bảng 2 thì sẽ có lỗi. Nếu bảng 2 là bảng gì đó khác thì sẽ có kết quả không mong muốn.
Muốn chỉ định linh hoạt thì phải dùng cách khác.

 

[ngochienbmt]

Tôi đã chêm lệnh copy bảng vào trong code trộn 1 dòng 1 văn bản và code trộn 1 dòng nhiều văn bản. Nếu muốn sửa code để đồng bộ cái bảng Word với dữ liệu trong Table "BanVL" thì từ từ tính tiếp.

Lưu ý: code chỉ định cứng là copy vào bảng thứ 2 của file Word mẫu (Set oTbl = Wdoc.Tables(2)). Nếu không có bảng 2 thì sẽ có lỗi. Nếu bảng 2 là bảng gì đó khác thì sẽ có kết quả không mong muốn.
Muốn chỉ định linh hoạt thì phải dùng cách khác.

Cảm ơn anh rất nhiều

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

Tôi đã chêm lệnh copy bảng vào trong code trộn 1 dòng 1 văn bản và code trộn 1 dòng nhiều văn bản. Nếu muốn sửa code để đồng bộ cái bảng Word với dữ liệu trong Table "BanVL" thì từ từ tính tiếp.

Lưu ý: code chỉ định cứng là copy vào bảng thứ 2 của file Word mẫu (Set oTbl = Wdoc.Tables(2)). Nếu không có bảng 2 thì sẽ có lỗi. Nếu bảng 2 là bảng gì đó khác thì sẽ có kết quả không mong muốn.
Muốn chỉ định linh hoạt thì phải dùng cách khác.

Mà anh ơi sao copy bản qua word làm sao để cố định font cho bản là font theo file word vậy anh( times new roma)

 

[Maika8008]

Cảm ơn anh rất nhiều

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

Mà anh ơi sao copy bản qua word làm sao để cố định font cho bản là font theo file word vậy anh( times new roma)

Bạn sửa Style Normal của file Word mẫu theo ý muốn đi. Bấm công cụ Normal trong nhóm Style rồi Modified. Sửa xong chọn AutoUpdate và Apply to New Document trước khi lưu.

Sau đó áp dụng style Normal cho bảng.
.

 

[ngochienbmt]

Tôi đã chêm lệnh copy bảng vào trong code trộn 1 dòng 1 văn bản và code trộn 1 dòng nhiều văn bản. Nếu muốn sửa code để đồng bộ cái bảng Word với dữ liệu trong Table "BanVL" thì từ từ tính tiếp.

Lưu ý: code chỉ định cứng là copy vào bảng thứ 2 của file Word mẫu (Set oTbl = Wdoc.Tables(2)). Nếu không có bảng 2 thì sẽ có lỗi. Nếu bảng 2 là bảng gì đó khác thì sẽ có kết quả không mong muốn.
Muốn chỉ định linh hoạt thì phải dùng cách khác.

anh vậy file này là cố định có 1 Table trong excel và sẽ chèn qua tất cả file word có table là 2, nếu file excel của mình có nhiều table và chèn vào các file khác nhau khôn g đc hả anh, ví dụ Có 3 bản BanVL, BanVL1, BanVL2, chèn vào cho 3 file word khác nhau thì không đc nhỉ, vì code hiểu lầm giữa các file đều có bản 2 or số bản do mình chèn.

 

[Maika8008]

anh vậy file này là cố định có 1 Table trong excel và sẽ chèn qua tất cả file word có table là 2, nếu file excel của mình có nhiều table và chèn vào các file khác nhau khôn g đc hả anh, ví dụ Có 3 bản BanVL, BanVL1, BanVL2, chèn vào cho 3 file word khác nhau thì không đc nhỉ, vì code hiểu lầm giữa các file đều có bản 2 or số bản do mình chèn.

Như vậy là thuộc diện cao cấp rồi bạn.

 

[ngochienbmt]

anh ơi làm sao để khi mình mở thư mục chọn file để chèn nó bung ra cái thư mục chứa cái file merge luôn anh nhi