我从用户那里收到一个excel(Book1.xls)文件,有时其中一个工作表名称是XYZ,有时它的名称是XYZ1。
我在做
Dim Conn,Rs
Set Conn = CreateObject("ADODB.Connection")
Conn.ConnectionTimeout = 10
Conn.CommandTimeout = 300
Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Book1.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";"
Set RS = Conn.Execute("SELECT * FROM [XYZ$B1:B1]")
当工作表名称为XYZ1时,上述代码的最后一行会弹出错误消息:Microsoft Access 数据库引擎找不到对象"XYZ$CB1:B1"。确保对象存在,并且正确拼写其名称和路径名。如果"XYZ$CB1:B1"不是本地对象,请检查网络连接或与服务器管理员联系。
而不是最后一行,我想做一些类似的事情
sheetNameString = Conn.Execute("SELECT Join(SheetName) from Book1.xls")
If InStr(sheetNameString,"XYZ1") Then
Set RS = Conn.Execute("SELECT * FROM [XYZ1$B1:B1]")
Else
Set RS = Conn.Execute("SELECT * FROM [XYZ$B1:B1]")
End If
在conn.open之后添加以下代码对我有用:
Dim Cat, Tbl, Tabs
Cat = CreateObject("ADOX.Catalog")
Cat.ActiveConnection = Conn
Tabs = Cat.Tables
For Each Tbl In Tabs
'Tbl.Name holds the name
Next
您可以使用"ADOX.目录"以获取数据库中表的列表:
Public Function getTableNames(oConn)
Dim aRVal : aRVal = Array()
Dim oCatalog : Set oCatalog = CreateObject( "ADOX.Catalog" )
Set oCatalog.ActiveConnection = oConn
Dim oTable
For Each oTable In oCatalog.Tables
ReDim Preserve aRVal( UBound( aRVal ) + 1 )
aRVal( UBound( aRVal ) ) = oTable.Name
Next
getTableNames = aRVal
End Function
If "XYZ1$" = getTableNames(Conn)(0) Then
Set RS = Conn.Execute("SELECT * FROM [XYZ1$B1:B1]")
Else
Set RS = Conn.Execute("SELECT * FROM [XYZ$B1:B1]")
End If
(未测试,假设只有一张纸/表,需要改进(例如 ReDim aRVal(oCatalog.Tables.Count - 1)
而不是ReDim Preserve
),不确定是否会保留尾随的$)
另一种方式:
Public Function getTableNames(oConn)
Const adSchemaTables = 20
Dim oRS : Set oRS = oConn.OpenSchema(adSchemaTables)
Dim aRVal : aRVal = Array()
Do Until oRS.EOF
ReDim Preserve aRVal(UBound(aRVal) + 1)
aRVal(UBound(aRVal)) = oRS.Fields("TABLE_NAME").Value
oRS.MoveNext
Loop
getTableNames = aRVal
End Function