如果您只在意查找标题行中的列,而不是使用find,则可以简单地遍历第一行以查找匹配项。我试图将其封装到可以调用的函数中。
您提供文件路径,ColumnName和可选的要执行的比较类型。默认情况下,比较是区分大小写的匹配(二进制),但也可以交换为不区分大小写的匹配。
功能
Option Explicit
Public Function GetColumnIndexFromFile(FilePath As String,ColumnName As String,Optional CompareMethod As VbCompareMethod = VbCompareMethod.vbBinaryCompare)
Dim wb As Workbook
Dim ws As Worksheet
Dim Column As Range
Dim Columns As Range
Dim ColumnIndex As Long
Set wb = Workbooks.Open(FilePath)
Set ws = wb.Sheets(1)
With ws
Set Columns = .Range(.Cells(1,1),.Cells(1,.Columns.Count).End(xlToLeft).Column))
End With
For Each Column In Columns
If StrComp(Column.Value2,ColumnName,CompareMethod) = 0 Then
GetColumnIndexFromFile = Column.Column
Exit Function
End If
Next
End Function
用法示例
Public Sub ExampleCall()
Debug.Print GetColumnIndexFromFile("G:\depts\Pri\RA.csv","ProductType")
End Sub
本文链接:https://www.f2er.com/3166362.html