Hi,
I've distributed a workbook containing multiple dependent queries to several users and I'd now like to replace one of the queries with a different version. Rather than re-issue the entire workbook, which would not be popular, is there a best way of achieving this? I'm not sure that I want to ask my users to copy/paste M-code in the Advanced Editor but, if necessary, I could do that.
Just thought I'd check to see if there's a simpler way to swap over queries. Many thanks in advance.
Pieter
You can do it with code. For example, let's say you have the new query in A2 of Sheet1 of a workbook, add this code to it:
Sub ChangeQuery()
Dim wb As Workbook
Set wb = Workbooks.Open("path to the file here")
wb.Queries("put the query name here").Formula = ThisWorkbook.Sheets("Sheet1").Range("A2").Value
End Sub
and amend the query name and file path of the workbook, then save it as a macro enabled workbook. You can then email that to the users and ask them to run the macro.
Hi Velouria,
Very many thanks for a really helpful answer. I tried it and it worked like a dream first time.
Just to be clear, for anyone else who's reading this, the new query needs to be open in View/Advanced Editor, and the entire M-code needs to be copied and then pasted into (in this case) cell A2 of the macro-enabled workbook.
