Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I
Helper I

Tricking VBA when updating Power Query

Hi all,


I have a spreadsheet where I need the user to add their API Token so that I will get some JSON data.

I do not want them to drop the value in a cell because they might forget about it and save it there.

This is what I have set on VBA as seen on this link to get the token via a pop-up



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 "api_key", InputBox("Please provide your token", "API Token")
    ChangeParameterValue "api_key", " "
End Sub



Everything works perfectly fine except a tiny hiccup.

As you can see on the final step, I am calling ChangeParameterValue again to reset the token so that it will not be saved in the api_key query.

If I will accidently place a wrong token, the "Access Web content" window will pop up, which make sense:


If I will try immediately after and add a correct token, the "Access Web content" comes back again.

It seems that the macro runs all the way and resets the api_key before the whole ActiveWorkbook.RefreshAll is completed.


If I remove the ChangeParameterValue "api_key", " " everything works fine, but that's something I want to avoid since it will keep the token in the query for further users to see.


FYI, the query that calls JSON, below. api_key as seen above and Start_Date/ End_Date as set by the user in relavent cells.



    apiToken = api_key,
    url = "https://************/api/clients/invoices/",
    query = [
        start = Start_Date,
        end = End_Date,
        format = "json"
    headers = [
        Authorization = "Token " & apiToken,
        #"Content-Type" = "application/json"
    Source = Json.Document(Web.Contents(url, [Headers=headers, Query = query])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Coverted to Table"



This problem might have numerous approaches on resolving or it should be better placed on a VBA forum thus, apologies for any possible confusion.


Many thanks for any help!

Super User
Super User

What's the lifespan of that token?  Are you chasing a non-existing problem?

Hey @lbendlin thanks for your message! Not much on tokens tbh but my understanding is that they have no expiry date. They stick with a particular user. I am not sure I understood your second query.

Tokens normally expire rather fast (an hour or so) and you usually need to get a refresh token.


So if your process stores the initial token, and it expires after an hour - that would mean the stored token can not do any harm and doesn't need to be cleared.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors