0

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?

Pawel Czyz
  • 1,651
  • 4
  • 17
  • 21
Benjamin Connell
  • 13
  • 5
  • 1
    Does `select *` work? Do you [have](https://stackoverflow.com/a/56010213/11683) to use SQL to begin with, given that your code resides in Excel to begin with? – GSerg May 09 '19 at 11:59
  • I can't Select * because there are loads of Headers, I don't need all of them, and the brief I have only calls for a few. I'm also joining two tables so I don't want all headers from both tables! I'm actually modifying an existing tool to add new features and SQL is the method already in place for this project. – Benjamin Connell May 09 '19 at 13:54

1 Answers1

0

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.

Mike
  • 624
  • 4
  • 14
  • Thanks Mike! That worked a treat. I am using ADODB so that line looked like this in the end: `"[_Dependant Name* (LastName, FirstName (Type))]" & _` – Benjamin Connell May 23 '19 at 10:01