So sánh 3 cách lấy dữ liệu từ 1 file đang đóng

[Maika8008]

Với Excel chúng ta thường có nhu cầu lọc lựa, lấy dữ liệu từ 1 file đang trạng thái đóng đã biết trước đường dẫn và địa chỉ tham chiếu vùng dữ liệu, nhưng lại không muốn tự tay mở nó ra để chép 1 cách thủ công qua file đang làm. Với VBA, chúng ta có 3 cách để tự động hóa khâu lấy dữ liệu này.

Cách 1: Mở trực tiếp file lên, lấy dữ liệu xong đóng lại:

Sub GetDataByOpenFile()
Dim Wb As Workbook, WbS As Workbook
Dim sFullName$, tmr#

tmr = Timer()
Application.ScreenUpdating = False
sFullName = "D:\GoogleDrive2\CaNhan\VBA\MapVN.xlsx"  'Duong dan file du lieu
Set Wb = ThisWorkbook
Set WbS = Workbooks.Open(sFullName)
WbS.Sheets("VNxy").Range("A1:D100").Copy Wb.Sheets("KQ").Range("A1")
WbS.Close False
Application.ScreenUpdating = True
Msgbox Timer() – tmr  'Thoi gian thuc hien
End Sub

- Ưu điểm của cách 1 là trực quan, ta có thể tạm ngừng lệnh, chạy từng bước để xem kết quả trung gian. Dữ liệu có thế nào chép sang thế ấy hoặc có thể tùy ý chép riêng định dạng, công thức, giá trị…

- Nhược điểm cách 1 là thời gian thực thi khá chậm, mất khoảng 1,4 giây cho việc mở file, chép dữ liệu, đóng file. (Thời gian ở đây là trong điều kiện thử nghiệm cụ thể của tác giả, chỉ để so sánh tốc độ thực hiện các cách với nhau. Thời gian đó sẽ khác đi khi dùng ở máy khác, dùng 1 file nguồn khác…)

Cách 2: Mở file bằng ADODB:

Sub GetDataByADODB()
Dim Rec As Object, rs As Object
Dim sFullName$, iCol&, tmr#

tmr = Timer()
sFullName = "D:\GoogleDrive2\CaNhan\VBA\MapVN.xlsx"   'Duong dan file du lieu
Application.ScreenUpdating = False
Set Rec = CreateObject("ADODB.Connection")
With Rec
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & sFullName & ";" & _
              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
End With
        Set rs = Rec.Execute("Select * From [VNxy$A1:D100]")
        Sheets("KQ").Range("A2").CopyFromRecordset rs
        For iCol = 0 To rs.Fields.Count - 1   'Chep tieu de cọt
                Sheets("KQ").Cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
        Set rs = Nothing
        Msgbox Timer() – tmr  'Thoi gian thuc hien
        Application.ScreenUpdating = True
End Sub

- Ưu điểm của cách 2 là thực thi nhanh, chỉ mất cỡ 0,2 giây, và người dùng không nhận biết được file đóng mở.

- Nhược điểm cách 2 là:

+ Trong đa số trường hợp phải mất công khắc phục việc chạy lệnh đối với tiêu đề có dấu tiếng Việt (Có cách khác không bị ảnh hưởng bởi tiêu đề tiếng Việt. Nhưng vấn đề này sẽ được trình bày ở 1 bài khác, có sự so sánh việc sử dụng của 2 cách).

+ Những ai chưa rành cú pháp SQL và lồng các biến vào câu lệnh SQL có thể gặp trở ngại khi điều kiện truy vấn phức tạp.

+ Gặp dữ liệu có dấu phân cách thập phân với các máy đã được dịnh dạng trong Control Panel là dấu phẩy thì sẽ có rắc rối với kết quả chép ra. Thay vì đúng là 144,12 thì kết quả chép ra ở đây là 14412,00.

Để khắc phục nhược điểm này thì phải thêm mã lệnh để chuyển định dạng dấu phẩy thập phân trong Control Panel sang dấu chấm trước khi chạy truy vấn rồi chuyển lại dấu phẩy ngay sau truy vấn.

Khai báo và đặt hàm SetLocalSetting trên đầu Module:

#If VBA7 Then

 Private Declare PtrSafe Function SetLocaleInfo _

 Lib "kernel32" Alias "SetLocaleInfoA" ( _

 ByVal Locale As LongPtr, _

 ByVal LCType As LongPtr, _

 ByVal lpLCData As String) As Boolean

 

 Private Declare PtrSafe Function GetUserDefaultLCID% Lib "kernel32" ()

#Else

 Private Declare Function SetLocaleInfo _

 Lib "kernel32" Alias "SetLocaleInfoA" ( _

 ByVal Locale As Long, _

 ByVal LCType As Long, _

 ByVal lpLCData As String) As Boolean

 

 Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()

