Bài tập "Tìm và thay thế VBA"

Thanhvotan · 18/11/19

Dạ em mới tập tành học VBA.
Em có bài tập chuyển dữ liệu từ excel qua word, em đã làm được nhưng có vùng ly_do lúc nhiều ký tự (tham khảo trên mạng là 255) nên nó báo lỗi 5854.
Xin các cao thủ giúp em với.
Em đính kèm file bên dưới

batman1 · 18/11/19

1. Tập tin Word và Excel để cùng thư mục.

2. Xóa toàn bộ code trong Module1 và thay bằng

Mã:

Option Explicit

Sub Button1_Click()
Const wdReplaceAll = 2
Const wdFindContinue = 1

     Dim num_of_cust As Long
     Dim num_of_column As Long
     Dim i As Long, j As Long
     Dim template As Object
     Dim t As Object
     
     num_of_column = 5
     
     num_of_cust = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row - 1
     
     With CreateObject("word.application") ' late binding
        .Visible = True
        For i = 1 To num_of_cust
            Set template = .documents.Open(ThisWorkbook.Path & "\vi_du_ve_nhap_lieu.docx")
            Set t = .Selection
            With t.Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .Forward = True
                .Wrap = wdFindContinue
                .Replacement.text = "^c"
            End With
            For j = 1 To num_of_column
                With t.Find
                    .text = Sheet1.Cells(1, j).Value
                    CopyTextToClipboard Sheet1.Cells(i + 1, j).Value
                    .Execute Replace:=wdReplaceAll
                End With
            Next j
            template.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & i & "-don_xin.docx"
        Next
        .Quit
    End With
    Set t = Nothing
    Set template = Nothing
End Sub

Private Sub CopyTextToClipboard(ByVal text As String)
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText text
        .PutInClipboard
    End With
End Sub

Thanhvotan · 18/11/19

Dạ em xin cảm ơn anh. Để tối về em chạy thử.

batman1 · 18/11/19

Thanhvotan đã viết:
Dạ em xin cảm ơn anh. Để tối về em chạy thử.

Chuỗi dài là một chuyện. Bạn dùng hằng số wdReplaceAll của Word thì Excel không biết đâu. Nếu không có Option Explicit như của bạn thì Excel coi wdReplaceAll là biến không được khai báo tường minh. Nó sẽ có kiểu là Variant, và do không được thiết lập giá trị nên nó có giá trị mặc định là 0 (chuỗi rỗng, false). Lúc đó Replace sẽ không đúng với dụng ý, vì phải có wdReplaceAll = 2.

Code mà tôi thêm dùng 2 hằng số của Word và cả 2 đều được khai báo

Mã:

Const wdReplaceAll = 2
Const wdFindContinue = 1

VetMini · 18/11/19

Bài tập gì mà hắc ám vậy trời. Làm xong bài này thì người học nắm được cái gì?

Thanhvotan · 19/11/19

VetMini đã viết:
Bài tập gì mà hắc ám vậy trời. Làm xong bài này thì người học nắm được cái gì?

Dạ ko. Tại mục đích đi học là làm đc cái này. Nên thầy chỉ sơ sơ rồi để em tự mày mò

Bài đã được tự động gộp: 19/11/19

batman1 đã viết:
Chuỗi dài là một chuyện. Bạn dùng hằng số wdReplaceAll của Word thì Excel không biết đâu. Nếu không có Option Explicit như của bạn thì Excel coi wdReplaceAll là biến không được khai báo tường minh. Nó sẽ có kiểu là Variant, và do không được thiết lập giá trị nên nó có giá trị mặc định là 0 (chuỗi rỗng, false). Lúc đó Replace sẽ không đúng với dụng ý, vì phải có wdReplaceAll = 2.

Code mà tôi thêm dùng 2 hằng số của Word và cả 2 đều được khai báo

Mã:

Const wdReplaceAll = 2 Const wdFindContinue = 1

Dạ em đã sử dụng đc. Cảm ơn thầy nhiều

Thanhvotan · 20/11/19

