需引用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