Kinh Nghiệm về Code VBA ADO lấy và tổng hợp tài liệu từ một file đang đóng 2022
Lê Hoàng Hưng đang tìm kiếm từ khóa Code VBA ADO lấy và tổng hợp tài liệu từ một file đang đóng được Update vào lúc : 2022-04-20 03:53:11 . Với phương châm chia sẻ Bí kíp về trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi Read nội dung bài viết vẫn ko hiểu thì hoàn toàn có thể lại phản hồi ở cuối bài để Tác giả lý giải và hướng dẫn lại nha.
Loại bài toán này đã từng được nói rất nhiều lần trên forum rồi. Hầu hết người ta dùng 2 cách:
Cách 1: Mở file để lấy tài liệu rồi đóng file lại
Cách 2: Dùng ADO (không thấy mở file nhưng vẫn có mở ngầm)
Hôm nay xin ra mắt với bạn 1 cách cực kỳ đơn giản (tự những bạn xây dựng). Bẳng cách dùng Macro 4, những bạn sẽ thấy giải thuật đơn giản đến không ngờ
Tất cả khởi đầu bằng những thí nghiệm sau đây:
- Giả sử ta có file Source.xls và file Main.xls nằm cùng 1 thư mục
- File Source.xls là file chứa nguồn tài liệu
- Ta mở 2 file cùng lúc. Tại file Main, sheet1, cell A1, tôi gõ dấu = , xong tôi dời con trỏ sang file Source.xls rồi nhấp chọn cell A1
- Sau khi thực hiện xong ta được công thức =[Source.xls]Sheet1!$A$1
- Đóng file Source.xls, ta thấy công thức tại cell A1 của file Main.xls đã biến hóa, có thêm đường dẩn tuyệt đối... đại khái thế này
PHP:
='C:Documents and SettingsNHAVDesktopGetData_Test[Source.xls]Sheet1'!$A$1
- Giở ta bật hiệu suất cao record macro lên, chọn cell A1, bấm F2 và Enter... xong, tắt record macro và Alt + F11 để xem code, ta sẽ thấy code có dạng thế này:
PHP:
Sub Macro1() ' ' Macro recorded 17/8/2010 by Anh Tu?n ' ' Range("A1").Select ActiveCell.FormulaR1C1 = _ "='C:Documents and SettingsNHAVDesktopGetData_Test[Source.xls]Sheet1'!R1C1" Range("A2").Select End Sub
- Chỉnh sửa code lại 1 tí:
PHP:
Sub Macro1() Range("A1") = "='C:Documents and SettingsNHAVDesktopGetData_Test[Source.xls]Sheet1'!R1C1" End Sub
- Code này thao tác làm gán công thức link đến file Source.xls vào cell A1 của file Main.xls- Để chuyển công thức thành giá trị, ta có 2 cách
Cách 1:
PHP:
Sub Macro1() With Range("A1") .Value = "='C:Documents and SettingsNHAVDesktopGetData_Test[Source.xls]Sheet1'!R1C1" .Value = .Value End With End Sub
Cách 2:
PHP:
Sub Macro1() Range("A1") = ExecuteExcel4Macro("'C:Documents and SettingsNHAVDesktopGetData_Test[Source.xls]Sheet1'!R1C1") End Sub
- Và cách 2 này đó đó là phương pháp dùng macro 4 để lấy tài liệu ở file đang đóng----------------------------------------------------------
- Hãy nghiên cứu và phân tích chuổi "'C:Documents and SettingsNHAVDesktopGetData_Test[Source.xls]Sheet1'!R1C1"
Ta thấy nó có cú pháp thế này:
"'" & đường dẩn & "[" Tên file & "]" & Tên sheet & "'!" & địa chỉ cell
- Vậy hãy xây dựng 1 code nhờ vào phương pháp lấy tài liệu này nhé
PHP:
Function GetData(sFile As String, sSheet As String, sAddr As String) Dim pLink As String, iR As Long, iC As Long, Arr If Len(Dir(sFile)) Then Arr = Range(sAddr) pLink = "'" & Replace(sFile, Dir(sFile), "[" & Dir(sFile) & "]") & sSheet & "'!" For iR = 1 To Range(sAddr).Rows.Count For iC = 1 To Range(sAddr).Columns.Count Arr(iR, iC) = ExecuteExcel4Macro(pLink & Range(sAddr).Cells(iR, iC).Address(, , 2)) Next iC Next iR GetData = Arr End If End Function
- Hàm đã có, giờ để lấy tài liệu vùng A1
12 tại sheet1 của file Source.xls, ta viết thêm code này:
PHP:
Sub Test() Dim sFile As String, sSheet As String, sAddr As String sFile = ThisWorkbook.Path & "Source.xls" sSheet = "Sheet1" sAddr = "A1:D100" Range("A1:D12") = GetData(sFile, sSheet, sAddr) End Sub
-----------------------------Ứng dụng mở rộng: Nạp list cho ComboBox, với tài liệu được lấy từ file đang đóng
- Giả sử ta có sẳn 1 ComboBox, tên ComboBox1
- Dữ liệu cần lấy nằm ở vùng H1:H10 của sheet1, file Source.xls
- Ta viết code sau:
PHP:
Sub AddList() Dim sFile As String, sSheet As String, sAddr As String sFile = ThisWorkbook.Path & "Source.xls" sSheet = "Sheet1" sAddr = "H1:H10" Sheet1.ComboBox1.List() = GetData(sFile, sSheet, sAddr) End Sub
Sau khi chạy code, hãy nhấn vào ComboBox xem thử------------------------------------
Ưu điểm và nhược điểm:
- Ưu điểm của phương pháp dùng macro 4 này là code đơn giản, lại hoàn toàn không mở file tí nào (trong cả ADO, tuy không thấy mở file nhưng thực chất nó cũng mở ngầm)
- Với ADO hoặc phương thức mở file trực tiếp, dù không biết tên sheet thì ít nhất ta cũng lấy được tài liệu tại sheet đầu tiên
- Với phương pháp dùng Macro 4, nên phải biết trước tên sheet
- Còn ưu điểm hay nhược điểm gì nữa.. tất cả chúng ta cùng nghiên cứu và phân tích thêm nhé
------------------------------------
Hãy tham khảo file đính kèm và cảm nhận nhé! Đơn giản không nào?https://www.giaiphapexcel.com/diendan/attachments/getdata_test-rar.164453/
Thì code này đây:
PHP:
Sub Test() Dim sFile As String, sSheet As String, sAddr As String sFile = ThisWorkbook.Path & "Source.xls" sSheet = "Sheet1" sAddr = "A1:D100" Range("A1:D12") = GetData(sFile, sSheet, sAddr) End Sub
Dùng để copy ra sheet phụ chứ còn gì nữa... Bạn làm như sau:- Ở code trên, hãy sửa Source.xls thành Data.xls
- Sửa Sheet1 thành Dinhmuc
- Sửa A1
12 thành A1:F58- Chèn thêm 1 sheet, chạy code vừa sửa là có ngay tài liệu thôi
-----------------
Sau khi đã cho tài liệu vào cùng 1 file thì phần còn sót lại đó là một trong bài toán khác rồi (sẽ xử lý và xử lý ở 1 topic khác, đồng ý không?)
Điều này sẽ không bắt buộc, tôi để cùng 1 thư mục để tiện cho quá trình thí nghiệm thôiNếu file của bạn nằm ở thư mục khác, cứ ghi đường dẫn đúng chuẩn vào là được rồiVí dụ đoạn code này:
PHP:
Sub Test() Dim sFile As String, sSheet As String, sAddr As String sFile = ThisWorkbook.Path & "Source.xls" sSheet = "Sheet1" sAddr = "A1:D100" Range("A1:D12") = GetData(sFile, sSheet, sAddr) End Sub
Nếu file Source.xls nằm ở 1 thư mục khác (D:BaitapSource.xls ví dụ điển hình) thì sửa code thành:
PHP:
Sub Test() Dim sFile As String, sSheet As String, sAddr As String sFile = "D:BaitapSource.xls" sSheet = "Sheet1" sAddr = "A1:D100" Range("A1:D12") = GetData(sFile, sSheet, sAddr) End Sub
Vậy thôi
Suy nghĩ vậy không biết đúng không , xin cho biết thêm thêm ý kiến bạn
Đối với trường hợp lấy tài liệu từ Workbook đang đóng, tôi nghĩ nếu đưa vào mảng sẽ chậm hơn so với cách đưa trực tiếpCác code ở trên là dùng mảng đấy thôi!Còn đây là cách không dùng mảng và vòng lập
PHP:
Sub GetData(sFile As String, sSheet As String, sAddr As String, Target As Range) Dim pLink As String If Len(Dir(sFile)) Then pLink = "'" & Replace(sFile, Dir(sFile), "[" & Dir(sFile) & "]") & sSheet & "'!" With Range(sAddr) With Target.Resize(.Rows.Count, .Columns.Count) .FormulaArray = "=" & pLink & sAddr .Value = .Value End With End With End If End Sub
PHP:
Sub Test() Dim sFile As String, sSheet As String, sAddr As String sFile = ThisWorkbook.Path & "Source.xls" sSheet = "Sheet1" sAddr = "A1:C10000" GetData sFile, sSheet, sAddr, Range("A1") End Sub
Cách này cho tốc độ nhanh hơn, tuy nhiên vẫn có số lượng giới hạn (số lượng giới hạn bao nhiêu tôi chưa chắc như đinh) ---> Nếu tăng vùng tài liệu cần lấy (ví dụ điển hình A1:C60000) thì code sẽ báo lỗiCòn việc tăng dung tích gì đó thì... có sao đâu ---> Sao khi xong việc, bạn xóa tài liệu đi là xong chứ gì!Đương nhiên chỉ có cách dùng ADO là hầu như không biến thành số lượng giới hạn gì cả nhưng ác cái là code rất khó nhớ
[embed]https://www.youtube.com/watch?v=jZWFk_xmjr8[/embed]