Carriage Return in Table Header

I’m writing an Excel macro to pull in data from another Workbook. I don’t have any control over that workbook, which is a shame because then I could fix this issue at source, as it is i have to work around it.

There is a column header that starts with a carriage return! and I need to call a select on it.

I’ve looked around here and found a lot of solutions for when there are carriage returns in the DATA but I can’t find anything to deal with them in the header. I’ve tried to play arround with implementing similar syntax but nothing seems to work. If there is already a solution out there do link me! Maybe I’m just using the wrong terms.

sSQL = "SELECT " & _
                "['Dependant Name* (LastName, FirstName (Type))']" & _
           "FROM " & _
                "........" & _
           "WHERE " & _
                "........"

So it’s a horrible header to start with and it starts with a carriage return!

Any suggestions?

Answer

Not sure how you’re connecting to the excel sheet, using ADODB connection, Excel replaces the carriage return with an “_”.

So if I had:

~blank line~ Field1

as column name, Excel would give _Field1 as the fieldname for that column.

Alternatively, you could adjust your script to SELECT * from... and then in the immediate window debug.print Recordset.Fields(# of the column the bad header is).Name to get how Excel is interpreting it and use that value in your select statement.