Modifying Datasource Query using VBA in Power Pivot

[matute]

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!

[Bob Phillips]

You could do that easily if you load the data through Power Query and then access that datamodel. You can build a parameter table in Excel that you can pull into PQ and use that to filter the data. If you want to try this, provide more details and we can help you get there.

[matute]

Hello xld, all the datasoruces impact on external records. Excels and SQL server.
At this point, and since this tool has not been developed by me, if there is a chance to modify the queries into the datasets that impact SQL Server it would be great.
Not sure how adding Power Query would help and not a pro user :(
Thanks

[Bob Phillips]

If you were to use Power Query, you would need to recreate the connections, not sure if that is possible if you didn't develop it.

The general thrust of what I am saying is:
- build an Excel parameter table of all the variables that you want
- build queries for all of the datasources (complete data)
- in each query read in the relevant parameter(s) and filter the data
- load the queries into the datamodel.

Power Query helps because you can read the datasources as you can with PowerPivot, manipulate/transform that data, and then load into the datamodel. PowerPivot, or regular pivot tables, can then access that datamodel.