Kiểm tra sự tồn tại của một sheet khi không mở file

Chia sẻ bởi:hands

★★★★★

‹ Tổ hợp trong excel Làm nhãn vở học sinh trên Excel ›

Quảng cáo

Chào các anh chị trên diễn đàn.
Tôi có điều cần trợ giúp: Muốn kiểm tra sheetname có tồn tại trong filename.xls ở thư mục C:MYFOLDER mà không mở file.
Xin cảm ơn.

Mở 1 file exxcel bất kỳ. gõ " =C:MYFOLDERSheetname'!$B$4"
Nếu sheet không có Excel sẽ hiện bảng báo update hơạc báo "#REF"

Xin hỏi bạn : tại sao phải yêu cầu " không mở file " –> có nhất thiết phải như vậy không ?
Hay chỉ cần đơn giản :

Workbooks.Open " đường dẫn"
For ws in worksheets
If ws.name = "…" then
next
Workbooks.close(..)

Còn nếu bắt buộc không mở file theo cách bạn muốn –> vọc thêm về topic ADO trên diễn đàn

Thực ra yêu cầu của tác giả là bất khả thi… trừ khi chúng ta có phép, mà có phép thì vẫn cứ phải mở file – không bằng cách này thì cách khác.
Muốn biết trong túi có gì thì: (1) Mở miệng túi ra (2) Dùng máy Soi (vẫn phải mở nhỉ) (3) Thò tay vào túi.
Muốn biết sheet nào đó có tồn tại trong tập tin excel không thì chả có cách nào khác ngoài việc phải mở nó ra. Có chăng thì mở bằng chương trình nào mà thôi.
Trong phạm vi hỏi của tác giả, thì tôi đoán không phải dùng các biện pháp quá cao siêu như dùng trình soạn thảo khác Excel để mở mà có thể dùng Excel mở nó ra, kiểm tra xong không có thì đóng (tất nhiên còn nhiều công cụ khác nữa).
Để làm được yêu cầu của tác giả thì chắc dùng VBA (lập trình) trong chính Excel thôi. Nếu tác giả lại nói là không muốn mở Excel ra thì chắc phải áp dụng các giải pháp cao siêu khác rồi…Trong trường hợp này, tác giả cần đưa ra yêu cầu cụ thể hơn…
Xin có vài dòng lãng đãng thế.

Muốn biết tim tức về một sheet thì có thể tra cứu cấu hình hạ tầng của file (metastructure).
Có nhiều cách để tra cứu. Cách dễ nhất là lợi dụng các Objects của Windows.
File Excel có thể được thông qua bởi một trong hai Objects: Data Access Objects (DAO) và ActiveX Data Objects (ADO).
Chịu khó tra cứu bài "Đố Vui Về DAO và ADO" của Hai Lúa Miền Tây bên hộp CSDL.

Làm cho Excel2003, các bạn test thử và mở rộng thêm:

Function SheetExists(ByVal strExcelFullName As String, ByVal strSheetName As String) As Boolean
    SheetExists = False
    On Error GoTo ErrorHandler
    Dim objExcel As Object
    Set objExcel = CreateObject("ADODB.Recordset")
    Dim FileExists As Boolean
    FileExists = (GetAttr(strExcelFullName) And vbNormal) = vbNormal
    Dim sAppEName As String

If FileExists Then
        sAppEName = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & strExcelFullName
        objExcel.Open "SELECT * FROM [" & strSheetName & "$]", sAppEName
        SheetExists = True
    End If
    Exit Function
ErrorHandler:
    If Not FileExists Then MsgBox "Error Description : " & Err.Description
End Function

Ví dụ:

Sub test()
    MsgBox SheetExists(ThisWorkbook.Path & "Test.xls", "Sheet2")
End Sub

các bạn test thử và mở rộng thêm:

Chú ý:
– Tên file, tên sheet là tiếng Việt có dấu
– Nếu người ta bỏ đối số TÊN FILE thì đồng nghĩa muốn nói đến file hiện hành

