Using Excel and XML to post to web service

[sirsql]

Hello,

Right now I can post 1 xml record to a web service using Excel and a few lines of code but I need the ability to post about 100 records at a time so I need some sort of loop (and not an infinite one)...

Issues-


So I think I need a vba loop, perhaps with built-in pauses because I think the web service "response" needs to be removed/cleared out after xml each record is posted.


I need an approach to call the XML Data for about 100 records:
either I need to call the data on the second Excel tab that has a XSD applied to it (note, the XSD excel produces is slightly off) or
I need to call a text file that has all the XML data or another way?

I think that's it..... trying to keep this dirt simple to import some data via a web service.

Thanks in advance for any thoughts!
-sjr

Code so far that posts 1 record:

Private Sub insPat_Click()
Dim info As New clsws_FDirect
Dim addpat As String
Dim response As String

addpat = Range("B10").Text

response = info.wsm_PatientAdd(addpat)

Set responserange = Range("B11")

responserange.Value = response

End Sub


Correct XSD:
<Patient>
<FacID>TEST</FacID>
<PatID>99</PatID>
<PatLName>IMPORT</PatLName>
<PatFName>MISTER</PatFName>
<Allergy>Sulfites</Allergy>
<Floor>11</Floor>
<NsID>1F</NsID>
<Room>111</Room>
<Bed>1</Bed>
<PhOrdNotice>pudding as appropriate.</PhOrdNotice>
<PhNPI>1558399717</PhNPI>
<PatStatus>A</PatStatus>
<SSN>999-99-9999</SSN>
<MedRecNo>8772</MedRecNo>
<BirthDate>1974-05-01T00:00:00-04:00</BirthDate>
<AdmDate>2011-08-05T00:00:00-04:00</AdmDate>
<Sex>M</Sex>
<WeightLbs>200.0000</WeightLbs>
<HeightInches>0</HeightInches>
<AllowRefills>true</AllowRefills>
<HIPAAStmt>true</HIPAAStmt>
<HIPAAStmtRecd>2012-01-09T00:00:00-05:00</HIPAAStmtRecd>
<DefProfileOnly>false</DefProfileOnly>
<DoNotPrintMedRecs>false</DoNotPrintMedRecs>
<MTMFee>9.0000</MTMFee>
</Patient>

[sirsql]

FYI: I used this tool set to get as far as I did: Calling XML Web Services from Office VBA Using the Web Service References Tool.... ok cant post a link but it is found on the MSDN Site

[stanl]

Your code to POST appears to be proprietary. Having faced similar tasks, I opt to use WinHttp.WinHttpRequest.5.1 POST method in a loop, validate via the WaitForResponse method/ responseText then either move to the next post or repost upon error.