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