How do I reference an Excel ListObject table column if the column name has line breaks?

Question

I'm tasked with the following:

several sheets from different workbooks have to be copied to a new workbook
each of those sheets contains an Excel table (a ListObject), named like the sheet with a postfixed T (sheet Foo, table: FooT)
the new workbook has to contain a summary sheet where each table name is listed, and various values of the respective tables are presented by referencing them with suitable formulas

This has to be done frequently for different workbooks, so the idea was to do this in VBA.

Copying the sheets is easy, listing the table names in a new sheet is easy, but referencing the values runs into problems.

The general idea is to do the following

ActiveSheet.Range("A1").Value = "FooT"
ActiveSheet.Range("B1").Formula = "=FooT[[#Totals],[Quantity]]"
ActiveSheet.Range("C1").Formula = "=FooT[[#Totals],[Total List Price]]"

and iterate over all sheets.

Setting the value for A and the formula for B works as expected and gets the expected results.

The issue with C is that instead of "Total List Price", the column header is actually formatted as

"Total
List
Price"

I can't change that, this has been a design decision.

This is also how the column name shows up in the formula if I add the formula to the cell manually.

So there's some sort of line break happening here, and I've tried cater to this in VBA with

ActiveSheet.Range("C1").Formula = "=FooT[[#Totals],[Total" & vbCrLf & _
                                                   "List" & vbCrLf & _
                                                   "Price]]"

and vb_Cr and vb_Lf and vb_Newline instead of the vbCrLf. Trying to set C's formula to any of these variations yields the infamous Error 1004.

Getting the value of the column header from one of the sheets and using it in the formula works. This could be a potential workaround, but I'd really like to know what I'm missing or how I can figure out how to build this formula string correctly.

Can't speak 100% to excel, but in SQL returns and line feeds are not valid characters at all for columns. This applies to a datagrid and related objects in .NET too. I can assume it applies to excel as well. The design decision seems to have painted you into a corner. Perhaps you can store the index and reference it by that value? — Jacob H
Your formula is OK but typically, the newline character will be vbLf if the title was set from the keyboard. I suspect there's a trailing space after "Total" and/or "List". (Select your title and, from the Immediate Window, type Debug.Print Selection.Value, then check where each printed line ends. — Excelosaurus
@Excelosaurus can you turn this into an answer? vbLf works, and there were no spaces. Seems to be one of these errors where you open Excel the next day and the unchanged code suddenly starts to work. — Michael Schumacher

Excelosaurus Excelosaurus · Accepted Answer · 2017-10-26T13:22:28

Your formula is OK but typically, the newline character will be vbLf if the title was set from the keyboard. I also suspect there might be leading and/or trailing space characters anywhere in the title. Select your title cell and, from the VBE's Immediate Window (Ctrl+G), type Debug.Print ActiveCell.Value, then check where each printed line ends.

Are you using Option Explicit? In your question, you mention you've tried vb_Lf but this constant doesn't exist and, without Option Explicit, would have been interpreted as an empty string.

How do I reference an Excel ListObject table column if the column name has line breaks?

1 Answers