#End If

 

 Private Const LOCALE_SDECIMAL = &HE

 

 Private Function SetLocalSetting(LC_CONST As Long, Setting As String) As Boolean

 Call SetLocaleInfo(GetUserDefaultLCID(), LC_CONST, Setting)

 End Function

Với Sub GetDataByADODB() ở trên, thêm lệnh này trước lệnh truy vấn SQL:

Call SetLocalSetting(LOCALE_SDECIMAL, ".")

Và thêm lệnh này ngay sau khi chạy xong truy vấn SQL:

Call SetLocalSetting(LOCALE_SDECIMAL, ",")



Cách 3: Dùng Macro 4. Cách này tôi dùng code từ nguồn:

https://www.giaiphapexcel.com/diendan/threads/dùng-macro-4-để-lấy-dữ-liệu-từ-1-file-đang-đóng.39312/

Sub GetDataByMacro4()
Dim tmr#
tmr = Timer()
  Dim sFile As String, sSheet As String, sAddr As String
  sFile = "D:\GoogleDrive2\CaNhan\VBA\MapVN.xlsx"
  sSheet = "VNxy"
  sAddr = "A1:D100"  
  Sheets("KQ").Range("A1:D100") = GetData(sFile, sSheet, sAddr)
'Kích thuoc phai bang sAddr
  Msgbox Timer() – tmr  'Thoi gian thuc hien
End Sub

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

Với cách này, dù dữ liệu nguồn sAddr = "A1100" nhưng khi muốn hiển thị kết quả ít hơn thì bạn giảm kích thước vùng kết quả chỗ Sheets("KQ").Range("A1100"). Nếu tăng kích thước vùng kết quả thì sẽ bị #N/A ở các ô thừa.

- Thực tình thì không thấy ưu điểm nào của cách này ngoài việc nó lấy đúng số liệu nguồn như cách 1. Còn nhược điểm là quá chậm, phải đến tận 5 giây (gấp hơn 3 lần cách 1) thì mới lấy được dữ liệu cùng cỡ với các cách trên. Dòng tiêu đề sẽ hiển thị là 0 với tiêu đề nào chừa trống.

P/S: Tôi đính kèm file dữ liệu mà tôi đã dùng để test tốc độ thực thi code để các bạn có cùng 1 mẫu thử.
Trong bài có chỗ nào sai, sót thì các bác "gội" rồi mới "cạo" nhẹ nhàng góp ý giúp.

 

[Cá ngừ F1]

Ưu điẻm của 'thực' là nó ... thực.
Khuyết điểm của 'thực', trong môi trường làm việc ở VN, là nó quá cứng nhắc theo guồng máy của VN.

Tôi đã từng nhiều lần nhắc nhỏ, như cái đĩa hát cũ, là làm việc kiểu này là chạy theo dữ liệu chứ không làm chủ nó. (*1)

BI là Business Intelligence, nó ngầm chứa điều kiện trích xuất và phân tích dữ liệu theo dạng cube (nhiều mặt, đa chiều). Dữ liệu chứa trong bảng tính Excel là một mặt, hai chiều, những cái vặn vẹo qua VBA chỉ tối đa giúp cho lắp thêm vây cánh cho Excel một cách cứng nhắc. Code VBA có thể thêm mặt nhìn, thêm chiều, nhưng muốn nó uyển chuyển thì cái project VBA phải lớn tổ bố. Viết cái project tổ bố thì thà học Power Query khoẻ hơn.

(*1) nếu bạn chỉ vì cơm gạo thì thoi. Nhưng nếu muốn có chút thoải mái (tôi tránh dùng từ 'đam mê') thì làm chủ được dữ liệu sẽ thấy cái đẹp, cái hay của chúng, và sẽ thấy thoải mái với công việc thiết kế những dạng bảng tính 'xịn'.

Mỗi môn đều có cái hay. Nếu có một cơ sở dữ liệu chuẩn thì dùng Power Query quá tuyệt, phân tích dữ liệu, sắp xếp tổng hợp dữ liệu lớn, thiết lập các mối quan hệ...
Còn nhiều khi với dữ liệu chưa chuẩn lắm, dùng VBA lại uyển chuyển, mà ngôn ngữ em thấy cũng dễ hiểu, cho kết quả nhanh, các báo cáo tùy biến theo nhu cầu
Nên hiện tại em vẫn kết hợp cả hai trong công việc.

 

[VetMini]

Mỗi môn đều có cái hay. Nếu có một cơ sở dữ liệu chuẩn thì dùng Power Query quá tuyệt {1}, phân tích dữ liệu, sắp xếp tổng hợp dữ liệu lớn, thiết lập các mối quan hệ...
Còn nhiều khi với dữ liệu chưa chuẩn lắm, dùng VBA lại uyển chuyển {2}, mà ngôn ngữ em thấy cũng dễ hiểu, cho kết quả nhanh, các báo cáo tùy biến theo nhu cầu
Nên hiện tại em vẫn kết hợp cả hai trong công việc.

