读取Excel文件表名称 [英] Read Excel file sheet names

问题描述

我有一个导出过程，该过程将数据从Access表传输到Excel文件.有几次我遇到了问题，该过程未在Excel中生成一张或多张工作表(1张工作表= 1张表).因此，当传输完成后，我希望Access检查所有工作表是否都位于Excel文件中.我已经完成了大部分检查过程，现在我所需要的是一种将工作表名称从Excel文件读取"到表中的方法.如何读取工作表名称(而不是数据)?

I have an export process that transfers data from my Access tables to an Excel File. A couple times I have had issues where the process didn't generate one or more of the sheets (1 sheet = 1 table) in Excel. So when the transfers are complete I want Access to check if all the sheets are located in the Excel file. I have most of the Check process worked out all I need now is a way to "read" the sheet names from the Excel File in to a table. How can I read the Sheet name (not the data)?

推荐答案

在Access中，您可以自动执行Excel，打开工作簿文件，并从Worksheets集合中读取工作表名称.

From Access you can automate Excel, open the workbook file, and read the sheet names from the Worksheets collection.

此示例使用后期绑定.如果您希望早期绑定，请添加对 Microsoft Excel [version]对象库的引用，并启用早期"行而不是后期"行.

This sample uses late binding. If you prefer early binding, add a reference for Microsoft Excel [version] Object Library and enable the "early" lines instead of the "late" lines.

为该过程提供工作簿文件的完整路径作为其 pWorkBook 参数.

Give the procedure the full path to your workbook file as its pWorkBook parameter.

Public Sub List_worksheets(ByVal pWorkBook As String)
    'Dim objExc As Excel.Application ' early
    'Dim objWbk As Excel.Workbook ' early
    'Dim objWsh As Excel.Worksheet ' early
    Dim objExc As Object ' late
    Dim objWbk As Object ' late
    Dim objWsh As Object ' late

    'Set objExc = New Excel.Application ' early
    Set objExc = CreateObject("Excel.Application") ' late
    Set objWbk = objExc.Workbooks.Open(pWorkBook)
    For Each objWsh In objWbk.Worksheets
        Debug.Print objWsh.Name
    Next
    Set objWsh = Nothing
    objWbk.Close
    Set objWbk = Nothing
    objExc.Quit
    Set objExc = Nothing
End Sub

这篇关于读取Excel文件表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！