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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kalspiros
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 https://hatfullofdata.blog/excel-power-query-vba-to-edit-a-parameter-value/ 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")
    ActiveWorkbook.RefreshAll
    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:

kalspiros_0-1659524633243.png

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.

 

 

let
    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),
in
    #"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!

3 REPLIES 3
lbendlin
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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors