Return a column number based on the column name in csv file using .find

Question

below is a piece of code that works on xlsx files but not on the csv files, at least I suspect so.

The aim of the code is to find a column name e.g. ProductType and return the column number in which the said column name is stored.

Again, this piece of code works perfectly when I run it on xslx file types, however doing so on the csv files does not work.

Any help is appreciated.

Sub ma1()

    Dim RA As Excel.Workbook

    Set RA = Workbooks.Open("G:\depts\Pri\RA.csv")

    RA_col = RA.Sheets(1).Cells.Find(What:="ProductType", MatchCase:=True, LookAt:=xlWhole).Column

    Debug.Print (RA_col)


End Sub

Are the columns already separated when the csv-file is opened? Otherwise your `find` wouldn't return a match. — Beek, Nov 04 '19 at 13:42
when I open a ```csv``` i see them seperated as if I opened an ```xlsx``` file. But I see your point — Mr.Riply, Nov 04 '19 at 13:44
So if you're in a CSV that needs to has a specific value, would you not want `LookAt:=xlWhole` to be `LookAt:=xlPart`? You could essentially denote a "column" number by the count of commas (or other delimiter) + 1. At the end of the day, how you will use the data will play a significant part in what would be more appropriate. — Cyril, Nov 04 '19 at 13:58
You could use `Match` in your header range to find the column assuming that your columns start from column `A` — Zac, Nov 04 '19 at 14:48

score 2 · Accepted Answer · answered Nov 04 '19 at 14:02

Instead of using find, if you only care about finding the column in the header row, you can simply iterate over the first row looking for a match. I've tried to encapsulate that into a function you can call.

You supply the filepath, ColumnName and optionally the type of comparison you want to perform. By default, the comparison is a case sensitive match (binary), but can also be swapped to a case insensitive match too.

Function

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, .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

Example usage

Public Sub ExampleCall()
    Debug.Print GetColumnIndexFromFile("G:\depts\Pri\RA.csv", "ProductType")
End Sub

You should probably include some code to deal with when the file is already open, as it will throw an error otherwise. For example https://stackoverflow.com/questions/49234771/open-workbook-if-not-already-open-if-already-then-get-that-reference/49236520#49236520 — Tragamor, Nov 04 '19 at 14:57
Don't disagree, but running this code multiple times with the same workbook (the csv) open doesn't raise on error for me. — Ryan Wildry, Nov 04 '19 at 15:31

Return a column number based on the column name in csv file using .find

1 Answers1