Is there a way to define a table area using excel programmatically with dynamic data?

Question

I have a sheet containing a table (derived from a jasper report request). This table will be the source of my pivot table. Pivot is created using an external connection (from Microsoft Query). since the source table needs to be defined before it can be used in a Micrososft Query query, can someone show me how to do this programmatically?

INFO:

• There are two documents here, the first is the protected source data, and the second is the master document.
• The data is dynamic, and the table contains a header.

Is there a way to define a table area using excel programmatically with dynamic data?

Answer 1

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

Here is a way to define a named range using 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 using vba (remember that it will only work in 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

Answer 2

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

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

Answer 3

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

 Sub DefineTable() Dim tbl As ListObject Set tbl = Sheets("Plan1").ListObjects(1) tbl.Range.Select End Sub 

Otherwise, a dynamic range is created using the name, for example

= OFFSET (plan1 A1; 0; 0; COUNTA (plan1 A :! A); COUNTA (plan1 1: 1))

Select a name for this range, and in the vault, define a range in = NameOfInterval

[] 's

Answer 4

 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$1:$D$16").Select oSh.ListObjects.Add(xlSrcRange, oSh.Range("$A$1:$D$16"), , 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 

Answer 5

Here's how to approach if you don't know the size of the range: first get the index refs of the last row / column. Then use indexes to create "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