Changing Web Query URL with VBA

[Twollaston]

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:

Portfolio 2.0.xlsm

drive.google.com

 

[John_w]

Try this macro:

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

 

[Twollaston]

Try this macro:

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!