Cách lấy dữ liệu của File Excel đang đóng bằng VBA

Như trong một bài viết trước đây, mình có nói về một ví dụ ứng dụng để đọc dữ liệu của Workbook đang đóng từ một Workbook đang mở bằng VBA. Trong bài viết này, mình sẽ chia sẻ với bạn rõ và chi tiết hơn về ví dụ đó.

Vậy để thực hiện ví dụ khá thực tế này, bạn sẽ cần những chi thông tin sau:

1. Cách sử dụng Macro và VBA: gán Macro vào nút nhấn.
2. Sử dụng Hàm With -End With: để mở hộp thoại duyệt tập tin FILEDIALOG.
3. Cách sử dụng Workbook: mở và đóng một Workbook bằng VBA.
4. Thao tác dữ liệu  Worksheet, Range, Cells: sao chép và ghi dữ liệu

Đọc dữ liệu từ File Excel đang đóng bằng VBA

Đầu tiên, bạn cần xác định trước các phần của Workbook đang đóng như sau:

• Thư mục lưu trữ các File cần lấy dữ liệu.
• Định dạng của File Excel.
• Vị trí Range, Cells chứa giá trị cần lấy dữ liệu.

Tiếp theo, bạn tiến hành lập mẫu lưu dữ liệu trên Workbook hiện hành. Bạn có thể tham khảo 1 mẫu như sau:

Viết hàm VBA đọc dữ liệu từ Workbook đang đóng và lưu vào Workbook hiện hành.

Hoàn thành xác định các thành phần của Workbook cần lấy và lưu dữ liệu. Tiếp theo, bạn sẽ thực hiện tạo hàm VBA trong Workbook hiện hành. Bạn có thể tạo nó trong Module hoặc Sheets.

Hàm layduongdan() sẽ có chức năng là mở hộp thoại duyệt tập tin theo thư mục định sẵn trong thuộc tính InitialFileName, với bộ lọc các File định đạng .xls?.

💡 Định dạng File .xls? sẽ lọc ra tất cả các File Excel bao gồm (.xlsx, .xlsm, .xls…).

Public Sub layduongdan()
Dim filedlg As FileDialog
Set filedlg = Application.FileDialog(msoFileDialogFilePicker)
With filedlg
.AllowMultiSelect = False
.InitialFileName = "D:\OneDrive\9.MMO\Post\File-du-lieu-mau" & "\"
.Filters.Clear
.Filters.Add "Excel", "*.xls?"
If .Show = True Then
docdulieu (.SelectedItems(1))
End If
End With
Set filedlg = Nothing
End SubCode language: VB.NET (vbnet)

Khi đã chọn 1 File, hàm sẽ tự động gửi đường dẫn File đến biến duongdan của Hàm docdulieu().

Hàm docdulieu() sẽ thực thi khi được Hàm 1 gọi. Từ duongdan được gửi đến, hàm sẽ tự động mở File (Readonly). Đọc dữ liệu và sau đó ghi các giá trị vào Workbook hiện hành. Kết thúc, thực hiện đóng Workbook và chuyển biến wb_close về rỗng để chờ lần thực hiện kế tiếp.

Sub docdulieu(duongdan As String)
Dim lstRow As Long
Dim wb_active As Workbook
Dim ws_active As Worksheet
Dim wb_close As Workbook
Dim ws_close As Worksheet
Set wb_active = ActiveWorkbook
Application.ScreenUpdating = False
Set wb_close = Workbooks.Open(duongdan, True, True)
Set ws_close = wb_close.Sheets(1)
Set ws_active = wb_active.Sheets(1)
' ghi du lieu
lstrow = ws_active.Range("A" & Rows.Count).End(xlUp).Row + 1
ws_active.Cells(lstrow, 1).Value = duongdan
ws_active.Cells(lstrow, 2).Value = wb_close.Name
ws_active.Cells(lstrow, 3).Value = ws_close.Name
ws_active.Cells(lstrow, 4).Value = Date
'doc du lieu
ws_active.Cells(lstrow, 5).Value = ws_close.Cells(3, 1).Value
ws_active.Cells(lstrow, 6).Value = ws_close.Cells(3, 2).Value
ws_active.Cells(lstrow, 7).Value = ws_close.Cells(3, 3).Value
ws_active.Cells(lstrow, 8).Value = ws_close.Cells(3, 4).Value
ws_active.Cells(lstrow, 9).Value = ws_close.Cells(3, 5).Value
'---------------
wb_close.Close
Set wb_close = Nothing
End SubCode language: VB.NET (vbnet)

👉 Lệnh Application.ScreenUpdating = False được dùng để ngăn màn hình Excel nhấp nháy khi thực thi lệnh mở/ đóng Workbook.

Hoặc bạn có thể thay đoạn lệnh ‘doc du lieu bằng lệnh Copy dữ liệu như sau:

ws_close.Range("A3:E3").Copy Destination:=ws_active.Range(Cells(lstrow, 5), Cells(lstrow, 9))Code language: VB.NET (vbnet)

Tiếp theo, để thuận tiện bạn hãy tạo một nút nhấn để liên kết Hàm layduongdan().

💡 Để tạo nút nhấn, bạn có thể tạo bằng cách vào Insert ⇒ Shape ⇒ chọn 1 hình ⇒ vẽ. Sau đó, bạn chuột phải vào nút nhấn vừa tạo, chọn Assign Macro và liến kết đến Macro layduongdan().

Cách sử dụng các hàm vừa tạo để đọc dữ liệu:

👍 Click chuột vào nút nhấn ⇒ chọn một File ⇒ chọn OK hoặc (Double Click) ⇒ các hàm VBA sẽ tự động đọc dữ liệu.

Kết quả: Sau mỗi lần thực hiện, Excel sẽ tự động lấy các dữ liệu được bạn lập trình và ghi vào File hiện hành.

💡 Bạn có thể tùy chỉnh thêm để tự động đọc dữ liệu từ tất cả các File trong một thư mục.

Ngoài ra, bạn còn có thể sử dụng cách tạo kết nối đến Workbook đang đóng để đọc dữ liệu.