Hi,
Currently I have a get and transform query that is used to pull our scheduled production for upcoming weeks and transform it into a usable data table that gets loaded into Power Pivot, this scheduling file is done in Excel and each worksheet shows production for each week of the year.
I have multiple workbooks that use this particular transform query. Currently I am just copying the query and pasting into Queries and Connections of a new workbook when I need to reuse it, however, if I ever need to make an edit to the query I have to go into each and every workbook and make the same edit in each one.
Is there a better way of managing a core query that is used across multiple workbooks so that if I ever need to make an edit I only have to do it to the main query, all the rest can be updated?
Thank you.
Hi Louis,
You'll have to use vba for this.
Basically, make changes in only 1 workbook query, and the query text will become the reference for all other books:
Set oledb = Workbooks("x.xlsx").Connections("Query - Query1").OLEDBConnection
Workbooks("x.xlsx").Queries.Item("Query1").Formula=queryFormula
oledb.Refresh
Set oledb = Workbooks("y.xlsx").Connections("Query - Query1").OLEDBConnection
Workbooks("y.xlsx").Queries.Item("Query1").Formula=queryFormula
oledb.Refresh
Of course, a loop can be built from a folder files, or from a list of locations.
See an idea here: https://stackoverflow.com/questions/60158029/update-queries-in-excel-using-vba
