[VBA] Compile Error question

[borski88]

I am new to VBA and I am having an issue with this code, if someone could debug it for me that would be very much appreciated.
I edited out part of the URL for security reasons.

When I run this I get the following messege:

Compile Error:
User-defined type not defined.

and it highlights the following section:

       Sub WebQuery()
         
        'Add: (menu) Tools/References/'Microsoft XML V6.0' & 'Microsoft Forms 2.0 Object Library'
         
        Dim [COLOR=#ff0000][B]httpRequest As XMLHTTP[/B][/COLOR]      'XML V6.0
        Dim DataObj As New MSForms.DataObject   'Forms 2.0
         
        'clear any old data
        Sheets("QuerySheet").Activate
            For Each QT In ActiveSheet.QueryTables
                QT.Delete
            Next QT
        ActiveSheet.Cells.Clear
        
        Date = Format(Range("Main!B1"), "YYYY-MM-DD")
     
        'prepare data download
        Set httpRequest = New XMLHTTP
           httpRequest.Open "GET", "https://****.****.com/?end_date=" & Date & "&fc=****&start_date=" & Date & "&submit=Fetch+Data", False
           
        httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        httpRequest.send ""
        'download data, and put in clipboard
        DataObj.SetText httpRequest.responseText
        DataObj.PutInClipboard
         
        'paste clipboard to sheet
        Sheets("QuerySheet").Range("C1").Select
        Sheets("QuerySheet").PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
                    
        Sheets("Main").Select
        Range("A1").Select
                    
        End Sub

 

[mole999]

found this as a function

<code>Function GetHTTPResult(sURL As String) As String
    Dim XMLHTTP As Variant, sResult As String

    Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    XMLHTTP.Open "GET", sURL, False
    XMLHTTP.Send
    Debug.Print "Status: " & XMLHTTP.Status & " - " & XMLHTTP.StatusText
    sResult = XMLHTTP.ResponseText
    Debug.Print "Length of response: " & Len(sResult)
    Set XMLHTTP = Nothing
    GetHTTPResult = sResult
End Function</code>

it might give clues how to format your needs

source excel vba http request download data from yahoo finance - Stack Overflow

 

[Rick Rothstein]

I am new to VBA and I am having an issue with this code, if someone could debug it for me that would be very much appreciated.
I edited out part of the URL for security reasons.

When I run this I get the following messege:

Compile Error:
User-defined type not defined.

and it highlights the following section:

       Sub WebQuery()
         
        [COLOR="#FF0000"][B]'Add: (menu) Tools/References/'Microsoft XML V6.0' & 'Microsoft Forms 2.0 Object Library'[/B][/COLOR]
         
        Dim [B]httpRequest As XMLHTTP[/B]      'XML V6.0
        Dim DataObj As New MSForms.DataObject   'Forms 2.0
         
        'clear any old data
        Sheets("QuerySheet").Activate
            For Each QT In ActiveSheet.QueryTables
                QT.Delete
            Next QT
        ActiveSheet.Cells.Clear
        
        Date = Format(Range("Main!B1"), "YYYY-MM-DD")
     
        'prepare data download
        Set httpRequest = New XMLHTTP
           httpRequest.Open "GET", "https://****.****.com/?end_date=" & Date & "&fc=****&start_date=" & Date & "&submit=Fetch+Data", False
           
        httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        httpRequest.send ""
        'download data, and put in clipboard
        DataObj.SetText httpRequest.responseText
        DataObj.PutInClipboard
         
        'paste clipboard to sheet
        Sheets("QuerySheet").Range("C1").Select
        Sheets("QuerySheet").PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
                    
        Sheets("Main").Select
        Range("A1").Select
                    
        End Sub

You are getting that error because you did not set the Reference that what I commented in red instructed you to do.