none
How to fill API Key parameter RRS feed

  • Question

  • Hi All,

    I have a macro file which is using API link to import data and it is running successfully. 
    Now I am trying to import the data from same link but using Power Query.

    Can any body let me know how to use API key parameter n Power Query editor as per below mentioned VBA code ?

    Sub Button1_Click()
    
    'Create the XmlHttp Object
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    'Set the URL of the API Endpoint
    Url = "http://111.345.234.456/unknown_Api/api/DownloadReport/ABC"
    objHTTP.Open "GET", Url, False
    'Set the API Key
    objHTTP.SetRequestHeader "X-XXX-Key", "1234567890"
    
    'Send the API Request
    objHTTP.send
    
    'Define an object for holding the API Response
    Dim response
    
    'ensure that the call was successful
    If objHTTP.readyState = 4 Then
        ' get the response text
        response = objHTTP.responseText
        
        'define a path where the ATT Report should be saved.
        Dim strTempPath As String
        strTempPath = ThisWorkbook.Worksheets(1).Range("B1").Text
    
        'save response to the above file
        Open strTempPath For Binary As #1
           Put #1, 1, DecodeBase64(response)
        Close #1
        
        MsgBox "Report downloaded to: " & strTempPath
        
    Else
        MsgBox "Error: " & objHTTP.Status
    End If
    
    End Sub


    AskQuery1984

    Wednesday, July 29, 2020 12:10 PM

All replies

  • Hi there. Is the code you pasted supposed to be using Power Query, or is this your non-PQ macro code?

    Ehren

    Friday, July 31, 2020 7:09 PM
    Owner
  • Hi Ehren,

    Above code is non-PQ code. but I am trying to achieve this goal via Power Query M-code.

    I am unable to understand how to use change the VBA code to M-Query as in Code some Header information provided. so looking for suggestions.   

    Url = "http://111.345.234.456/unknown_Api/api/DownloadReport/ABC"
    objHTTP.Open "GET", Url, False
    'Set the API Key
    objHTTP.SetRequestHeader "X-XXX-Key", "1234567890"
    
    'Send the API Request
    objHTTP.send



    AskQuery1984

    Saturday, August 1, 2020 6:54 AM
  • Have you gotten the solution working manually in Power Query? If so, please paste the M code here. Someone should then be able to provide guidance on how to create it via VBA.

    Ehren

    Monday, August 10, 2020 4:17 PM
    Owner