Cần giúp đỡ về lấy dự liệu từ một file excell mà ko cần mở file đó (1 người xem)

[nguyenvanhuannlbk]

Chào cả nhà!
Hiện tại mình đang xây dựng thêm các hàm trong excel phục phụ riêng cho công việc của mình.
một số đầu vào tính toán cho các hàm mình muốn xây dựng thành các bảng ở một file excel (data) để khi các giá trị này thay đổi mình dễ cập nhật. vấn đề là hiện tại mình chưa thực hiện được việc lấy một giá trị nào đó (có điều kiện) từ file data (file này không được mở) để làm đầu vào tính toán cho hàm mình muốn xây dựng. mình đã thử viết đoạn code về một chương trình con thực hiện lấy dự liệu nhưng kết quả báo lỗi. Mong mọi người chỉ cho mình doạn code sau có vấn đề gì chưa ổn nhé.
Public Function vidu(i As Integer, j As Integer) As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim st As Range
Set wb = Application.Workbooks.Open("E:\LAPTRINH\data.xlsx")
Set ws = wb.Worksheets(Sheet1)
Set st = ws.Range("B6:L10")
vidu = Val(st.Cells(i, j).Value)
End Function

 

[ndu96081631]

Chào cả nhà!
Hiện tại mình đang xây dựng thêm các hàm trong excel phục phụ riêng cho công việc của mình.
một số đầu vào tính toán cho các hàm mình muốn xây dựng thành các bảng ở một file excel (data) để khi các giá trị này thay đổi mình dễ cập nhật. vấn đề là hiện tại mình chưa thực hiện được việc lấy một giá trị nào đó (có điều kiện) từ file data (file này không được mở) để làm đầu vào tính toán cho hàm mình muốn xây dựng. mình đã thử viết đoạn code về một chương trình con thực hiện lấy dự liệu nhưng kết quả báo lỗi. Mong mọi người chỉ cho mình doạn code sau có vấn đề gì chưa ổn nhé.
Public Function vidu(i As Integer, j As Integer) As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim st As Range
Set wb = Application.Workbooks.Open("E:\LAPTRINH\data.xlsx")
Set ws = wb.Worksheets(Sheet1)
Set st = ws.Range("B6:L10")
vidu = Val(st.Cells(i, j).Value)
End Function

Đơn giản là thế này:
- Bạn đưa lên đây 2 file: 1 file gốc và 1 file kết quả
- Ghi rõ bạn mong muốn làm điều gì
Vậy là xong!
(Có file ta sẽ bàn tiếp, còn bạn cứ mô tả chung chung thì cứ.. CHỜ nhé)

 

[nguyenvanhuannlbk]

em gửi các bác hai file này nhé

 

[ndu96081631]

em gửi các bác hai file này nhé

Vậy thì cần quái gì code với kiết
Công thức cho bạn:

=INDEX([data.xlsx]Sheet1!$B$6:$L$10,B3,C3)

 

[nguyenvanhuannlbk]

cái hàm mà mình đang viết chỉ đểm kiểm tra xem nó lấy giá trị trong bảng kia có đúng không. từ đó mình sẽ thay đổi một chút và dùng nó làm một hàm con cho các hàm tính tính toán khác. tuy thuộc vào yêu củ thể mà mình sẽ lấy một giá trị một ô bất kỳ để tính toán

 

[ndu96081631]

cái hàm mà mình đang viết chỉ đểm kiểm tra xem nó lấy giá trị trong bảng kia có đúng không. từ đó mình sẽ thay đổi một chút và dùng nó làm một hàm con cho các hàm tính tính toán khác. tuy thuộc vào yêu củ thể mà mình sẽ lấy một giá trị một ô bất kỳ để tính toán

Muốn vậy thì bạn dùng ADO đi. Nó sẽ lấy dữ liệu từ file đang đóng mà không cần mở file. Từ dữ liệu lấy được, bạn thích làm gì nữa thì cứ tùy thích
Ví dụ dùng ADO thế này:

Function GetData(ByVal FileName As String, ByVal SheetName As String, ByVal RangeAddress As String)
            
  Dim rsCon As Object, rsData As Object, cat As Object, tbl As Object, aTmp
  Dim szConnect As String, szSQL As String, tmp As String
  Dim lVer As Long
  lVer = Val(Application.Version)
  Set rsCon = CreateObject("ADODB.Connection")
  Set rsData = CreateObject("ADODB.Recordset")
  Set cat = CreateObject("ADOX.Catalog")
  If lVer < 12 Then
    szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";" & _
                "Extended Properties=""Excel 8.0;HDR=No"";"
  Else
    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";" & _
                "Extended Properties=""Excel 12.0;HDR=No"";"
  End If
  If SheetName = "" Then
    Dim Dbs  As Object, db As Object
    Set Dbs = CreateObject("DAO.DBEngine." & IIf(lVer < 12, "36", "120"))
    Set db = Dbs.OpenDatabase(FileName, False, False, "Excel 8.0;")
    tmp = db.TableDefs(0).Name
    tmp = Replace(tmp, " ", "?")
    tmp = Replace(tmp, "'", " ")
    tmp = WorksheetFunction.Trim(tmp)
    tmp = Replace(tmp, " ", "'")
    tmp = Replace(tmp, "?", " ")
    SheetName = tmp
    db.Close
    Set Dbs = Nothing: Set db = Nothing
  End If
  If Right(SheetName, 1) <> "$" Then SheetName = SheetName & "$"
  rsCon.Open szConnect
  cat.ActiveConnection = rsCon
  
  szSQL = "SELECT * FROM [" & SheetName & RangeAddress & "];"
  rsData.Open szSQL, rsCon, 0, 1, 1
  aTmp = rsData.GetRows
  rsData.Close: Set rsData = Nothing
  rsCon.Close: Set rsCon = Nothing
  GetData = aTmp
End Function

Từ hàm trên, bạn viết thêm hàm vidu của bạn thế này:

Public Function vidu(ByVal lR As Long, ByVal lC As Long)
  Dim FileName As String, SheetName As String, RangeAddress As String
  Dim arr
  On Error Resume Next
  FileName = "E:\LAPTRINH\data.xlsx"
  SheetName = ""
  RangeAddress = "B6:L10"
  arr = GetData(FileName, SheetName, RangeAddress)
  vidu = arr(lC - 1, lR - 1)
End Function

Vậy là xong!
Gõ công thức =vidu(B3,C3) trên bảng tính, sẽ cho kết quả = 2.5

 

[nguyenvanhuannlbk]

cảm ơn bác ndu96081631 nhé