I am trying to edit the source of one of my queries using VBA. This is what I have so far:
Dim mFormula As String
mFormula = _
"let Source = Excel.Workbook(File.Contents(wbname), null, true) in Source"
query1 = ActiveWorkbook.Queries.Add("LATEST", mFormula)
I set wbname previously in my code. "LATEST" is already added, instead of delete it and read it, I would just like to change the source. Is this possible?
You can use ActiveWorkbook.Queries.Item to get the query you want and use the Formula property to update the query's formula, like so:
ActiveWorkbook.Queries.Item("LATEST").Formula = "let MyNewFormula = 1 + 1 in Source"
Note: this only works on Excel 2016 or later.
I beleive you'd better avoid such methods as they can cause compatibility problems as well as some other.
If you learn M, you probably won't need to edit code with VBA.
A bit late to the party, but additionally, for anyone who views this moving forward one can use:
Thisworkbook.Queries("LATEST").Formula = mFormula
Note, ThisWorkbook is preferable to ActiveWorkbook.
It is not recommended to use Thisworkbook.Queries(index).Formula = "newFormula" to change your queries, but I have not discovered any other method to update the connections.
The drawback working with this method is that you can not change any specific property of the connection (source, etc), but you can use the functions Split, Replace, Instr, Mid, etc to get exactly what you want.
Another drawback is that so often the new ".formula" throws syntax errors. For that, you will need a simple error handler.
That worked for me:
ActiveWorkbook.Queries.Add Name:="Qname", Formula:= _
"let" & " Source = Pdf.Tables(File.Contents(" & """" & FullPathN & """" & "), [Implementation=""1.3""])," & _
" Page1 = Source{[Id=""Page001""]}[Data]," & _
" #""Promoted Headers"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & _
" #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Column" & "1"", type text}})" & _
"in" & " #""Changed Type"""
FullPathN is full path name, string; Qname is query name, string
I don't know if this will work on MacOS excel. Can anybody test this? My task is for MacOS.
Thanks for this post. A way to manipulate and replace existing formula will be like this:
Sub EditAllWorkbookFormuals()
For Each q In ThisWorkbook.Queries
q.Formula = NewQuery(q.Formula)
Next
End Sub
Function NewQuery(MyQuery As String) As String
' Do you manipulation here of your MyQuery string
MsgBox MyQuery
' Use Select case, If, Inst, replace, and so on.
' .....
' Parse the new string back to the NewQuery
NewQuery = MyQuery
End Function
