Bài viết: Dùng Macro 4 để lấy dữ liệu từ 1 file đang đóng

Liên hệ QC

anhtuan1066

Thành viên gạo cội
Tham gia
10/3/07
Bài viết
5,802
Được thích
6,905
Loại bài toán này đã từng được nói rất nhiều lần trên diễn đàn rồi. Hầu hết người ta dùng 2 cách:
Cách 1: Mở file để lấy dữ 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 giới thiệu với bạn 1 cách cực kỳ đơn giản (tự các bạn xây dựng). Bẳng cách dùng Macro 4, các bạn sẽ thấy giải thuật đơn giản đến không ngờ
Tất cả bắt đầ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 dữ 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 đổi, có thêm đường dẩn tuyệt đối... đại khái thế này
PHP:
='C:\Documents and Settings\NHAV\Desktop\GetData_Test\[Source.xls]Sheet1'!$A$1
- Giở ta bật chức năng record macro lên, chọn cell A1, bấm F2Enter... 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 Settings\NHAV\Desktop\GetData_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 Settings\NHAV\Desktop\GetData_Test\[Source.xls]Sheet1'!R1C1"
End Sub
- Code này làm công việc gán công thức liên kết đế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 Settings\NHAV\Desktop\GetData_Test\[Source.xls]Sheet1'!R1C1"
    .Value = .Value
  End With
End Sub
Cách 2:
PHP:
Sub Macro1()
  Range("A1") = ExecuteExcel4Macro("'C:\Documents and Settings\NHAV\Desktop\GetData_Test\[Source.xls]Sheet1'!R1C1")
End Sub
- Và cách 2 này chính là phương pháp dùng macro 4 để lấy dữ liệu ở file đang đóng

----------------------------------------------------------
- Hãy nghiên cứu chuổi "'C:\Documents and Settings\NHAV\Desktop\GetData_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 dựa trên phương pháp lấy dữ 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 dữ liệu vùng A1:D12 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 dữ 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 bấm 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 (ngay 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 dữ liệu tại sheet đầu tiên
- Với phương pháp dùng Macro 4, bắt buộc phải biết trước tên sheet
- Còn ưu điểm hay nhược điểm gì nữa.. chúng ta cùng nghiên cứu 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?
------------------------------------
Lưu ý quan trọng: Tải file về, phải giải nén ra rồi hẳn chạy, nếu không code chẳng hoạt động đâu nha

Một số bài viết có liên quan:
1/ Viết hàm VBA như thế nào là tốt
2/ Cách chạy macro trong sheet bị protect
3/ Gọi một Private Sub
4/ Phương thức SpecialCells trong Excel VBA (P2)
5/ Phương thức SpecialCells trong Excel VBA (P1)
6/ Thuộc tính End() trong Excel - giới thiệu tổng quan (P2)
7/ Thuộc tính End() trong Excel - giới thiệu tổng quan (P1)
8/ Tổng hợp về phương thức tìm kiếm FIND (P2)
9/ Tổng hợp về phương thức tìm kiếm FIND (P1)
10/ Toán tử Like trong VBA và các ký tự đặc biệt
 

File đính kèm

  • GetData_Test.rar
    15.1 KB · Đọc: 19
Chỉnh sửa lần cuối bởi điều hành viên:
Upvote 0
Đơn giản hiệu quả --=0 cảm ơn nhìu @$@!^%
 
sao tôi mở macro trong ppt ko đc vậy? Dù đã làm theo một số cách như trên mạng. Làm ơn chỉ dùm mình nhé. Thanks.
 
- Cách này chỉ dùng để lấy dữ liệu thôi phải các bác, nếu ngược lại muốn dùng Macro 4 để nạp dữ liệu vào file đang đóng có được không ah.
 
Có cách nào để copy hết data từ file source vào, thay vì chỉ có 12 dòng như trong ví dụ, cảm ơn.
 

có cách nào có thể sử dụng luôn hàm getdata trên sheet luôn không NDU?
ví dụ muốn lấy giá trị của cell A1, trong sheet dang đóng
gõ =Getdata(duongdan, tenfile,cell)
hình như phải sử dụng sub, không sử dụng trực tiếp trên hàm phải không anh?
 
có cách nào có thể sử dụng luôn hàm getdata trên sheet luôn không NDU?
ví dụ muốn lấy giá trị của cell A1, trong sheet dang đóng
gõ =Getdata(duongdan, tenfile,cell)
hình như phải sử dụng sub, không sử dụng trực tiếp trên hàm phải không anh?

