Cách sử dụng hàm trong VBA để trích xuất một giá trị cụ thể từ một tệp đang đóng

Chuyên mục:
Excel VBA

Trong VBA không có cách nào để truy xuất một giá trị cụ thể từ một tệp đang đóng. Tuy vậy, bạn có thể tận dụng khả năng hoạt động với các tệp có liên kết khác của Excel.

Dưới đây là một hàm chức năng trong VBA giúp bạn thực hiện được thao tác trên. Phương thức hoạt động của nó là nhờ một macro chạy hệ mã XLM.

Lưu ý: Hàm này sẽ không hoạt động được trong thanh công thức của trang tính

Hàm GetValue

Hàm GetValue sẽ xác định bốn yếu tố sau:

Đường dẫn: Đường dẫn cũng như ổ nhớ chứa tệp đóng cần tìm (ví dụ, “D:/Files”)
Tên tệp: Tên tệp cần tìm (ví dụ, “budget.xls”)
Trang tính: Tên trang tính của tệp (ví dụ, “Sheet1”)
Tham số: Ô tính cần tham chiếu (ví dụ, ô “C4”)

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Cách sử dụng hàm GetValue

Để sử dụng hàm này, hãy sao chép nó vào mô-đun VBA. Sau đó điền nội dung của hàm với các yếu tố cần xác định như trên. Quy trình cách thức hoạt động cụ thể có thể tham khảo ở dưới đây. Chẳng hạn, hàm này sẽ cho ra kết quả là giá trị trong ô tính A1 thuộc trang Sheet1, tệp Budget.xls có đường dẫn trong ổ C:/XLFiles/Budget.

Sub TestGetValue()
    p = "c:\XLFiles\Budget"
    f = "Budget.xls"
    s = "Sheet1"
    a = "A1"
    MsgBox GetValue(p, f, s, a)
End Sub

Một ví dụ khác được minh họa dưới đây. Dòng lệnh sẽ phân tích liên tục 1200 giá trị khác nhau có trong 1 tệp đóng (bao gồm 100 hàng và 12 cột), và sau đó sẽ gán các giá trị đó vào bảng tính đang hoạt động.

Sub TestGetValue2()
    p = "c:\XLFiles\Budget"
    f = "Budget.xls"
    s = "Sheet1"
    Application.ScreenUpdating = False
    For r = 1 To 100
        For c = 1 To 12
            a = Cells(r, c).Address
            Cells(r, c) = GetValue(p, f, s, a)
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

Lỗi có thể gặp phải

Để hàm này có thể hoạt động hiệu quả, yêu cầu phải có một bảng tính đang được mở trong Excel. Sẽ có thông báo lỗi xuất hiện nếu như tất cả các cửa sổ đều đang ẩn, hoặc Excel đang mở một trang chứa bảng biểu.