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

Liên hệ QC

Maika8008

Thành viên gạo cội
Tham gia
12/6/20
Bài viết
4,741
Được thích
5,668
Donate (Momo)
Donate
Giới tính
Nam
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 --=0

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

File đính kèm

  • GMH.docx
    21.1 KB · Đọc: 538
  • MergeToMSWord_Update300923.xlsm
    177.4 KB · Đọc: 217
Lần chỉnh sửa cuối:
Upvote 0
Gõ ra đây chứ. Mà không biết vba không đồng nghĩa với việc không biết nó nằm ở đâu, cửa sổ của nó như thế nào.
Mình tìm được code tạo folder nằm ở vị trí màn hình.
Vậy vào đâu để chỉnh sửa để mỗi khi nhấn trộn 1 dòng hay trộn toàn bộ dữ liệu thì folder được tạo ra luôn và folder nằm trong folder chứa file word mẫu ban đầu vậy bạn? và các file word mới sinh ra nằm luôn trong folder được tạo.
Mình chưa biết nên thấy nó không đơn giản.
Mong bạn chỉ giáo
Mã:
Sub createFolderExample1()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim currentPath As String
    Dim folderTest As String
  
    currentPath = Application.ActiveWorkbook.Path
    folderTest = currentPath & "C:\Users\Admin\Desktop\" & "Foder"
         ' create folder "test" if not exists
    If (Not fso.FolderExists(folderTest)) Then
        fso.CreateFolder (folderTest)
    End If
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
Thử sửa code như thế này có đc ko :)
Mã:
Sub createFolderExample1()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim currentPath As String
    Dim folderTest As String
 
    currentPath = Application.ActiveWorkbook.Path
    folderTest = currentPath & "\Foder"
         ' create folder "test" if not exists
    If (Not fso.FolderExists(folderTest)) Then
        fso.CreateFolder (folderTest)
    End If
End Sub
 
Upvote 0
Thử sửa code như thế này có đc ko :)
Mã:
Sub createFolderExample1()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim currentPath As String
    Dim folderTest As String
 
    currentPath = Application.ActiveWorkbook.Path
    folderTest = currentPath & "\Foder"
         ' create folder "test" if not exists
    If (Not fso.FolderExists(folderTest)) Then
        fso.CreateFolder (folderTest)
    End If
End Sub
Cám ơn bạn! Xin được hỏi:
Gắn code vào vị trí nào trong file sau để khi nhấn (Trộn hàng loạt) hoặc (Trộn 1 dòng) thì folder được tạo ra luôn và folder nằm trong folder chứa file word mẫu ban đầu vậy bạn? và các file word mới sinh ra nằm luôn trong folder được tạo.
Đoạn code trong file :
Mã:
Option Explicit

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

On Error GoTo Thoat
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"))
On Error GoTo Ve
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
Ve:
WordApp.Activate
Set wDoc = Nothing
Set WordApp = Nothing
Thoat:
End Sub

Sub MergeByVBA()
Dim WordApp As Object
Dim wDoc As Object, wDoc2 As Object
Dim i&, k&, rw&, rw2&, Col&, arrD, arrF
Dim sPath$, sFile$
Dim Rng As Range

On Error GoTo Thoat
Set Rng = Application.InputBox("Chon 1 cell bat ky cua bang can tron sang Word", Type:=8)
rw2 = Rng.CurrentRegion.Cells(1).Row
If rw2 < 4 Then MsgBox "Chua chon du lieu merge!": Exit Sub
rw = rw2 + Rng.CurrentRegion.Rows.Count - 1

sPath = Trim(Sheet2.Range("F1"))
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
sFile = Trim(Range("F2"))
Col = Range("A" & rw2).End(xlToRight).Column
arrD = Range("A" & rw2 + 1, Cells(rw, Col))
arrF = Range("A" & rw2, Cells(rw2, Col))
On Error GoTo Ve
Set WordApp = CreateObject("Word.Application")
For i = 1 To UBound(arrD)
    Set wDoc = WordApp.Documents.Open(sPath & sFile)
    WordApp.Visible = False
    For k = 1 To Col
        With WordApp.Selection.Find
            .Text = arrF(1, k)
            .Replacement.Text = Format(Cells(i + rw2, k), GetFormat(Cells(i + rw2, k)))
        End With
        WordApp.Selection.Find.Execute Replace:=wdReplaceAll
    Next
    wDoc.SaveAs2 sPath & arrD(i, 1) & ".docx"
    wDoc.Close False
Next
Ve:
WordApp.Quit
Set wDoc = Nothing
Set WordApp = Nothing
MsgBox "Xong!"
Thoat:
End Sub

