Re: Using VBA in Excel to Pull Item Table

CDS_Josh — Thu, 24 Mar 2011 19:34:54 GMT

Thanks Paul. Always a second set of eyes will do the trick. I have been staring at this for at least an hour

Re: Using VBA in Excel to Pull Item Table

Method_Paul — Thu, 24 Mar 2011 19:22:59 GMT

Case UCase("Qty on Hand")
                Cells(iRow, 3) = Format(objChild.Text, "#,###")
            Case UCase("Avg Cost")
                Cells(iRow, 4) = Format(objChild.Text, "$#.00")

-These should be the names of the fieds, not the caption of the columns.

Paul

Using VBA in Excel to Pull Item Table

CDS_Josh — Thu, 24 Mar 2011 19:10:41 GMT

Just a quick thanks in advance!!

Here is my issue:

I am trying to use the VBa example to pull 4 fields from the item table into an excel sheet. i am not sure where i am off because only the first 2 columns are being populated. But it seems like ALL the rows are there. I used the Import/Export tool to get the entire table list to make sure the field where correct and to make sure there was some actual values for those fields. There was, so I am puzzled.

The fields I need are:Name,PurchaseDesc,QuantityOnHand,AverageCost

Here is my VBA Code:

Sub butGetItemList_Click()
On Error GoTo Myerr
'This sub gets a total of outstanding invoices lists the invoices in Excel.
    Dim objSclient As MSSOAPLib30.SoapClient30
    Dim sResult As String 'tells us Success or error
    Dim sXML As String 'will be used to store the XML we get back

    'Get the Method Account values from excel sheet
    Dim sCompanyAccount As String
    Dim sUserName As String
    Dim sPassword As String
    sCompanyAccount = Sheet1.Cells(5, 4)
    sUserName = Sheet1.Cells(6, 4)
    sPassword = Sheet1.Cells(7, 4)

    'Write my SQL statement to get any outstanding invoices
    Dim sSelectFields As String
    Dim sSelectFrom As String
    Dim sSelectWhere As String
    Dim sSelectGroupBy As String
    Dim sSelectHaving As String
    Dim sSelectOrderBy As String
    sSelectFields = "Name,PurchaseDesc,QuantityOnHand,AverageCost"
    sSelectFrom = "Item"
    sSelectWhere = ""
    sSelectGroupBy = ""
    sSelectHaving = ""
    sSelectOrderBy = "Name"

    'Declare XML objects
    Dim xmlDoc As New MSXML2.DOMDocument40
    Dim objNodeList As MSXML2.IXMLDOMNodeList
    Dim objChild      As MSXML2.IXMLDOMNode
    Dim objAttribute As MSXML2.IXMLDOMAttribute

    'Point the SOAP API to the web service that we want to call...
    Set objSclient = New SoapClient30
    Call objSclient.mssoapinit(par_WSDLFile:="https://www.methodintegration.com/MethodAPI/service.asmx?wsdl")

    'Call the MethodAPI web service
    sResult = objSclient.MethodAPISelect_XMLV2(sCompanyAccount, sUserName, sPassword, "", sSelectFrom, _
    sSelectFields, sSelectWhere, sSelectGroupBy, sSelectHaving, sSelectOrderBy)
    Set objSclient = Nothing

          'START: Make sure the request was succesful
          If InStr(1, UCase(sResult), UCase("Success")) = 0 Then
            'failed, give a message
            If Strings.Left(sResult, Len("<?xml")) = "<?xml" Then
              xmlDoc.LoadXML (sResult)
              Set objNodeList = xmlDoc.getElementsByTagName("MethodAPI")
              For Each objAttribute In objNodeList.Item(0).Attributes
                If objAttribute.Name = "response" Then
                  MsgBox "Error from the MethodAPI: " & vbCrLf & vbCrLf & objAttribute.Value, vbExclamation, "Method API Warning"
                End If
              Next
            Else
                  MsgBox "Error from the MethodAPI: " & vbCrLf & vbCrLf & objAttribute.Value, vbExclamation, "Method API Warning"
            End If
            Exit Sub
          Else
            'success
          End If
          'END: Make sure the request was succesful


    'Handle the returned XML - An object of type IXMLDOMNodeList is declared to parse the Web service's returned data.
    Dim iRow As Integer
    Dim i As Integer

    xmlDoc.LoadXML (sResult)
    Set objNodeList = xmlDoc.getElementsByTagName("Record")

    Sheets("ITEM_LIST").Select
    iRow = 1 'start a row 12
    'create title row
    Cells(iRow, 1) = "Item"
    Cells(iRow, 2) = "Item Description"
    Cells(iRow, 3) = "Qty on Hand"
    Cells(iRow, 4) = "Avg Cost"

    'loop through each record of the xml and dump the values into the spread sheet
    For i = 0 To (objNodeList.Length - 1)
        iRow = iRow + 1 'set the new row
        For Each objChild In objNodeList.Item(i).ChildNodes
            Select Case UCase(objChild.nodeName)
            Case UCase("Name")
                Cells(iRow, 1) = objChild.Text
            Case UCase("PurchaseDesc")
                Cells(iRow, 2) = objChild.Text
            Case UCase("Qty on Hand")
                Cells(iRow, 3) = Format(objChild.Text, "#,###")
            Case UCase("Avg Cost")
                Cells(iRow, 4) = Format(objChild.Text, "$#.00")
            End Select
        Next
    Next i

    'clean up
    Set objSclient = Nothing
    Set objNodeList = Nothing
    Set xmlDoc = Nothing
    Set objChild = Nothing

    Range("A:A").HorizontalAlignment = xlRight

    Exit Sub
Myerr:
    MsgBox Err.Description, vbCritical, "Error: " & Err.Number
End Sub

Method:API Q&A

Re: Using VBA in Excel to Pull Item Table

Re: Using VBA in Excel to Pull Item Table

Using VBA in Excel to Pull Item Table