+ Reply to Thread
Results 1 to 2 of 2

PivotCache

  1. #1
    T.
    Guest

    PivotCache

    I am using MS Dynamics GP SmartList Export Solution. The code to create a
    pivot chart when I export the SmartList includes this:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R322C14").CreatePivotTable TableDestination:="",
    TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10

    I want to replace the second line: "Sheet1!R1C1:R322C14" with variables
    that will change depending on the row number in the SmartList I am exporting.
    The number of rows will vary from day to day depending on the number of open
    transactions.

    The range of the data for the pivot chart will always go from A1 to Last
    Cell. I can capture the address of the last cell, but I can't figure out how
    to replace the hard coded range with the variables. Right now I am setting
    A1 as the value of a variable called HomeCell and whatever the last cell
    address is for the value of a variable called CellRef. However when I change
    the PivotCache to read:

    "Sheet1!HomeCell, CellRef"

    I get an error. I have seen this done before so I know it is possible, but
    I cannot remember the syntax. Can anyone help?

    Thanks.

    T.

  2. #2
    Debra Dalgleish
    Guest

    Re: PivotCache

    You could use the address of the current region, e.g.:

    '=======================
    Dim rngSource As Range
    Dim strRng As String
    Set rngSource = Worksheets("Sheet1").Range("A1").CurrentRegion
    strRng = "Sheet1!" & rngSource.Address

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=strRng).CreatePivotTable TableDestination:="", _
    TableName:="PivotTable3"
    '=======================

    T. wrote:
    > I am using MS Dynamics GP SmartList Export Solution. The code to create a
    > pivot chart when I export the SmartList includes this:
    >
    > ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    > "Sheet1!R1C1:R322C14").CreatePivotTable TableDestination:="",
    > TableName:= _
    > "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    >
    > I want to replace the second line: "Sheet1!R1C1:R322C14" with variables
    > that will change depending on the row number in the SmartList I am exporting.
    > The number of rows will vary from day to day depending on the number of open
    > transactions.
    >
    > The range of the data for the pivot chart will always go from A1 to Last
    > Cell. I can capture the address of the last cell, but I can't figure out how
    > to replace the hard coded range with the variables. Right now I am setting
    > A1 as the value of a variable called HomeCell and whatever the last cell
    > address is for the value of a variable called CellRef. However when I change
    > the PivotCache to read:
    >
    > "Sheet1!HomeCell, CellRef"
    >
    > I get an error. I have seen this done before so I know it is possible, but
    > I cannot remember the syntax. Can anyone help?
    >
    > Thanks.
    >
    > T.



    --
    Debra Dalgleish
    Contextures
    http://www.contextures.com/tiptech.html


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1