Fixing Range Select

Question

I am getting the error Run-time error '1004': Application-defined or object-defined error on Range("A1").Select. I have looked through a couple of other similar questions, but I am currently unable to find a resolution. I would prefer to change the Select to a dimmed range, but since I don't understand how the code is choosing which sheet to select, I'm not sure how to define the range.

Here is the code:

Sub ServiceNowRestAPIQuery()

    ' Replace with your Service Now Inctance URL
    InstanceURL = "https://dev#####.service-now.com"
    ' Replace with your Authorization code
    AuthorizationCode = "Basic ########################"
    ' Add more tables as comma seperated with no spaces
    TableNames = ("incident,problem")

    Dim ws As Worksheet
    Dim objHTTP As New WinHttp.WinHttpRequest
    Dim columns As String
    Dim Header As Boolean
    Dim jsonString As String
    Dim Resp As New MSXML2.DOMDocument60
    Dim Result As IXMLDOMNode
    Dim ColumnsArray As Variant

    TablesArray = Split(TableNames, ",")

    For x = LBound(TablesArray) To UBound(TablesArray)

        'Table Choices
        Select Case TablesArray(x)

        Case "incident"
            Set ws = ThisWorkbook.Sheets("incidents")
            columns = "number,company,close_notes,impact,closed_at,assignment_group"
            ColumnsArray = Split(columns, ",")
            OtherSysParam = "&sysparm_limit=10"
            SysQuery = "&sysparm_query=active%3Dtrue"

        Case "problem"
            'Sheet name
            Set ws = ThisWorkbook.Sheets("problem")
            'Columns to Query
            columns = "number,short_description,state"
            ColumnsArray = Split(columns, ",")
            'Query filter Parameters
            OtherSysParam = "&sysparm_query=state=1"
            'Other Query Parameters
            SysQuery = ""
        End Select

        Url = InstanceURL & "/api/now/table/"
        Table = TablesArray(x) & "?"
        sysParam = "sysparm_display_value=true&sysparm_exclude_reference_link=true" & OtherSysParam & SysQuery & "&sysparm_fields=" & columns
        Url = Url & Table & sysParam
        objHTTP.Open "get", Url, False
        objHTTP.SetRequestHeader "Accept", "application/xml"
        objHTTP.SetRequestHeader "Content-Type", "application/xml"

        ' Authorization Code
        objHTTP.SetRequestHeader "Authorization", AuthorizationCode
        objHTTP.Send                             '("{" & Chr(34) & "short_description" & Chr(34) & ":" & Chr(34) & "Test API2" & Chr(34) & "}")

        Debug.Print objHTTP.Status
        Debug.Print objHTTP.ResponseText
        ws.Select
        Header = False
        i = 1
        Range("A1").Select
        Cells.Clear

        Resp.LoadXML objHTTP.ResponseText
        For Each Result In Resp.getElementsByTagName("result")
            For n = LBound(ColumnsArray) To UBound(ColumnsArray)
                If Header = False Then
                    ActiveCell.Offset(0, n).Value = ColumnsArray(n)
                End If
                ActiveCell.Offset(i, n).Value = Result.SelectSingleNode(ColumnsArray(n)).Text
            Next n
            i = i + 1
            Header = True
        Next Result
        'MsgBox Time
    Next x
End Sub

This code is inspecting both Sheets("incident") and Sheets("problems"), but I'm getting lost when trying to discover how it is choosing which sheet to select for the Range("A1").Select code and why it is returning the 1004 error. Please assist, thank you very much! :)

By the way, this code is for integrating an Excel workbook with a ServiceNow instance via the REST web services. More information and the source of the code can be found on ServiceNowElite's ServiceNow to Microsoft Excel Integration webpage.

Answer 1

You don't need to Select here:

ws.Select
...
Range("A1").Select
Cells.Clear

can just be

ws.UsedRange.Clear