Passing parameters from VBA to PowerQuery

Question

Good day

I am looking for a way to pass a username and API key to PowerQuery from registry information via VBA. I don't want the information to necessarily be contained in a cell on a sheet because I would like to restrict functionality to a user's PC.

Any thoughts on how I can pass the variable / parameter from the registry or from a VBA variable to PowerQuery?

As a POC, I do currently have the information in a hidden cell on a sheet and PowerQuery reads it with:

= Excel.CurrentWorkbook(){[Name="username"]}[Content]{0}[Column1]

Much appreciated.

Renier Wessels · Accepted Answer · 2022-01-21 10:41:47Z

1

I've figured it out from another thread here: https://hatfullofdata.blog/excel-power-query-vba-to-edit-a-parameter-value/

Option Explicit

Sub ChangeParameterValue(ParameterName As String, ParameterValue As String)

    Dim qry As WorkbookQuery
    Dim formula As Variant
    
    '=== Get the query
    Set qry = ThisWorkbook.Queries(ParameterName)
    
    '=== Split the formula into 3 parts and update the second one
    formula = Split(qry.formula, Chr(34), 3)
    formula(1) = ParameterValue
    
    '=== Update the parameter value
    qry.formula = Join(formula, Chr(34))
    
End Sub

Sub MySub()
    ChangeParameterValue "username", "[email protected]"
    ChangeParameterValue "api_key", "adfsd32564fdsfdsf8rr46we5"
End Sub

answered Jan 21, 2022 at 10:41

Renier Wessels

832 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing parameters from VBA to PowerQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related