Changing Web Query URL with VBA

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
Not sure if I'm using the exact terminology correctly. But what I'm trying to do is change the source of query that is connected to coin market cap API(for tracking cryptocurrency prices) with VBA.

The original Link is:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?CMC_PRO_API_KEY=796ca343-261a-4031-8819-7fee2bb27019&start=1&limit=5000

I want to change it to:
https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?CMC_PRO_API_KEY=ABCD&start=1&limit=5000

Ultimately I will create a popup that allows the user to input their API key and have it update the source to change the dummy API key to their API key that they have entered. But I can figure most of that out, except I'm having trouble figuring out how to change the source. Any help would be appreciated.


Spreadsheet Google Drive Link:
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,764
Try this macro:
VBA Code:
Public Sub Modify_API_key()

    Dim API_key As String
    Dim p1 As Long, p2 As Long
    
    API_key = InputBox("Enter API key", "CoinMarketCap API")
    
    If API_key <> "" Then
    
        With ActiveWorkbook.Queries("CoinMarketCap")  'query name is case-sensitive
            p1 = InStr(1, .Formula, "CMC_PRO_API_KEY=", vbTextCompare)
            If p1 > 0 Then
                p1 = p1 + Len("CMC_PRO_API_KEY=") - 1
                p2 = InStr(p1, .Formula, "&")
                .Formula = Left(.Formula, p1) & API_key & Mid(.Formula, p2)
            End If
        End With
    
        ActiveWorkbook.Connections("Query - CoinMarketCap").Refresh
    
    End If
        
End Sub
 
Solution

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
Try this macro:
VBA Code:
Public Sub Modify_API_key()

    Dim API_key As String
    Dim p1 As Long, p2 As Long
   
    API_key = InputBox("Enter API key", "CoinMarketCap API")
   
    If API_key <> "" Then
   
        With ActiveWorkbook.Queries("CoinMarketCap")  'query name is case-sensitive
            p1 = InStr(1, .Formula, "CMC_PRO_API_KEY=", vbTextCompare)
            If p1 > 0 Then
                p1 = p1 + Len("CMC_PRO_API_KEY=") - 1
                p2 = InStr(p1, .Formula, "&")
                .Formula = Left(.Formula, p1) & API_key & Mid(.Formula, p2)
            End If
        End With
   
        ActiveWorkbook.Connections("Query - CoinMarketCap").Refresh
   
    End If
       
End Sub
Awesome John, it worked like a charm. Thank you so much for taking the time to write a macro for this! Happy Holidays Buddy!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,143
Messages
5,640,386
Members
417,139
Latest member
bdmprasenjit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top