CODEWITHSUNDEEP
excelvbapowerquery
Fismeister
Fismeister

Reputation: 31

Extracting PowerQuery script from Excel fie

I'm currently developing an Excel template that pulls data from various datasources using a PowerQuery script. This script will be running on a weekly basis by multiple people. And the end of every year, the templates used could be subject to an audit. One of the requirements for this template to be 'compliant' so auditors can rely on the content is that the PowerQuery script is exactly the same for all the individual templates. I wanted to check this by writing a script in VBA that pulls the PowerQuery script from the PowerQuery editor in 'plaint text'and calculates the hash value of that script. This hash value will be compared with the hash value of the script stored in a standard .txt file external from the template. Calculating the hash values shouldn't be a problem, but what I can't figure out is if it's possible to extract the PowerQuery script from an Excel file in VBA. Would anyone of you know if this is possible and if so, how I can do this?

I tried to figure out if I could use the WorkbookQuery object functionality in VBA based on information on the Microsoft website: https://learn.microsoft.com/en-us/office/vba/api/excel.workbookquery?source=recommendations. However, didn't manage to find a way to extract the script.

Upvotes: 2

Views: 318

Answers (1)

Fismeister
Fismeister

Reputation: 31

Managed to solve it using the link 'Horseyride' provided. Thanks for that! Please find the stripped down version of the code I used below:

Sub fetch_queries()

Dim qry As WorkbookQuery
query_q = ActiveWorkbook.Queries.Count

For i = 1 To query_q
Set qry = ActiveWorkbook.Queries(i)
qry_formula = qry.Formula
Next i

End Sub

Upvotes: 1

Related Questions