Sub MergeByVBA_One()
Dim WordApp As Object
Dim wDoc As Object, wDoc2 As Object
Dim k&, rw&, rw2&, Col&, arrF
Dim sPath$, sFile$
Dim Rng As Range

On Error GoTo Thoat
Set Rng = Application.InputBox("Chon 1 cell bat ky cua dong can tron sang Word", Type:=8)
sPath = Trim(Sheet2.Range("F1"))
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
sFile = Trim(Sheet2.Range("F2"))
rw2 = Rng.CurrentRegion.Cells(1).Row
rw = Rng.Row
Col = Rng.End(xlUp).End(xlToLeft).End(xlToRight).Column
arrF = Range("A" & Rng.End(xlUp).Row, Cells(rw2, Col)).Value
On Error GoTo Ve
Set WordApp = CreateObject("Word.Application")
Set wDoc = WordApp.Documents.Open(sPath & sFile)
WordApp.Visible = True
For k = 1 To Col
    With WordApp.Selection.Find
        .Text = arrF(1, k)
        .Replacement.Text = Format(Cells(rw, k), GetFormat(Cells(rw, k)))
    End With
    WordApp.Selection.Find.Execute Replace:=wdReplaceAll
Next
wDoc.SaveAs2 sPath & Cells(rw, 1) & ".docx"
Ve:
wDoc.Close False
WordApp.Quit
Set wDoc = Nothing
Set WordApp = Nothing
MsgBox "Xong!"
Thoat:
End Sub

Function GetFormat(iValue)
 Dim isNumber As Boolean
 Dim isDay As Boolean
 Dim numFormat As String
 Dim isTime As Boolean
 
    numFormat = iValue.NumberFormat
    isNumber = IsNumeric(iValue)
    isDay = IsDate(iValue)
   
    Select Case numFormat
        Case "General", "0", "0.0", "0.00", "#,##0", "#,##0.00", "h:mm", "hh:mm"
            If isNumber Then
                If numFormat = "#,##0.00" Then
                    GetFormat = "#,##0.00"
                ElseIf numFormat = "#,##0" Then
                    GetFormat = "#,##0"
                ElseIf numFormat = "h:mm" Or numFormat = "hh:mm" Then
                    GetFormat = "hh:mm"
                Else
                    GetFormat = "0"
                End If
            End If
        Case "Date", "dd-mm-yy", "m/d/yyyy"
            If isDay Then
                GetFormat = "dd/mm/yyyy"
            End If
        Case Else
            GetFormat = ""
    End Select

 End Function
 

File đính kèm

  • MergeToMSWord_QuangMinhtb_2.xlsm
    24.1 KB · Đọc: 13
Upvote 0
@chothadiem
File đã thêm chức năng tạo filder mới chứa file kết quả + 2 file mẫu trộn
 

File đính kèm

  • MergeToMSWord_QuangMinhtb_2.xlsm
    24.9 KB · Đọc: 77
  • Cong van chap thuan.docx
    27.3 KB · Đọc: 58
  • GMH.docx
    19.7 KB · Đọc: 57
Lần chỉnh sửa cuối:
Upvote 0
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 --=0

Ứ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 file Word mẫu tại ô E1 , tên file Word mẫu tại ô E2 của file Excel.
- 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.
admin cho hỏi: có thể chèn hoặc trộn nguyên một bảng được đặt tên trong excel sang word được không vậy?
 
Upvote 0
Chèn như bạn nói là chèn thủ công à? hay cách nào bạn mà chèn được cả bảng.
Bạn có thể ví dụ cụ thể giúp mình được chứ?
Có 2 kiểu: Chép cả vùng dán sang Word hoặc ghi tuần tự từng cell qua bảng được tạo bên Word.

Mỗi nút bấm chạy mỗi kiểu.
 

File đính kèm

  • Tao-ChepBangWordTuExcel.xlsm
    22.1 KB · Đọc: 66
Upvote 0
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 --=0

Ứ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 file Word mẫu tại ô E1 , tên file Word mẫu tại ô E2 của file Excel.
- 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
Anh cho em hỏi giả sử nội dung công việc có người 1 cviec, có người nhiều hơn 1 công việc thì sẽ xử lý như thế, nào ạ. Cảm ơn Anh :)
 
Upvote 0
Anh cho em hỏi giả sử nội dung công việc có người 1 cviec, có người nhiều hơn 1 công việc thì sẽ xử lý như thế, nào ạ. Cảm ơn Anh :)
Chuyện này phải đồng bộ giống như mail merge bạn à. Còn như muốn phá cách thì phải tính cách khác dựa trên việc xem xét cơ sở dữ liệu nguồn
 
Upvote 0
Web KT
Back
Top Bottom