Code chạy rất êm và cảm ơn thầy đã giảng kỹ để em hiểu. Nhưng khi em tạo thử thêm một button tương tự cho một file word khác, dữ liệu lấy cùng từ file excel input, khi run thử thì lại báo can't put in clipboard. Cảm ơn thầy trước ạ

Thanhvotan · 20/11/19

Thanhvotan đã viết:
Code chạy rất êm và cảm ơn thầy đã giảng kỹ để em hiểu. Nhưng khi em tạo thử thêm một button tương tự cho một file word khác, dữ liệu lấy cùng từ file excel input, khi run thử thì lại báo can't put in clipboard. Cảm ơn thầy trước ạ

Dạ em tạo thêm module mới để chứa bộ code cho button2 thì chạy ok. Liệu em tạo nhiều module thì có sao ko thầy?

batman1 · 20/11/19

Thanhvotan đã viết:
Dạ em tạo thêm module mới để chứa bộ code cho button2 thì chạy ok. Liệu em tạo nhiều module thì có sao ko thầy?

Bạn có thể tạo nhiều Module nhưng chúng sẽ có những gì?

Vd. tôi có 2 sub trong code đưa cho bạn. Nhưng Sub CopyTextToClipboard chỉ phải để trong 1 module và gọi sub đó từ nhiều nơi. Muốn thế phải đổi Private Sub CopyTextToClipboard thành Public Sub CopyTextToClipboard.

Về Button1, Button2 ... nếu chúng cùng làm một việc y như nhau, chỉ là có thay đổi chút xíu thì ta sẽ không làm như thế. Lúc đó ta sẽ tạo 1 sub Gìgìđó và tất cả các Button1_Click, Button2_Click, ... sẽ cùng gọi sub Gìgìđó.

Bạn hãy cho ví dụ về 2 Button và nói chúng làm gì thì tôi sẽ viết cho bạn sub Gìgìđó tổng quát.

Thanhvotan · 20/11/19

batman1 đã viết:
Bạn có thể tạo nhiều Module nhưng chúng sẽ có những gì?

Vd. tôi có 2 sub trong code đưa cho bạn. Nhưng Sub CopyTextToClipboard chỉ phải để trong 1 module và gọi sub đó từ nhiều nơi. Muốn thế phải đổi Private Sub CopyTextToClipboard thành Public Sub CopyTextToClipboard.

Về Button1, Button2 ... nếu chúng cùng làm một việc y như nhau, chỉ là có thay đổi chút xíu thì ta sẽ không làm như thế. Lúc đó ta sẽ tạo 1 sub Gìgìđó và tất cả các Button1_Click, Button2_Click, ... sẽ cùng gọi sub Gìgìđó.

Bạn hãy cho ví dụ về 2 Button và nói chúng làm gì thì tôi sẽ viết cho bạn sub Gìgìđó tổng quát.

Dạ cái này em chỉ suy diễn ra thêm ạ. Để em đi mượn mẫu từ anh trai rồi chế mẫu lại ạ. Thầy nhiệt tình quá chừng, cảm ơn thầy nhiều nhiều.

Thanhvotan · 21/11/19

Dạ em xin trình bày ý tưởng: cùng file dữ liệu excel, có 5 file word. Sự kiện đều là tìm và thay thế. Nhập 1 nút ra cả 5 file. Hôm qua em làm 5 module cho mỗi file word ạ. Mấy cái mẫu của anh em, nó rối mắt quá nên em tự chế các mẫu theo ví dụ lần trước luôn ạ. Xin thầy chỉ dạy. Cảm ơn thầy.

batman1 · 21/11/19

Lưu ý:
- bỏ Reference Microsoft Word 15.0 Object Library. Ta dùng kết nối chậm nên không cần thêm Reference. Thậm chí không được thêm vì nếu mở trên máy khác, vd. máy tôi, thì có thể có lỗi và không thể chạy được code. Lỗi là do máy tôi chỉ có phiên bản 14.0

- hãy đặt tên cho sheet dữ liệu là data như trong tập tin đính kèm.

