locked
Can PowerPivot data be accessed with an Excel macro? RRS feed

  • Question

  • The basic question is:  Can PowerPivot data be accessed with an Excel macro?

    We are very new PowerPivot users with a simple task that needs to be automated with an Excel macro.

    We have a blank Excel worksheet and we have three columns of numeric data in PowerPivot (F2, F3 and F4).  F2's numbers are actually dates in yymmdd format that are already sorted smallest to largest by PowerPivot, we unselect "(Select All)" here and select the first box (date), then "OK".  Then we manually sort F3 smallest to largest.  F4 is left alone.

    We are good, but not great, with Excel macros.  Here is what we are doing manually now and need to automate with an Excel maro, if possible, but we are open to other methods:

    Ctrl-a then Ctrl-p at the PowerPivot screen (selecting and copying everything)

    Alt-Tab then Ctrl-v (to paste everything to the Excel sheet)

    We move the cursor four columns to the right to be ready for the next paste operation then Alt-Tab back to PowerPivot

    At F2 we uncheck the first box then check the second, then OK.  (F3 does not need to be touched as PowerPivot keeps the previous sort selection.)

    We repeat the above four paragraphs over and over until the dates in F2 are exhausted.  We would very much appreciate any assistance.  We are unable to discover any Excel macro command that would even access PowerPivot let alone carry out the simple instructions above.

    Friday, March 25, 2011 10:48 PM

Answers

  • There is no API to program with VBA against PowerPivot (http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=33)

    I don't know what the source of your PowerPivot table is and how much rows are involved, but you may put the data in a Worksheet, in Excel you could anipulate the data with VBA, format the data as table and create a "Linked Table" to impport the data in PowerPivot. 


    Eddy N.
    • Marked as answer by JP1949 Monday, March 28, 2011 3:01 PM
    Monday, March 28, 2011 8:09 AM

All replies

  • There is no API to program with VBA against PowerPivot (http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=33)

    I don't know what the source of your PowerPivot table is and how much rows are involved, but you may put the data in a Worksheet, in Excel you could anipulate the data with VBA, format the data as table and create a "Linked Table" to impport the data in PowerPivot. 


    Eddy N.
    • Marked as answer by JP1949 Monday, March 28, 2011 3:01 PM
    Monday, March 28, 2011 8:09 AM
  • Essentially we are using PowerPivot to extract a limited amount of data from large .csv text files that were previously inaccessible to Excel due to their size.  These .csv files average about six million rows each and each contains about 60 days of data, each day having many rows.  We bring that data into PowerPivot then use PowerPivot to filter that data and manually copy the results, about 100,000 rows for each day, one day at a time, from PowerPivot to Excel.  We were looking for a way to somehow automate this process.

    Unfortunately there does not seem to be a solution.  Eddy, thank you for your response and the link; that answers our basic question.

    Regards,
    JP

    Monday, March 28, 2011 3:01 PM