Is there any way to define table area using excel

问题:

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?

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.

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

回答1:

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

回答2:

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

回答3:

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

回答4:

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"

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