使用VBA从Excel文件中检索列标题 [英] Using VBA to retrieve Column Headers from Excel files

问题描述

我正在与一些必须在Excel文件中识别某些变量的人进行合作。目前，我正在使用的人有大量的文件夹和子文件夹，其中包含Excel文档。他正在使用一个VBA代码，在一个文件夹中查找子文件夹，然后返回该路径，然后创建一个超级链接到子文件夹（这不是VBA代码的一部分），并查看所有Excel文件内，无论主文件夹中的子文件夹级别如何。

I'm working with someone who has to identify certain variables within excel files. Currently, the man I'm working with has a great deal of folders and sub-folders that have Excel documents in them. He's using a VBA code that looks within a folder for a sub-folder, and then returns the pathway, then creating a hyperlink to the sub-folder (this isn't part of the VBA code below) and looking at all excel files within, no matter the level of sub-folders within the main folder.

这里是代码：

    Sub GetFolders()
Dim path As String
Dim folder As String
Dim row As Integer


path = "your directory here"
folder = Dir(path, vbDirectory)
row = 1

Do While folder <> ""
If (GetAttr(path & folder) And vbDirectory) = vbDirectory Then
    Cells(row, 1) = path & folder
    row = row + 1
End If
folder = Dir()
Loop

End Sub

这是伟大的，但我知道必须有一个更好的方法。如何操作此代码以将文件夹中包含的子文件夹中的所有Excel文件中的所有Excel文件返回到文件夹或B中）。我希望将这些数据返回到excel电子表格，以便不需要打开100个excel文档，而只需要打开这些文档，然后我们可以识别需要进一步调查的任何excel电子表格，并忽略其余的。

This is great, but I know there has to be a better way. How can I manipulate this code to return COLUMN HEADERS of any excel files found A) within a folder or B) within a subfolder contained within a folder. I want these to be returned to an excel spreadsheet so that 100's of excel documents don't need to be opened, but rather just this one, and then we can identify any excel spreadsheets that need further investigation and ignore the rest.

推荐答案

您可以使用ADO查询它们（根据需要调整连接字符串）：

You can query them with ADO (adjust the connection string as needed):

'Requires reference to Microsoft ActiveX Data Objects #.# Library
Private Function GetHeaders(filepath As String) As String()
    Dim output() As String
    Dim ado As New ADODB.Connection
    output = Split(vbNullString)

    With ado
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & filepath & "';" & _
              "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"";"
        With .OpenSchema(adSchemaTables)
            Dim table As String
            Dim columns As ADODB.Recordset
            Do While Not .EOF
                table = .Fields("TABLE_NAME")
                Set columns = ado.OpenSchema(adSchemaColumns, Array(Empty, Empty, table))
                With columns
                    Do While Not .EOF
                        ReDim Preserve output(UBound(output) + 1)
                        output(UBound(output)) = table & .Fields("COLUMN_NAME")
                        .MoveNext
                    Loop
                End With
                .MoveNext
            Loop
        End With
    End With
    GetHeaders = output
End Function

然后像您找到的每个文件一样调用它：

Then call it like this for each file that you find:

Sub Example()
    Dim headers() As String
    Dim i As Long
    headers = GetHeaders("C:\Foo\Bar.xlsx")
    For i = LBound(headers) To UBound(headers)
        Debug.Print headers(i)
    Next i
End Sub

请注意，这假设您不知道工作表名称和需求以获取 所有 的标题。输出数组中的字符串将采用 Sheet $ Field 的形式，但可根据需要进行调整。

Note that this assumes you don't know the sheet names and need to get headers for all of them. The strings in the output array will be in the form of Sheet$Field, but that can be adjusted according to need.

这篇关于使用VBA从Excel文件中检索列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！