有没有什么办法用动态数据程序化地定义表区域? [英] Is there any way to define table area using excel programatically with dynamic data?
问题描述
INFO:
- 这里有2个文件,首先是受保护的源数据,第二个是数据透视文档。
- 数据是动态的,表包含标题。
有没有办法用动态数据以编程方式定义表区?
从以前的两个答案中回答您的意见(在我看来,符合您的需要)。
以下是使用vba定义命名范围的方法:
Dim Rng1 As Range
'更改范围的单元格(A1:B15)是要定义的单元格范围
设置Rng1 =表格(Sheet1)。范围(A1:B15)
ActiveWorkbook.Names.Add名称: =MyRange,参考:= Rng1
这是一种用vba创建表的方法(记住它只能在Excel 2007或更高版本):
Sub CreateTable()
ActiveSheet.ListObjects.Add(xlSrcRange,Range($ B $ 1:$ D $ 16),xlYes).Name = _
Table1
'没有在2003
ActiveSheet.ListObjects(Table1)。TableStyle =TableStyleLight2
End Sub
I have a sheet that contain table (produced from jasper report query). This table will be the source of my pivot table. The Pivot is created using external connection (From Microsoft Query). since the source table need to defined before before can be used in Micrososft Query, could anyone show me how to do it programatically?
INFO:
- There are 2 documents here, first is protected source data and the second is Pivot document.
- The data is dynamic and the table contain header.
Is there any way to define table area using excel programatically with dynamic data?
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:
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
Here is a way to create a table with vba (remember it will only work on Excel 2007 or higher):
Sub CreateTable()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name = _
"Table1"
'No go in 2003
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub
这篇关于有没有什么办法用动态数据程序化地定义表区域?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!