How to copy the PowerPivot data into the Excel workbook as a table with VBA?

Asked Apr 22 '15 at 06:46

Active Apr 22 '15 at 10:31

Viewed 1,323 times

I have found in another post how to copy PowerPivot data into an Excel 2013 workbook, post. But now I want to do the same thing as the answer but with a macro. (Unfortunately I can't leave a comment so I have to ask it as a new question.)

What I want to do is done by the following steps:

Select the Data tab
Select Existing connections
Choose the connection one wants.
Press Ok
Press Table at the radio button option
Choose a new sheet (you can choose to put it somewhere in an existing as well but not what i try to do here.)
Press Ok

I can't record a macro while I do this, so do anyone know the VBA code to do this?

edited Jul 09 '18 at 19:34

Community

asked Apr 22 '15 at 06:46

Erik Dahlen

Which version of Excel? – Rory Apr 22 '15 at 09:59
excel 2013... (do it matter?) – Erik Dahlen Apr 22 '15 at 10:02
Yes - those steps don't work in 2010. :) – Rory Apr 22 '15 at 10:23
Ok, good to know :) but you don't know the answer to the question? – Erik Dahlen Apr 22 '15 at 10:30
I don't have 2013 available here to test. If you do it manually, then in the immediate window enter: `?activesheet.listobjects(1).querytable.connection`, what output do you get? – Rory Apr 22 '15 at 11:01
Sry but I don't understand which "immediate window" I should enter it... – Erik Dahlen Apr 22 '15 at 11:12
Open the VB editor, press Ctrl+G (or View - Immediate Window) and paste the code in, then press Enter. – Rory Apr 22 '15 at 11:16
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/75930/discussion-between-erik-dahlen-and-rory). – Erik Dahlen Apr 22 '15 at 11:17
that same link you provided gives an option to do the same thing using PowerQuery, which is available as a free add-in to Excel 2010. – guitarthrower Apr 22 '15 at 15:52
yes, I saw that, but is it easier do write a macro for PowerQuery to fetch data than for PowerPivot? And do you know how to do this? – Erik Dahlen Apr 23 '15 at 09:49

How to copy the PowerPivot data into the Excel workbook as a table with VBA?

0 Answers0