• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Modify Power Pivot Query from a Data Source using VBA

matute

New Member
Hello there! I have been days looking for a solution to my problem.
Y have a complex tool in excel that uses information from 20 different datasources loaded in Power Pivot.

Thing is, that queries bringing iformation from DataBase have date ranges. And it is very annoying edit every connection to edit the query and update the data range every time. It takes for ever.

Solution I am needing is to have a simple table in the main excel file, where I have the dates for each data source.
What I need is, load all the dates in this excel table, and then using VBA modify all the queries from the datasources working in Power Pivot.

I dont know if I was clear enough. If not, let me know.

Thank you for your help!
 
It would be very helpful if you uploaded a sample of what you have and a sample of what you expect the end result to look like. In this manner, we can produce some code that is specific to your needs.
 
Hello, Thank you for your responses.
I am not using Power Query but Power Pivot.

For instance, one of the datasourses called Customers, is connected to SQL server and runs a query where it defines table, fields and conditions.

Let's say that datasource runs against SQL server, database MyDatabase a query
select * from footable where datestart BETWEEN '2019-01-01' and '2019-01-31'

Only way I found is get into Power Pivot, in the menu select Design->Table Properties and edit the query.

Since I have to do it for about 12 datasources in same power pivot, and every time I change a query, it runs when I save it, and it brings thousands to millions of records....... it is a painful half of day doin it manually.

Makes sense?
 
In all instances, except when you are using Excel 2010. You should always use PQ to query data and perform any required transformation before loading data to model.

PQ is the ETL tool where you should prepare data for analysis to avoid the issue you mention.

If you are using Excel 2010, unfortunately VBA does not have access to control data model objects. That was introduced in Excel 2013.
 
Back
Top