{1} tôi nhường cho các bạn đã rành Power Query trả lời câu này

{2} Đó chính là câu "chạy theo dữ liệu"

 

[Cá ngừ F1]

{1} tôi nhường cho các bạn đã rành Power Query trả lời câu này

{2} Đó chính là câu "chạy theo dữ liệu"

{1} Tất nhiên là Power Query cũng có thể làm sạch dữ liệu, em cũng có theo dõi nhiều bài trên GPE, việc làm sạch cũng cần phải có kiến thức nhất định, không thì làm mãi không thấy sạch.

 

[tranhungdao12a3]

Tôi nghĩ cái món Vba ở đây trên 90% tham gia gpe xem nó như kim chỉ nam rồi, nghĩ nó là nhất rồi thì đâu biết cái khác nó hay nó dở thế nào đâu. Làm cái nhỏ nhỏ thì được. Còn dữ liệu lớn thì nên tìm giải pháp khác. Tôi thấy một số thứ viết vba mướt mồ hôi còn dùng power bi, tableau, python, đại loại rất nhiều thứ xử lý rất đơn giản, nhanh gọn. Biết nhiều càng tốt nhưng đừng cuồng quá một thứ. Tôi nghĩ vậy

 

[Maika8008]

Tôi nghĩ cái món Vba ở đây trên 90% tham gia gpe xem nó như kim chỉ nam rồi, nghĩ nó là nhất rồi thì đâu biết cái khác nó hay nó dở thế nào đâu. Làm cái nhỏ nhỏ thì được. Còn dữ liệu lớn thì nên tìm giải pháp khác. Tôi thấy một số thứ viết vba mướt mồ hôi còn dùng power bi, tableau, python, đại loại rất nhiều thứ xử lý rất đơn giản, nhanh gọn. Biết nhiều càng tốt nhưng đừng cuồng quá một thứ. Tôi nghĩ vậy

Vì đây là giải pháp excel mà bạn! Và nó phù hợp với đa số mới phố thông excel. Chuyện nâng cao hoặc các ngôn ngữ khác ưu việt hơn có mấy người rành để bàn tới.

 

[batman1]

Tôi nghĩ cái món Vba ở đây trên 90% tham gia gpe xem nó như kim chỉ nam rồi, nghĩ nó là nhất rồi thì đâu biết cái khác nó hay nó dở thế nào đâu. Làm cái nhỏ nhỏ thì được. Còn dữ liệu lớn thì nên tìm giải pháp khác. Tôi thấy một số thứ viết vba mướt mồ hôi còn dùng power bi, tableau, python, đại loại rất nhiều thứ xử lý rất đơn giản, nhanh gọn. Biết nhiều càng tốt nhưng đừng cuồng quá một thứ. Tôi nghĩ vậy

Biết càng nhiều càng tốt, vì kiến thức không bao giờ đủ. Nhưng tôi nghĩ số người dùng dữ liệu khủng chỉ là thiểu số. Mà dù thiểu số hay đa số thì cũng có người chỉ làm việc với dữ liệu bình thường. Nếu kiến thức hiện có đủ để làm việc với dữ liệu đó thì khó khuyên họ học cái mới. Vì họ chưa phải làm việc với dữ liệu khủng, sau giờ làm việc nếu có thời gian thì họ muốn giúp vợ / chồng việc nhà, chăm sóc con, trau dồi những kỹ năng khác cấp bách hơn. Nhu cầu có rất nhiều, không đủ thời gian thì phải lựa chọn thôi. Với bạn là phải đầu tư cho cái này, người khác họ có ưu tiên khác.

 

[tranhungdao12a3]

Tôi không tranh luận về việc bị giới hạn nguồn lực. Ý tôi là không nên quá thụ động vào một thứ. Giải quyết vấn đề thì có nhiều cách, ta nên tiếp thu cách mà giúp ta tăng hiệu suất, tiết kiệm thời gian. Tôi nhận thấy các công cụ BI, nó giúp nhiều cho người sử dụng, một số hoạt động cơ bản chỉ cần kéo, thả ví dụ như distinct, sum, average,min, max, count..., công cụ Q&A nó còn tự động phân tích một số thứ dựa trên dataset, và nó sẽ gợi ý cho người sử dụng nếu cần thì chỉ cần kéo thả. Chốt lại thì nên chọn cái nào nhanh gọn, tiết kiệm thời gian, tăng hiệu suất mà không phải mất quá nhiều công sức. Còn ai thấy đủ mà không cần phải thay đổi thì cũng đâu có sao đâu, miễn hài lòng là được rồi.