利用ADO的OpenSchema方法在不打开xls文件情况下获取Sheet名集合

时　间:2011-10-10 13:41:59
作　者:叶海峰   ID：31  城市：广州
摘　要:通常要获取Sheet名称,都是要用excel.applcation打开相应的xls文件,然后循环sheet来获取名称,利用OpenSchema方法,可以直接获取,对于MDB同理
正　文:

需引用ADO库

Public Function GetTableName(DataBaseName As String)
    Dim strcnn As String
    Dim TableSet As ADODB.Recordset
    Dim con As New ADODB.Connection
    Dim DataBaseType As String
    Select Case Right(DataBaseName, 3)
    Case "xls"
        DataBaseType = "Excel Files"
    Case "mdb"
        DataBaseType = "MS Access Database"
    End Select
    strcnn = "DSN=" & DataBaseType & ";dbq=" & DataBaseName
    con.Open strcnn
    Set TableSet = con.OpenSchema(adSchemaTables)
    Do Until TableSet.EOF
        GetTableName = GetTableName & TableSet!table_name & ","
        TableSet.MoveNext
    Loop
    GetTableName = Left(GetTableName, Len(GetTableName) - 1)
    con.Close
    Set gconnection = Nothing
End Function