Obviously the formula was initially created in the Excel Power query dialogue then adjusted and concatenated to accommodate what I was trying to achieve.
I did this about 4 years ago and remember it was quite an effort to get it to work, but it did what I need it to do.
I can't post any more of it as it belongs to a client.
sTablename was a variable used as the spreadsheet had multiple data sources, and multiple outputs all gathered into a complicated output structure.
This was all driven from Access recordsets in multiple loops, not done in Excel.
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim newSQL As String
newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
Set qdf = db.CreateQueryDef("tempQry", newSQL)
Obviously the formula was initially created in the Excel Power query dialogue then adjusted and concatenated to accommodate what I was trying to achieve.
I did this about 4 years ago and remember it was quite an effort to get it to work, but it did what I need it to do.
I can't post any more of it as it belongs to a client.
sTablename was a variable used as the spreadsheet had multiple data sources, and multiple outputs all gathered into a complicated output structure.
This was all driven from Access recordsets in multiple loops, not done in Excel.
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim newSQL As String
newSQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
Set qdf = db.CreateQueryDef("tempQry", newSQL)
Not my area of expertise but if you can do this manually through the interface, you should be able to record the actions into an excel macro which you can then edit as required
Start by creating the power query you need in Excel manually.
You can then peek at the code using the advanced editor:
This code is what you would need to replicate in VBA using something similar to the syntax I posted earlier.
No one is going to build this for you without you creating the initial power query first, then trying it out I'm afraid.