Cảm ơn các bác đã quan tâm.
1. Có thể em dùng từ chưa được chính xác. Yêu cầu chính xác là "…mà không nhìn thấy mở file"
2. Mô tả công việc cụ thể thêm: Tổng hợp từ nhiều file vào 1 file
– Từ file tổng hợp chọn thư mục chứa các file.
– Dùng công thức dạng link (=thumucsheet!range)
– Bỏ công thức (value=value)

Tức là lấy dữ liệu từ file đang đóng!
Ngay từ đầu mô tả vậy luôn đi, chứ cái gì mà kiểm tra sheet tồn tại. Bạn cứ hướng người ta đi theo con đường tầm bậy tầm bạ (mà bạn cứ cho là đúng)
Tóm lại: Yêu cầu cuối cùng của bạn là gì thì cứ mô tả như vậy. Đưa file lên sẽ có kết quả sớm (còn không thì cứ cù nhầy mãi thôi)
————————————-

Em thử viết như vầy:

Function SheetExist(SheetName As String, Optional FileName As String = "") As Boolean
Dim tmp As String
    With New ADODB.Connection
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & IIf(FileName = "", ThisWorkbook.FullName, FileName) & _
                            ";Extended Properties=""Excel 12.0;HDR=No;"";"
        .Open
        With .OpenSchema(adSchemaTables)
            While Not .EOF
                If .Fields("TABLE_TYPE") = "TABLE" Then
                    tmp = Replace(.Fields("TABLE_NAME"), "'", "")
                    If UCase(tmp) Like UCase(SheetName & "$") Then SheetExist = True
                End If
                .MoveNext
            Wend
        End With
    End With
End Function

Viết code kiểu đó ai mà test được đây
Viết dưới dạng CreateObject(…) đi
Nếu người ta dùng Excel 2003 thì chổ màu xanh xem như.. toi
—————–
Thử dùng DAO xem sao

Function SheetExists(ByVal SheetName As String, Optional ByVal FileName As String = "") As Boolean
  Dim dao  As Object, db As Object
  Dim i As Long, lVer As Long
  Dim tmp As String
  On Error Resume Next
  lVer = Val(Application.Version)
  If Len(FileName) = 0 Then FileName = ThisWorkbook.FullName
  Set dao = CreateObject("DAO.DBEngine." & IIf(lVer < 12, "36", "120"))
  Set db = dao.OpenDatabase(FileName, False, False, "Excel 8.0;")
  tmp = CStr(db.TableDefs(SheetName & "$").Name)
  If Len(tmp) = 0 Then tmp = CStr(db.TableDefs("'" & SheetName & "$'").Name)
  SheetExists = (Len(tmp) > 0)
  db.Close: Set dao = Nothing: Set db = Nothing
End Function

@thanhlanh:

Code của bạn không sai nhưng có hai điểm mà người lập trình nên tránh:

1. Code không huỷ object sau khi sử dụng

2. Code dùng hiện tượng bắt lỗi để làm việc chính. Đối với lập trình, hiện tượng bắt lỗi là chuyện cực chẳng đã, chỉ dùng để bắt những trường hợp không biết trước. Nếu có cách nào khác, người ta luôn luôn chọn cách khác.

Đọc Schema qua ADO hay DAO như hai bạn trên là cách đơn giản hơn.

Tôi xin hầu thêm cách đọc Catalog qua ADOX
Function SheetExists(ByVal fileName As String, ByVal sheetName As String)
Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table

SheetExists = False
Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=" & fileName
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
For Each t In cat.Tables
    If t.Name = sheetName & "$" Then
        SheetExists = True
        Exit For
    End If
