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

Question

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

JMax · Accepted Answer · 2011-09-20 08:39:29Z

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

Source

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

Source

Bruno Leite · Answer 2 · 2011-09-20 01:34:49Z

If you use a table (defined) , you can call 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))

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

[]'s

I need to define the table/ area a name, how this could be achived?

brettdj · Answer 3 · 2011-09-20 01:34:20Z

up vote 2 down vote

You can use a dynamic range name which expands automatically to your data size - no VBA required

See Debra's write up at http://www.contextures.com/xlpivot01.html#Dynamic

answered Sep 20 '11 at 1:34

brettdj
12.4k2932

I need this using VBA because this will be inserted inside my VBA Application. – indrap Sep 20 '11 at 6:20

feedback

asked	11 months ago
viewed	188 times
active	11 months ago

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

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged excel vba or ask your own question.

Hello World!

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

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged excel vba or ask your own question.

Hello World!

Related