- hãy thêm 1 sheet có tên là word_files. Trong sheet word_files nhập trong cột A bắt đầu từ A1 lần lượt tất cả các tên tập tin Word - chỉ tên không có đường dẫn đầy đủ vì các tập tin Word phải để cùng thư mục với tập tin Excel. Thêm sheet word_files với tên các tập tin vì code tổng quát không thể mặc định là sẽ chỉ có 5 tập tin với các tên cứng nhắc như bạn liệt kê. Code sẽ duyệt tất cả các tên trong word_files bất luận có bao nhiêu tên - 5 hay là 3 hay 15, và bất luận chúng có tên như thế nào, không nhất thiết cứng nhắc là vi_du_ve_nhap_lieu.docx hay vi_du_ve_nhap_lieu_2.docx

- Code tổng quát không thể mặc định cứng nhắc là dữ liệu sẽ có đúng 5 cột. Có bao nhiêu cột thì phải có bấy nhiêu tiêu đề ở dòng 1 của sheet data. Code tự phát hiện có bao nhiêu cột - 5 hay 7 hay 10.

- code hiện thời duyệt từng ô trên sheet data. Nếu dòng cột quá nhiều thì dữ liệu nên lấy vào mảng rồi duyệt từng ô của mảng. Nếu bạn muốn thì đây là bài tập về nhà cho bạn.

Toàn bộ code trong Module1

Mã:

Option Explicit

Sub Button1_Click()
Dim lastRow As Long, r As Long, filename As String, filenames(), fso As Object
    With ThisWorkbook.Worksheets("word_files")
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'        lay du them 1 dong sau dong co du lieu cuoi cung
        filenames = .Range("A1:A" & lastRow + 1).Value
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
'    bo dong lay them cuoi cung, chi xet cac dong con lai
    For r = 1 To UBound(filenames) - 1
        filename = ThisWorkbook.Path & "\" & filenames(r, 1)
'        neu tap tin ton tai thi thuc hien
        If fso.FileExists(filename) Then FindAndReplace filename, ThisWorkbook.Worksheets("data")
    Next r
    Set fso = Nothing
End Sub

Private Sub FindAndReplace(ByVal filename As String, ByVal sh As Worksheet)
Const wdReplaceAll = 2
Const wdFindContinue = 1

     Dim num_of_cust As Long, num_of_column As Long, i As Long, j As Long, new_filename As String
     Dim template As Object, t As Object
'     cot cuoi cung co du lieu. Tieu de cac cot tai dong 1 cua sheet
     num_of_column = sh.Cells(1, Columns.Count).End(xlToLeft).Column
'     dong cuoi cung co du lieu o cot A
     num_of_cust = sh.Cells(Rows.Count, "A").End(xlUp).Row - 1
     
     With CreateObject("word.application") ' late binding
        .Visible = True
        For i = 1 To num_of_cust
'            mo tap tin filename cho tung Ho Ten
            Set template = .documents.Open(filename)
            Set t = .Selection
            With t.Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .Forward = True
                .Wrap = wdFindContinue
'                ^c co nghia la text thay the lay tu Clipboard
                .Replacement.text = "^c"
            End With
            For j = 1 To num_of_column
                With t.Find
'                    text can thay the - text can tin
                    .text = sh.Cells(1, j).Value
'                    sao chep text thay the vao Clipboard
                    CopyTextToClipboard sh.Cells(i + 1, j).Value
'                    thay the tat ca cac doan duoc tim thay. Text thay the lay tu Clipboard
                    .Execute Replace:=wdReplaceAll
                End With
            Next j
            new_filename = Left(filename, InStrRev(filename, ".") - 1) & "_" & i & "-don_xin.docx"
'            luu lai voi ten moi
            template.SaveAs new_filename
'            dong tap tin hien hanh
            template.Close
        Next
        .Quit
    End With
    Set t = Nothing
    Set template = Nothing
End Sub

Private Sub CopyTextToClipboard(ByVal text As String)
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText text
        .PutInClipboard
    End With
