|
旧版本excel获取工作表名称没有问题,2010版本之后的就不行了,一直是运行时错误,各位大神支支招啊。
Function GetTable()
path = GetPath() 'ThisWorkbook.path & "\系统数据\"
file = "D20191101.xls" '无法识别.xlsx
Dim cat As Object, MyTable As Object, n&, s$, arr()
Set cat = CreateObject("ADOX.Catalog")
'2003-2007版的用这个
'cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & path & file 'ThisWorkbook.path & fileProvider
'2010版本测试没有成功
cat.ActiveConnection = "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;data source=" & path & file 'ThisWorkbook.path & fileProvider
For Each MyTable In cat.Tables
If MyTable.Type = "TABLE" Then
s = Replace(MyTable.name, "'", "")
If Right(s, 1) = "$" Then
n = n + 1
ReDim Preserve arr(1 To n)
arr(n) = s
End If
End If
Next
Set cat = Nothing
Set MyTable = Nothing
GetTable = Left(arr(1), Len(arr(1)) - 1)
MsgBox GetTable
End Function
|
-
报错截图
|