All Questions
Tagged with powerpivot vba
142 questions
0
votes
1
answer
67
views
Apply PivotTable filter
I created a table from Power Pivot.
I am trying to apply a filter to the pivottable.
It is constantly coming up with this error:
I tried
ThisWorkbook.Sheets("DATA").PivotTables("...
- 129
1
vote
1
answer
1k
views
Retrieving data from DataModel (Power Pivot) within VBA
first of all, apologies. I am a relatively new VBA user, and I am a bit lost in the search for the answer to the question below.
I am creating a UserForm which will include some ComboBox to get user's ...
- 13
1
vote
1
answer
286
views
Cubefield items
I am trying to set a specific value, specified in a cell of a sheet, for a CubeField of a power pivot. I can't enter the item as I would normally do with a simple pivot table, using the PivotItems ...
- 11
0
votes
1
answer
359
views
Is it possible to automatically add a power query to the data model using VBA on excel?
I have a worksheet that is set up to eventually have many power queries. I want to be able to use the pivot table and performance features of Excels Data Model. I have automated getting queries from a ...
0
votes
1
answer
37
views
Reformatting a Table using Power Pivot or VBA or excel
I have a table with over 35000 rows that i need to reformat. I get this type of file every 2 days and would like to know if there is a faster and easier way to reformat the data.
[Original File]
[...
0
votes
1
answer
371
views
Refreshing Queries and PivotTables subsequently with VBA
I have an issue that I cannot seem to find a proper solution for.
Basically I'm creating an excel dashboard which gets data from specific excel files, based on a file path that the user can choose. ...
2
votes
1
answer
3k
views
Change Pivot Table data source from External Connection to PowerPivot
We have large Excel reports with LOTS of PivotTables. Is there any way to change the data source on the existing PivotTables to change them from an External connection to use the internal Data Model ...
- 2,022
0
votes
0
answers
952
views
Access data from Power Pivot Data Model in VBA
I am using under Excel 2016 the Student Data Model sample workbook with completed Data Model provided by Microsoft and I have a simple question: how can I access Data Model data from VBA with a ...
- 175
0
votes
1
answer
61
views
Ignore Tablename in ADODB recordset field name
I want to get data from an Excel Power Pivot model to an ADODB recordset. This works, but the field names of the recordset are preceded by the tablename: Tablename[Columnname] instead of just the ...
- 1
1
vote
0
answers
64
views
VBA code to Loop through Power Pivot Slicers and safe each item in a folder
Below code works perfectly in creating PDFs from an Excel table PivotTable and slicer however falls short when connected to a PowerPivot PivotTable with a slicer. I attempted with the modified query ...
0
votes
1
answer
229
views
Why pivotTable dont update after using RefreshAll or .PivotCache.Refresh - VBA
In resume, It's an extract from SAP ERP, copy/paste in a table, this table is treated by Power Query and exported to a Pivot Table.
I'm doing a dashboard with VBA + PowerQuery + SAP ERP data, my doubt ...
0
votes
1
answer
404
views
VBA - Renaming column titles/names on power pivot table
The GetMeasure caption parameter does not seem to affect the column name of the pivot table. In a regular pivot table, the caption can be used to rename the title of the column. I cannot figure out ...
- 1
1
vote
0
answers
2k
views
Excel Data Model Refreshing a Single Table via VBA
This is for anyone who may be looking to refresh a single table within the Power Pivot Data Model instead of the whole Model.
Why?
Well, when the excel workbook is opened, I wanted the users to only ...
- 505
0
votes
1
answer
191
views
Cannot add values field properly in a Pivot Table using a Data Model
I am trying to add a Pivot Table field values to a Pivot Table backed by multiple tables in a Data Model and I am quite struggling on the way to do that.
I am using the following code to add rows but ...
0
votes
1
answer
611
views
Setting Rows in Pivot Table with VBA
Is there a way to optimize this code or does it have to be listed as I have it below? This correctly adds fields A & B to my pivot as expected but would prefer rather than repeating each 'With' ...
- 11
0
votes
0
answers
60
views
Automating process in Excel
I have an excel spreadsheet that looks at road work licenses and there is a bit of a manual process that I am looking to automate but do not know how to.
The main purpose of the report is to look at ...
- 57
0
votes
1
answer
1k
views
"Unable to set the CurrentPage property of the PivotField Class" with Pivot Table Filtering Attempt
So I have been trying to figure out what I am doing wrong for the past two days and I can't seem to get past this error.
The error is "Unable to set the CurrentPage property of the PivotField ...
0
votes
0
answers
89
views
Some users can run the VBA and others get the error "subscript out of range"
It's my first time using VBA, so, I'm not sure if I'm going to explain my issue in the best way.
So, I have a pivot table that should be updated using the VBA code, however, every time I try to run it,...
- 1
0
votes
2
answers
325
views
How to compare and identify similarities between customer orders on the same list
I'm stuck trying to solve the below excel problem.
I have a list of customer orders that I'm trying to compare with each other to get a percentage total. The percentage should identify the ...
- 1
0
votes
1
answer
88
views
Out of Memory Error when running Drill Down Script
The purpose of the macro is to avoid creating a new worksheet each time a user double clicks / drills down on a pivot table value. Instead the script copies the data to a dedicated "DrillDown&...
- 421
0
votes
0
answers
232
views
How do you code to pull specific power pivot table data values using available variables in Excel R1C1 in vba with GETPIVOTDATA()
I have a complex scorecard that I update monthly in Excel. For each datastream I have pivot tables generated from the data model. The scorecard is pretty dynamic and updates each cell based on row (...
- 171
0
votes
3
answers
835
views
Refresh power pivot-power query
I have a table in power query that is fed from some Excel files, with this data I make an inner join with other catalog tables that I have and do operations on calculated columns and then add to the ...
- 37
0
votes
1
answer
688
views
Cubefields from multiple queries as Values for Pivot Table VBA
Background
Based on a PowerPivot import I retrieve multiple sources that have the same information to manipulate within excel and use Pivot tables to display basic calculations, in this process I ...
- 2,800
0
votes
0
answers
676
views
Refreshing using VBA takes an extremely long time compared to Refresh All; is there a way to fix this?
I have a number of reports that are designed to refresh themselves on open using some VBA. For my purposes, I cannot use the "refresh on open" property of connections.
For one specific ...
- 3
1
vote
0
answers
2k
views
Create Calculated field in Pivot Table using VBA
I need the expert help. I want to implement one formula for particular column on Pivot Table created by VB code.
I have written the code that will create the Pivot table with Count the Repeat Data and ...
- 589
0
votes
1
answer
290
views
How can I record the date-time of entering data in excel?
I have many Excel sheets for production and quality information as below. All the records are collected to a single excel file through Power Query.
Product1-Datasheet
Production Time Test Result
...
1
vote
1
answer
129
views
Excel Power query logic to get data that does not appear using logic
I am currently working on a report that shows a list of depot with the total of reports based on thier performance. the query only shows the depot that matches the conditions set in the query and the ...
1
vote
0
answers
76
views
Extracting Count of Distinct Values from one column based on filter from another column
I have multiple excel sheets where I need to extract the count of distinct values from column A associated with different comments(Column Q), logins(Column J) and brand name(Column S). I have a faulty ...
- 13
0
votes
1
answer
2k
views
Error 1004 - item could not be found in OLAP cube when using variables instead of hard codes
I need to copy data from a pivot table in an Excel file, which links to an external data source. The difficulty is to select the period in the pivot field.
The macro should start with an InputBox for ...
- 3
1
vote
0
answers
2k
views
Excel VBA create Data Model connection for Power Pivot table to get distinct values - error
In a macro, I'm trying to get distinct values to count, based on 2 criteria. Before, I've used a formula in an external report to add the counts =IFERROR(ROWS(UNIQUE(FILTER(. This works for most of ...
- 311
0
votes
0
answers
402
views
How to assign the "table name" of a query in the powerpivot datamodel
I'm using PowerPivot, and I'm creating a table in the datamodel using the next code:
Set ObjConn = ActiveWorkbook.Connections.Add2( _
Name:= "SomeConnName", _
Description:="...
- 115
4
votes
1
answer
7k
views
Application.CalculateUntilAsyncQueriesDone Crashes Excel
I have a OLAP Data Model that is published on the Power-BI Services. I built a report using CUBE formulas. I need to do a bit of Hiding and Showing Columns based on the CUBEVALUE results. And I need ...
- 678
1
vote
1
answer
140
views
Excel Power Pivot: Match next non-blank item after date match for customer across two tables using DAX
I need to create a calculated column in Table_Order that would find the next matching, non-blank Customer Status on or after the current Order Date for the current row Customer #.
The tricky part is ...
- 87
1
vote
0
answers
41
views
Maintaining Series Format in Chart: VBA Event for MDX Legend Changes
Is there a solution through VBA or by editing my MDX set, or anything else, to apply my Series formatting and make my measure/series colors consistent, even when isolated?
Or in other words, when I ...
- 11
0
votes
1
answer
676
views
How to copy and paste filtered power pivot table
Could you please help me with below problem statement,
I want to copy and paste a power pivot table from one sheet to other.
Problem here is this table is filtered with some specific values like ...
- 11
0
votes
0
answers
131
views
PowerPivot Sort by Column with VBA/C#
is it in any way possible to access the "Sort by Column" functionality in PowerPivot in Excel via VBA or even C#?
The functionality I mean is in Tab->PowerPivot->Manage and then "Sort By Column" for ...
- 53
0
votes
0
answers
323
views
VBA error '1004' while modifying data connection from Power Pivot
I am facing issues in VBA while modifying the path of a data connection used in a Power Pivot. I am working on Office 365 with Windows 10 and I am sharing my file on a business Sharepoint.
Through ...
0
votes
1
answer
526
views
Excel Slicer connected to a Power Pivot - Select multiple slicer items
I'm trying to make a report form that will ask what month and year you want the report to show. However, there is a specific pivot table that uses a four-year range to show trends data.
However, all ...
- 331
0
votes
2
answers
2k
views
Is there really no way to count the number of Visible Slicer Items in a Slicer connected to an OLAP data source?
I'm trying to loop through all slicer items from three slicers and then call another sub within the inner most loop. The slicers are connected to an OLAP data source. For each slicer I'd only like it ...
0
votes
0
answers
170
views
Is there a way to do a sum of columns in an Excel pivot table with a calculated field greyed out? (It contains distincts values fields)
I have a pivot table with distincts value. Short story it was needed for manual adjustment of quantity and also I have no access to Power Pivot.
Is there a way to sum columns inside the pivot table? ...
- 1
0
votes
0
answers
67
views
Can't synchronize pivot tables created with Power Pivot
I'm trying to use VBA to synchronize the filters of two pivot tables that are created with PowerPivot ... I want to apply the filter settings in pf "OU" from pt "pt_ECperMonth" to another pt named "...
0
votes
2
answers
1k
views
Loop Through Power Pivot, Pivot Table Filter and Print to PDF
The objective is to Loop through an Excel Power Pivot, Pivot Table and print each Filtered Result to PDF in a specific file location.
When the code gets to the for loop of the output it is giving me ...
- 181
2
votes
0
answers
377
views
How do I add Calculated Columns to a table in the PowerPivot Data Model using VBA?
I am aware that we can add/edit Measures to/in the Data Model from VBA using the ThisWorkbook.Model object. Is there a way to add Calculated Columns using VBA using the Model Object?
- 678
-2
votes
2
answers
90
views
Excel Convert Make/Model/Year Range into Individual Year/Make/Model rows
I'm attempting to systematically expand a table of Make/Model/Year Range combinations into individual year rows. I've tried looking for solutions but none seem to match my use-case and I don't know ...
- 13
0
votes
0
answers
331
views
Building Connection Between Power pivot and SAS using VBA
I'm trying to build a connection between Power Pivot and SAS so that I can import my SAS dataset directly to Power Pivot Data Model, using VBA. I'm using the Add2 function and keeps getting the same ...
- 1
1
vote
0
answers
372
views
Export Excel PowerPivot recordset as offline cube
I have a PowerPivot model that has Power Query as a source. Is it possible to create an offline cube from this?
I've tried creating a pivot table out of the PP and then using the pivotttable....
- 165
0
votes
1
answer
264
views
Run-time error 1004 when trying to select OLAP pivotfield array element
I'm having trouble looping through the items in my OLAP pivot field. I have an array which contains the names/items I want the filter to loop through, but I cannot get the pivot field to select an ...
- 1
0
votes
2
answers
1k
views
Dynamically refreshing CUBEVALUE
Trying to dynamically refresh a bunch of CUBEVALUE functions to build a report. One of the dimensions of CUBEVALUE refers to an Excel validation list.
What I am trying to do is loop (via VBA) over ...
- 117
4
votes
0
answers
876
views
Excel Data Model: set OLAP MaxDrillthroughRecords on existing connection via VBA
I'm using VBA to programmatically create excel workbooks with embedded pivottable objects. My current project is utilizing the Data Model as pivot cache source.
I need to be able to drill into ...
- 469