End Sub

transongngocquan · 21/11/19

Nên dùng Variables trong word hơn là Clipboard
ActiveDocument.Variables.Add(Name:="ho_ten", Value:="Võ Tấn Thành")

Variables, lớn 255 ký tự vẫn dùng được bình thường

batman1 · 21/11/19

transongngocquan đã viết:
Nên dùng Variables trong word hơn là Clipboard
ActiveDocument.Variables.Add(Name:="ho_ten", Value:="Võ Tấn Thành")

Bạn có thể cho code hoàn chỉnh không?

Lưu ý: text cần chèn của người ta có thể là rất dài, > 255 ký tự.

Bạn đã thử với lý do > 255 ký tự chưa? Nếu bạn khẳng định là rồi thì cho tôi xin code.

batman1 · 26/11/19

transongngocquan đã viết:
Nên dùng Variables trong word hơn là Clipboard
ActiveDocument.Variables.Add(Name:="ho_ten", Value:="Võ Tấn Thành")

Variables, lớn 255 ký tự vẫn dùng được bình thường

Thì cho xin code đi.
Tôi không nói chuyện Variables. Hãy cho code thay Lý do khi Lý do > 255 ký tự. Bạn cho 1 dòng code thì chỉ là khoe thôi chứ giúp được gì, cho ai?

Lưu ý: Tôi không nói là bạn không làm được. Nhưng nếu muốn giúp thì cho code, ngược lại thì hãy im lặng. 1 dòng code của bạn không giúp gì được.

phuc12345 · 26/11/19

Em chào cả nhà. Mọi người xem sửa lỗi giúp em với a. Khi bấm code thì nó vẫn chạy bình thường, nhưng khi em thêm dữ liệu (ví dụ em copy dữ liệu là vùng màu đỏ phía bên phải file vào hàng số 13 thì khi bấm code nó lại báo lỗi (lỗi 5152 ).Với lại code này em thấy chạy hơi lâu và viết còn dài dòng, anh chị xem và chỉ bảo thêm cho em với a. Em cám ơn.

transongngocquan · 27/11/19

batman1 đã viết:
Bạn có thể cho code hoàn chỉnh không?

Lưu ý: text cần chèn của người ta có thể là rất dài, > 255 ký tự.

Bạn đã thử với lý do > 255 ký tự chưa? Nếu bạn khẳng định là rồi thì cho tôi xin code.

Tôi đã viết và chạy thử rồi nha, cũng được mà.

batman1 · 27/11/19

transongngocquan đã viết:
Tôi đã viết và chạy thử rồi nha, cũng được mà.

Thì tôi cũng nghĩ là bạn làm được nên mới giới thiệu. Nhưng đấy là bạn làm được còn chủ thớt thì chưa chắc. Bạn có nghĩ là trên cơ sở bài #13 của bạn thì chủ thớt sẽ làm được?

Bạn không trích bài của tôi nên tôi cho là bạn muốn giúp chủ thớt. Mà theo tôi thì bài #13 ngắn gọn quá nên tôi sợ là chủ thớt sẽ không biết làm thế nào.

Thanhvotan · 28/11/19

Dạ em cảm ơn 2 thầy, đó em vừa đi làm vừa đi học nên thời gian tham khảo bài không được nhiều. Bài của thầy Batman em đang nghiên cứu và học rất nhiều VBA lúc trước em chưa hiểu. Còn bài thầy Quang em xin từ từ xem vì sợ tiếp thu không kịp.

Bài tập "Tìm và thay thế VBA"

Thành viên mới

File đính kèm

Thành viên gạo cội

Thành viên mới

Thành viên gạo cội

Ăn cùng góc phố

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên gạo cội

Thành viên mới

Thành viên mới

File đính kèm

Thành viên gạo cội

File đính kèm

Thành viên hoạt động

Thành viên gạo cội

Thành viên gạo cội

Thành viên mới

File đính kèm

Thành viên hoạt động

File đính kèm

Thành viên gạo cội

Thành viên mới

Đếm ngược thời gian