不打开Excel文件读取工作表名(ADOX)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/taller_2000/article/details/88075998

经常会遇到网友提问:如何实现在不打开Excel工作簿文件的前提下,读取其中的工作表名称。说实话,我不太理解这个需求应用的场景,但是我相信存在的就是合理的,既然有人提问,可能就有其应用的场景。

方法1

常规实现思路是障眼法,这是个满(qī)足(piàn)用户的好方法,工作簿文件被打开了,但是用户看不到而已。示例文件中的工作表如下图。
在这里插入图片描述
VBA代码如下。

Sub getTablesNameFake()
    Dim objApp As Excel.Application
    Dim objWk As Workbook
    Dim strMsg As String
    Dim strPath As String
    Dim objSht As Worksheet
    'strPath = ThisWorkbook.Path & "\Excel2003.xls"
    strPath = ThisWorkbook.Path & "\Excel2016.xlsx"
    Set objApp = New Excel.Application
    objApp.Visible = falses
    Set objWk = objApp.Workbooks.Open(strPath)
    strMsg = "工作簿中共有:" & objWk.Sheets.Count & " 个工作表" & vbNewLine & vbNewLine
    For Each objSht In objWk.Sheets
        i = i + 1
        strMsg = strMsg & "工作表" & i & ":" & vbTab & objSht.Name & vbNewLine
    Next
    MsgBox strMsg
    objWk.Close False
    objApp.Quit
    Set objApp = Nothing
    Set objWk = Nothing
    Set objSht = Nothing
End Sub

运行代码结果如下图。
在这里插入图片描述

代码解析:

第9行代码新建一个Excel应用程序。
第10行代码隐藏Excel应用程序。
第11行代码打开工作簿文件,注意等号右侧必须指定objApp,否者将在当前Excel应用程序中打开工作簿文件。
第12行代码统计工作表数量。
第13行到第16行代码循环结构遍历工作表名称。
第17行代码显示查询结果。
第18行代码关闭工作簿,并且不保存修改。
第19行代码退出新建的Excel应用程序。

方法2:

对于ADO(Microsoft ActiveXData Object),大家并不陌生,多数VBA用户都知道ADO是Microsoft继ODBC技术之后,基于OLE DB的一种数据库操作技术。这个和读取工作表名称有关系吗!
答案是YES,不过大家经常用到的是Microsoft ActiveX Data Objects x(不知道如何使用的,请自行补脑),其实还有另外一ADOX的库,其全称是Microsoft ADO Ext. x for DDL and security,其中DDL(Data Definition Language)用于创建数据库中的各种对象(表、视图、索引等)。

在VBA中使用前期绑定方式创建ADOX对象,需要在VBE中添加引用,如下图所示。
在这里插入图片描述
VBA代码如下所示。

Sub getTablesName()
    Dim Cat As New ADOX.Catalog
    Dim Tb As ADOX.Table
    Dim strMsg As String
    Dim strPath As String
    Dim i As Integer
    'strPath = ThisWorkbook.Path & "\Excel2003.xls"
    'Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=yes;';Data Source=" & strPath
    strPath = ThisWorkbook.Path & "\Excel2016.xlsx"
    Cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=yes;';Data Source=" & strPath
    strMsg = "工作簿中共有:" & Cat.Tables.Count & " 个工作表" & vbNewLine & vbNewLine
    For Each Tb In Cat.Tables
        i = i + 1
        strMsg = strMsg & "工作表" & i & ":" & vbTab & Tb.Name & vbNewLine
    Next
    MsgBox strMsg
    Set Cat = Nothing
    Set Tb = Nothing
End Sub

运行代码结果如下图所示。请注意与方法1结果截图的区别,工作表的顺序与工作簿中的显示顺序不一致。
在这里插入图片描述

代码解析:

第2行和第3行代码声明ADOX对象。
第7行和第8行代码用于连接XLS文件,第9行和第10行代码用于连接XLSX文件,二种的区别在于OLEDB引擎版本和Properties参数。
第11行代码统计工作表数量。
第12行到第15行代码循环结构遍历工作表名称。
第16行代码显示查询结果。

对于比较大的工作簿文件,方法2借助ADOX引擎,其效率会更高些。

展开阅读全文

没有更多推荐了,返回首页