Comparing values which include an ampersand &

[Nick Envey]

I have a stack of CSV files that I need to import data from. I have obtained code that will open the CSV, and written code to read a cell in the file then search the destination for the value read from that cell. If it finds the term in question it imports the associated data into the existing column. If it does not find the term, it appends it to the end as a new column.

Example:
Suppose there are 100 columns of existing data and "Star Wars" is column 42. "Bill & Ted's Excellent Adventure" is column 12. "Aliens" is not present.

A CSV file is read and the key cell has the value "Star Wars". The program would then search the first row of the destination sheet for "Star Wars" and find that it is in column 42. Whatever is contained in the CSV file is written into column 42.

Now say that a CSV file is read and the key cell is "Aliens". The program searches for "Aliens" in the destination worksheet and doesn't find it. "Aliens" is imported into column 101.

Now we get to the Voodoo.

Our third CVS file has "Bill & Ted's Excellent Adventure" in the key cell. The program runs and does not find the match in column 12, so it is appended and added in column 102. The header in column 102 is the whole key string "Bill & Ted's Excellent Adventure", just like column 12 contains.

Investigation has shown that when getting the value from the key cell, everything after the ampersand (&) is truncated. So "Bill & Ted's Excellent Adventure" is read as "Bill ", which clearly does not match the full "Bill & Ted's Excellent Adventure", so it makes sense that it isn't found.


I understand what is going on, and I understand more or less why it is happening. How do I make it work? Any help would be appreciated.

There is clearly some junk left in this code, as I'm debugging. The code is modified from some of Jerry Beaucaire's excellent samples. (Thank you Jerry if you ever happen to read this!)

    Dim fPath   As String:      fPath = "C:\Database\
    Dim fCSV    As String
    Dim fRNG    As String:      fRNG = "b6:b360"
    Dim oldDir  As String
    Dim wsTrgt  As Worksheet:   Set wsTrgt = ThisWorkbook.Sheets("ReturnedData")
    Dim wbCSV   As Workbook
    Dim NxtCol  As Long
    Application.ScreenUpdating = False
    NxtCol = wsTrgt.Cells(1, wsTrgt.Columns.Count).End(xlToLeft).Column + 1
    oldDir = CurDir
    ChDir fPath
    fCSV = Dir("*.csv")
    Do While Len(fCSV) > 0
        Set wbCSV = Workbooks.Open(fCSV)
'       Check to see if term already has a database entry
'       finalCol set to 52 for testing purposes only
        finalCol = 52
        colNum = 2
 
        For colNum = 2 To finalCol
                checkingFor = UCase(Cells(5, 2))
                checkingAgainst = UCase(wsTrgt.Cells(1, colNum))
                MsgBox (checkingFor & "    " & checkingAgainst)
            If UCase(Cells(5, 2)) = UCase(wsTrgt.Cells(1, colNum)) Then
                existsTag = True
                targetCol = colNum
                colNum = finalCol
            Else
                existsTag = False
            End If
        Next colNum
'        MsgBox (existsTag)
        If existsTag = False Then
            wsTrgt.Cells(1, NxtCol) = Replace(fCSV, " DataFile.csv", "")
            Range(fRNG).Copy wsTrgt.Cells(2, NxtCol)
            NxtCol = NxtCol + 1
        Else
            Range(fRNG).Copy wsTrgt.Cells(2, targetCol)
        End If
        wbCSV.Close False
        fCSV = Dir
    Loop