As a small project, we were tasked with creating a data feed from a text report genereated by JD Edwards (not OneWorld, an older version) and our application. Instead of going the FTP and text file parsing route, I decided to try our first venture into a web service. Since the JD Edwards group were using Excel 2000 to manipulate the report before sending it to our system, I figured a VBA macro could send the data for them, using the XMLHTTP object.
‘Notes: Using late binding so the project won’t need explicit references
‘ Using MSXML 2.5 object model to be sure it will run on most PCs
Public Sub SendDataToEnCore()
Dim oXML As Object
Dim oDom As Object
Dim oNode As Object
Dim sXML As String
Dim nResult As Integer
Dim sResponse As String
On Error GoTo Handler
Set oXML = CreateObject(“Microsoft.XMLHTTP”)
Application.Cursor = xlWait ‘Change the cursor to a wait cursor
Application.ScreenUpdating = False ‘Stop screen redraw
With oXML
‘Call the service, it only takes one parameter, the XML string
.Open “POST”, & _
“http://test.com/Redeployment.asmx/Redeployment_Update“, False
‘The following line is necessary for the web service to recognize the post
.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”
Application.StatusBar = “Waiting for a response…”
‘Create XML is a function that loops the spreadsheet building an XML string
.Send CreateXML
End With
sResponse = oXML.responseText
With Application
.Cursor = xlDefault
.StatusBar = “”
.ScreenUpdating = False
End With
Set oDom = CreateObject(“MSXML.DOMDocument”)
oDom.loadXML (sResponse)
If oDom.hasChildNodes Then
‘Display the resulting message from the web service
Set oNode = oDom.documentElement.firstChild
nResult = MsgBox(oNode.Text, vbInformation, “EnCore Data Transfer”)
Else ‘No response at all
MsgBox (“The JDE upload failed. Please contact Development for assistance.”)
End If
Set oXML = Nothing
Set oDom = Nothing
Set oNode = Nothing
Exit Sub
Handler:
With Application
.Cursor = xlDefault
.StatusBar = “”
.ScreenUpdating = False
End With
MsgBox Err.Description
Set oXML = Nothing
Set oDom = Nothing
Set oNode = Nothing
End Sub
So far this has worked well for us. One of the issues we encountered was URLEncoding the XML string before sending it. Otherwise, it just won’t parse properly on the web service end. Here is an abbreviated version of the web service function that is called by the above Excel VBA function.
Public Function Redeployment_Update(Byval sInput As string) As String
Dim dt As DataTable
dim n as Integer
sInput = cstr(sInput)
n = sInput.Length
If n <= 0 Then
return “File contains no data”
End If
‘ Load the input XML string into a DataTable
dt = LoadDataTable(sInput)
Dim drCurrent As DataRow
For Each drCurrent In dt.Rows
ProcessRow(drCurrent) ‘Our custom function to parse a row
next
return “File received OK. Characters Received=” & n
End Function
