如何在没有任何数据行的Excel Table / ListObject中读取计算列的公式 [英] How to read formulas of Calculated Columns in an Excel Table/ListObject without any data rows

问题描述

我有一个ListObject，一个外部查询作为数据源，它返回18列。 ListObject之前已经添加了另外4个计算列。

现在，ListObject有0个数据行，但是有0个数据行，我不似乎能够读取计算列的预定义公式。

如果我刷新数据源，并且数据源返回至少1行，那么计算列的公式变得可读。同样，如果我手动输入一个非计算列中的数据，以至于至少有一行，则计算的列公式是可读的。

是否存在

解决方案

一种方法来确定计算的列公式是否不将添加到列表对象中>这是一个解决方法，无论表是否有行。

getListColumnFormulae
- 向表中添加一行
- 填充1维基数1数组与所有ListColumns的公式
- 删除行
- 返回数组

---

 函数getListColumnFormulae（tbl As ListObject）
 Dim Formulas 
 On Error Resume Next 
 With tbl.ListRows.Add 
 Formulas = Application.Transpose（.Range.Formula）
 Formulas = Application.Transpose（Formulas）
 getListColumnFormulae =公式
 .Delete 
结束
错误GoTo 0 
结束函数
 
 Sub FormulaeMessage（）
 Dim Data 
 Dim tbl As ListObject 
 Set tbl = Worksheets（Sheet2）。ListObjects（1）
 Data = getListColumnFormulae（tbl）
 
 End Sub 
  

I have a ListObject with an external query as the data source, which returns 18 columns. The ListObject has previously had an additional 4 calculated columns added.

Right now, the ListObject has 0 data rows, however, while there are 0 data rows, I don't seem to be able to read the pre-defined formulas of the calculated columns.

If I refresh the data source, and the data source returns at least 1 row, then the formulas for the calculated columns become readable. Likewise, if I manually enter data in one of the non-calculated columns, so that there is at least one row, then the calculated column formulas are readable.

Is there a way to determine what the calculated column formulas are without adding any data to the list object?

解决方案

Here is a workaround that will work whether the table has rows or not.

getListColumnFormulae - Adds a row to table - Fills an 1 dimensional base 1 array with the formulas for all the ListColumns - Deletes the row - Return the array

---

---

Function getListColumnFormulae(tbl As ListObject)
    Dim Formulae
    On Error Resume Next
    With tbl.ListRows.Add
        Formulae = Application.Transpose(.Range.Formula)
        Formulae = Application.Transpose(Formulae)
        getListColumnFormulae = Formulae
        .Delete
    End With
    On Error GoTo 0
End Function

Sub FormulaeMessage()
    Dim Data
    Dim tbl As ListObject
    Set tbl = Worksheets("Sheet2").ListObjects(1)
    Data = getListColumnFormulae(tbl)

End Sub

这篇关于如何在没有任何数据行的Excel Table / ListObject中读取计算列的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！