[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]

Mình bị lỗi này, b giúp mình sửa với, cảm ơn

View attachment 277076

Bạn đổi qua: wDoc.Activate thử xem

 

[thuy2022]

xin hỏi tác giả anh MaiKa có cách nào ở phần arrPath em muốn chỉ dẫn tới thư mục Foder chứa nhiều file mẫu, có bao nhiêu File mẫu thì trộn bấy nhiêu file. ( mục đích sau này em chỉ cần chép file mẫu vào thư mục tạo sẵn ) ( VD: "D:\TronThu\FileMauDeTronThu" thay vì phải chọn như dưới code. Thì sửa như thế nào . em xin cảm ơn
Nội dung code của anh
Sub MergeByVBA()
Dim WordApp As Object, wDoc As Object
Dim k&, rw&, Col&, arrF, Fullname, arrPath
Dim sPath$, sPathNew$, sFormat$
Dim Rng As Range
Dim fso As Object
On Error GoTo Thoat
Set Rng = Application.InputBox("Chon 1 cell bat ky cua dong can tron sang Word", Type:=8)
arrPath = Application.GetOpenFilename(Title:="Chon cac file doc can lam.", _
FileFilter:="Excel Files *.doc* (*.doc*),", MultiSelect:=True)
On Error GoTo Arr
If arrPath = False Then
MsgBox "Không có file nào.", vbExclamation, "Sorry!"
Exit Sub
Else
Arr:
sPath = Left(arrPath(1), InStrRev(arrPath(1), "\"))
rw = Rng.Row
Col = Range("XFD" & rw).End(xlToLeft).Column
arrF = Range("A4", Cells(4, Col)).Value
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
'
sPathNew = sPath
Set WordApp = CreateObject("Word.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
For Each Fullname In arrPath
Set wDoc = WordApp.Documents.Open(Fullname)
WordApp.Visible = False
On Error GoTo Ve
For k = 1 To Col
With WordApp.Selection.Find
.Text = arrF(1, k)
sFormat = GetFormat(Cells(rw, k))
If sFormat <> "0" Then
.Replacement.Text = Format(Cells(rw, k), sFormat)
Else
.Replacement.Text = Cells(rw, k)
End If
End With
WordApp.Selection.Find.Execute Replace:=wdReplaceAll
Next

If (Not fso.FolderExists(sPathNew)) Then fso.CreateFolder (sPathNew)
wDoc.SaveAs2 sPathNew & Range("G" & rw).Value & "-" & wDoc.Name
wDoc.Close False
Next
End If
MsgBox "Xong!"
Ve:
WordApp.Quit
Set wDoc = Nothing
Set WordApp = Nothing
Set fso = Nothing
Exit Sub
Thoat:
Set Rng = Nothing
End Sub

 

[Maika8008]

@thuy2022 :
Bạn tìm trên GPE cái hàm dùng FSO lấy hết các file trong thư mục, rồi dùng kết quả đó đưa vào chạy trong vòng lặp.

 

[thuy2022]

@thuy2022 :
Bạn tìm trên GPE cái hàm dùng FSO lấy hết các file trong thư mục, rồi dùng kết quả đó đưa vào chạy trong vòng lặp.

Em không rành về code anh à. anh có thể xem sửa giúp em với. Thật sự bài của anh áp dụng nhiều anh ạ
em xin chân thành cảm ơn anh

 

[Maika8008]

Em không rành về code anh à. anh có thể xem sửa giúp em với. Thật sự bài của anh áp dụng nhiều anh ạ
em xin chân thành cảm ơn anh

Dùng 2 sub ở dưới cùng có tên nick của bạn và đều là trộn 1 người cho nhiều văn bản. Tôi có chú thich trong code.

 

[thuy2022]

Dùng 2 sub ở dưới cùng có tên nick của bạn và đều là trộn 1 người cho nhiều văn bản. Tôi có chú thich trong code.

Hay quá anh ơi em cảm ơn anh nhiều. Giờ em chuyển sang trộn nhiều người cho nhiều văn bản. nếu có gì không được em hỏi anh nhé. Thật sự quá sức tưởng với những gì excel mang lại anh nhỉ biết thế này em tham gia diễn đàn sớm sớm rồi...

 

[Maika8008]

Hay quá anh ơi em cảm ơn anh nhiều. Giờ em chuyển sang trộn nhiều người cho nhiều văn bản. nếu có gì không được em hỏi anh nhé. Thật sự quá sức tưởng với những gì excel mang lại anh nhỉ biết thế này em tham gia diễn đàn sớm sớm rồi...

Chừ cũng đâu có muộn. Chỉ cần bạn cố 1 chút thôi thì 1 tháng là bạn tự sửa, chắp code được. Còn để viết được thì lâu hơn, bao lâu còn tùy.

 

[thuy2022]

Chừ cũng đâu có muộn. Chỉ cần bạn cố 1 chút thôi thì 1 tháng là bạn tự sửa, chắp code được. Còn để viết được thì lâu hơn, bao lâu còn tùy.

Dạ em cảm ơn anh đã động viên em sẽ cố gắng hi vọng được các anh chị bảo ạ

 

[doredore1988]

Bạn search google cú pháp option định dạng file lưu rồi đem thay vào lệnh saveas

Mình làm thế này thì khi trộn nhiều người đến khi kết thúc lệnh thì bị báo lỗi. Vậy thì chổ này nên sửa thế nào bạn?

 

[thuy2022]

Anh ơi nhờ anh sửa em code trộn nhiều người cho nhiều văn bản với . Em để sub mang tên nick anh ạ
em mày mò mãi mà kết quả chỉ có 1 dòng anh ạ

 

[Maika8008]

Mình làm thế này thì khi trộn nhiều người đến khi kết thúc lệnh thì bị báo lỗi. Vậy thì chổ này nên sửa thế nào bạn?

Sửa dòng đó thành:
wDoc.SaveAs2 Filename:=sPathNew & arrD(i, 1) & ".doc", FileFormat:=0
(Bỏ các tham số dài thòng đằng sau đi nếu không dùng gì tới nó)

 

[thuy2022]

Không được anh à. Nhờ anh sửa trên file em gui xem giúp e với

 

[Maika8008]

Không được anh à. Nhờ anh sửa trên file em gui xem giúp e với

Tôi có nói với bạn đâu mà được với không được?

 

[thuy2022]

Dạ anh xem sửa em code trộn nhiều người cho nhiều văn bản với anh

 

[Maika8008]

Dạ anh xem sửa em code trộn nhiều người cho nhiều văn bản với anh

Sub dưới cùng . . .

 

[thuy2022]

Dạ Em Cảm ơn anh nhiều ạ.

 

[ongke0711]

Sub dưới cùng . . .

Tôi thấy cái vụ đường dẫn file này có đủ các kiểu yêu cầu của mỗi người. Người thì muốn cố định khỏi chọn, người thì muốn tùy chọn đường dẫn do có thay đổi v.v.. Đi theo hướng dẫn sửa cũng hết giờ. Do đó nếu tôi thì sẽ không "hard code" đường dẫn file mà để thẳng trên sheet luôn cho người dùng muốn kiểu nào cũng được.

 

[Maika8008]

Trước đây làm vậy thì có người lại nhờ có hộp thoại chọn folder. Giờ làm hết các trường hợp rồi thì bạn gần đây lại không muốn chọn file doc mà lại muốn lọc lấy hết file doc trong folder để chạy. Chín người mười ý, thế mới mệt

 

[ongke0711]

Trước đây làm vậy thì có người lại nhờ có hộp thoại chọn folder. Giờ làm hết các trường hợp rồi thì bạn gần đây lại không muốn chọn file doc mà lại muốn lọc lấy hết file doc trong folder để chạy. Chín người mười ý, thế mới mệt

Thêm cái nút mở hộp thoại kế bên là được rồi. Tôi thường thiết kế ứng dụng theo kiểu viết cho người dùng không biết gì về lập trình, chỉ biết vi tính cơ bản để thao tác thôi. Còn viết tool cho người lập trình sử dụng thì lại khác. Có những tính năng, qui ước người dùng phải tuân thủ, làm theo để ứng dụng chạy đúng chứ không phải cái nào cũng sửa theo thói quen dùng, thao tác của họ thì chết luôn. Tất nhiên là thiết kế ứng dụng phải tạo trải nghiệm người dùng một cách thuận tiện, tốt nhất trước đã.

 

[thuy2022]

Em có ý tưởng xin các anh cho ý kiến. Tạo 1 ô B2 trên excel rồi làm cái list cần file nào chọn file đó thêm chế độ All các file mẫu nữa