vba 有什么方法可以使用动态数据以编程方式使用excel定义表格区域吗？

Is there any way to define table area using excel programatically with dynamic data?

提问by indrap

I have a sheet that contains a table (produced from jasper report query). This table will be the source of my pivot table. The Pivot is created using an external connection (From Microsoft Query). since the source table needs to be defined before it can be used in Micrososft Query, could anyone show me how to do it programatically?

我有一张包含表格的工作表（从 jasper 报告查询生成）。这张表将是我的数据透视表的来源。Pivot 是使用外部连接（来自 Microsoft Query）创建的。由于需要先定义源表，然后才能在 Micrososft Query 中使用它，有人可以告诉我如何以编程方式进行操作吗？

INFO:

信息：

1. There are 2 documents here, the first is a protected source data and the second is a Pivot document.
2. The data is dynamic and the table contains a header.

1. 这里有 2 个文档，第一个是受保护的源数据，第二个是 Pivot 文档。
2. 数据是动态的，表格包含一个标题。

Is there any way to define the table area using excel programatically with dynamic data?

有什么方法可以使用动态数据以编程方式使用 excel 定义表格区域？

采纳答案by JMax

To answer your comments from the two previous answers (whose, in my opinion, fit your need).

从前两个答案中回答您的评论（在我看来，它们适合您的需要）。

Here is a way to define named range with vba:

这是一种使用 vba 定义命名范围的方法：

Dim Rng1 As Range 
'Change the range of cells (A1:B15) to be the range of cells you want to define
Set Rng1 = Sheets("Sheet1").Range("A1:B15") 
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1 

Source

来源

Here is a way to create a table with vba (remember it will only work on Excel 2007 or higher):

这是一种使用 vba 创建表格的方法（请记住，它仅适用于 Excel 2007 或更高版本）：

Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B:$D"), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

Source

来源

回答by user2591038

Public Function CopyDist() As Variant
On Error Resume Next
CopyDist = 0
'   RemoveTableStyle
Dim oSh As Worksheet
Set oSh = ActiveSheet
'    Set oSh =  'Sheets("Sheet1")
Dim oNewRow As ListRow

Dim myfirstrow As Integer
Dim mylastrow As Integer
Dim myfirstcolumn As Integer
Dim myValue As Variant


myfirstrow = ActiveCell.Row + 1
mylastrow = ActiveCell.Row + 1
myfirstcolumn = ActiveCell.Column
Cells(myfirstrow, myfirstcolumn).Select
Cells(myfirstrow, myfirstcolumn).Clear

oSh.Range("$A:$D").Select
oSh.ListObjects.Add(xlSrcRange, oSh.Range("$A:$D"), , xlYes).Name = "Table1"
'No go in 2003
oSh.ListObjects("Table1").TableStyle = "TableStyleLight2"
' CreateTable
If oSh.ListObjects.Count > 0 Then
  myValue =oSh.ListObjects.Count 
End If
RemoveTableStyle
CopyDist = 1
End Function

回答by Anisa Khandkar

Here's how to approach if you do not know the range size: First get the index refs of the last row / column. Then use the indexes to create "Table1"

如果您不知道范围大小，可以采用以下方法：首先获取最后一行/列的索引引用。然后使用索引创建“Table1”

Dim lngLastColumn as Long
Dim lngLastRow as Long
Set oxlSheet = oxlWB.Worksheets(1) '''or whichever sheet you need    

With oXlSheet
        lngLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .ListObjects.Add(xlSrcRange, .Range(.Cells(1, 1), .Cells(lngLastRow, lngLastColumn)), , xlYes).Name = "Table1"
    End With

回答by Bruno Leite

If you use a table (defined) , you can call table object sample

如果使用 table (defined) ，则可以调用 table object sample

Sub DefineTable()

Dim tbl As ListObject

Set tbl = Sheets("Plan1").ListObjects(1)

tbl.Range.Select

End Sub

Otherwise is create a dynamic range using a name for example

否则，例如使用名称创建动态范围

=OFFSET(Plan1!A1;0;0;counta(Plan1!A:A);counta(Plan1!1:1))

=OFFSET(Plan1!A1;0;0;counta(Plan1!A:A);counta(Plan1!1:1))

Select a name to this range, and in your pivot define a range at =NameOfInterval

选择这个范围的名称，并在您的枢轴中定义一个范围在 =NameOfInterval

[]'s

[] 的