Nếu gõ trực tiếp lên cell thì đâu cần hàm tự tạo hả bạn?
Bạn có thể gõ theo cách bình thương nhất, ví dụ:
- Bạn có file Source.xls nằm trong thư mục D:\Excel
- File Source.xls này có dữ liệu tại Sheet1, vùng A1:D12
- Vậy để lấy dữ liệu trên, bạn cứ quét chọn vùng 4 cột 12 dòng rồi gõ vào thanh Formula công thức:
Mã:
='D:\Excel\[Source.xls]Sheet1'!$A$1:$D$12
Bấm Ctrl + Shift + Enter là xong
 
Nếu gõ trực tiếp lên cell thì đâu cần hàm tự tạo hả bạn?
Bạn có thể gõ theo cách bình thương nhất, ví dụ:
- Bạn có file Source.xls nằm trong thư mục D:\Excel
- File Source.xls này có dữ liệu tại Sheet1, vùng A1:D12
- Vậy để lấy dữ liệu trên, bạn cứ quét chọn vùng 4 cột 12 dòng rồi gõ vào thanh Formula công thức:
Mã:
='D:\Excel\[Source.xls]Sheet1'!$A$1:$D$12
Bấm Ctrl + Shift + Enter là xong

mục đích giống vlookup với sheet không cố định
ví dụ như vậy
mã spgiá trị tìm
010909 aaaaABaE:\New folder\[filedong.xlsx]09'!$E$5
020909bbbbE:\New folder\[filedong.xlsx]09'!$E$6
051009rorororE:\New folder\[filedong.xlsx]10'!$E$9

sheet sẻ thay đổi tuy mình đưa số liệu vào
giải thích thêm là 2 dòng đầu lấy ở sheet "09"
dòng cuối lấy sheet "10"
 
Lần chỉnh sửa cuối:
giờ em muốn kết hợp mở file ngầm với copy dữ liệu sang một file khác (file này có một button kích hoạt thêm 1 sheet mới), dữ liệu trong sheet file ngầm kia sẽ được lấy vào sheet mới này. Nhưng phải kết hợp code vào như thế nào?? làm cách nào gán vào 1 nút mà ko cần tạo thêm nút nữa ạ
 
Chào anh Tuấn,

Ý tưởng về việc lấy dữ liêu từ file đang đóng của anh rất hay.
Tuy nhiên, khi em lấy dữ lieu từ 1 cell thì không được. Em đã cố gang điều chỉnh code nhưng vẫn không được. Anh có thể tùy biến để cho có thể lấy được dữ lieu từ 1 cell không?
Cảm ơn anh nhiều.
 
Lấy dữ liệu 1 Cells có 2 kiểu
1/ Là kiểu: R1C6
2/ Là kiểu: [A1]

Bạn coi bài 1 là làm được kiếu 1
http://www.giaiphapexcel.com/diendan/threads/118101.Dùng-Macro-4-để-lấy-dữ-liệu-từ-1-file-đang-đóng

Còn muốn lấy 1 cells theo kiểu 2 thì sửa lại code...là dòng sau nè

PHP:
Range(sAddr).Cells(iR, iC).Address(, , 2))

À quên quên dòng này nữa
PHP:
='C:\Documents and Settings\NHAV\Desktop\GetData_Test\[Source.xls]Sheet1'!$A$1
 
Lần chỉnh sửa cuối:
Lấy dữ liệu 1 Cells có 2 kiểu
1/ Là kiểu: R1C6
2/ Là kiểu: [A1]

Bạn coi bài 1 là làm được kiếu 1
http://www.giaiphapexcel.com/diendan/threads/118101.Dùng-Macro-4-để-lấy-dữ-liệu-từ-1-file-đang-đóng

Còn muốn lấy 1 cells theo kiểu 2 thì sửa lại code...là dòng sau nè

PHP:
Range(sAddr).Cells(iR, iC).Address(, , 2))

À quên quên dòng này nữa
PHP:
='C:\Documents and Settings\NHAV\Desktop\GetData_Test\[Source.xls]Sheet1'!$A$1

Cảm ơn bạn kieu manh.
Hôm nay minh đã sửa lại được code rồi. Hôm qua excel bị làm sao đó mà VBA báo lỗi.
 
Web KT
Back
Top Bottom