Next t
Set cat = Nothing
cn.Close
Set cn = Nothing
End Function
Cảm ơn bạn đã góp ý, mình đúng là cực chẳng đã mới dùng bẫy lỗi để làm việc, bởi vì còn tệ hơn ndu, mình đâu biết gì về ADO, DAO hay ODA đâu, nhìn giống Tôn Hành Giả, Giả Hành Tôn quá. Bây giờ bạn còn đưa ADOX vào nữa thì có khi mình sẽ rối loạn tiền đình đó.
Tuy không đúng yêu cầu (sau) của tác giả topic nhưng tạo được hàm trong VBA để kiểm tra sự tồn tại của Sheets khi không mở file cũng hay và đáng quan tâm chớ nhỉ?

Có mấy vấn đề

Function SheetExists(ByVal fileName As String, ByVal sheetName As String)
Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table

SheetExists = False
Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=" & fileName
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
For Each t In cat.Tables
    If [COLOR=#ff0000]t.Name = sheetName & "$"[/COLOR] Then
        SheetExists = True
        Exit For
    End If
Next t
Set cat = Nothing
cn.Close
Set cn = Nothing
End Function

– Chổ màu đỏ phải lòng UCase hoặc LCase vào mới xong!
– Nếu tên sheet có chứa khoảng trắng thì t.Name sẽ có 2 dấu ' ở 2 đầu
– Code trên không dùng được nếu tên sheet là chuổi tiếng Việt có dấu
Thuật toán là vậy nhưng phải sửa tiếp thì code trên mới hoàn chỉnh

Cảm ơn bạn đã dẫn. Tôi cứ ngỡ code trên dùng ODBC nên thoát khỏi cảnh phải lồng giữa 2 dấu nháy. Không chịu thử cho kỹ.
Hoá ra đọc theo tầng trên (metadata) còn tệ hơn đọc theo kiểu dữ liệu thô.

Thật ra thì tôi có biết cái quỷ gì về ADO hay DAO đâu. Chẳng qua cách đây vài năm có từng làm 1 code với ứng dụng tương tự nên gần như đã "thuộc lòng" những sự cố cần phải tránh
Nói về kiến thức ADO, DAO thì: Nếu bạn biết được 10 phần, tôi chỉ biết được có 0.000…01
Ẹc… Ẹc…

Vấn đề này dùng hàm Macro4 cũng tốt mà gọn. Mình ví dụ như sau

Option Explicit
Function SheetExist(ByVal mPath As String, fName As String, ShName As String) As Boolean
Dim Tm, myVar
On Error Resume Next
Tm = "'" & mPath & "[" & fName & "]" & ShName & "'!" & _
Range("A1").Address(, , xlR1C1)
myVar = ExecuteExcel4Macro(Tm)
SheetExist = Not IsEmpty(myVar)
End Function
'--------------------------------------------------------------------------------
Sub Test()
Dim myPath As String, myFile As String, mySheet As String
myPath = Thisworkbook.Path & ""
myFile = "Mau danh cho CC BCTC48.xls"
mySheet = "B-01"
MsgBox IIf(SheetExist(myPath, myFile, mySheet), _
"Exist sheet: " & mySheet, "The sheet: " & mySheet & " not foumd")
End Sub

Code này chỉ chạy trên VBA như 1 sub thì được… Anh thử gõ trực tiếp hàm xuống sheet xem nó ra kết quả gì?

Mình viết lại rồi, chỉ thay đổi cách gán giá trị cho Hàm thôi

Test trong môi trường VBA thì được:

Sub Test()
 Dim mPath As String, fName As String, ShName As String
 mPath = [A5]
 fName = [A6]
 ShName = [B1]
 MsgBox SheetExist(mPath, fName, ShName)
End Sub

Nhưng nếu anh gõ hàm trực tiếp xuống sheet, ví dụ: =SheetExist(A5, A6, B1) với A5 là thư mục, A5 là tên file và B1 là tên sheet —> Kết quả toàn = FALSE thôi anh à

Đúng vậy, mình toàn test luôn trên VBA nên không lường ra trường hợp này.

Dùng trong VBA được là tốt rồi, miễn sao trả về kết quả chính xác với mọi phiên bản Excel và với mọi tên Sheet, tên file.
Mình "tín nhiệm cao" ndu test cho code của anh sealand.