PAVv3 VBA

From CDYNE Wiki
Jump to: navigation, search
Dim cXML
cXML = "<PavRequest xmlns=""pav3.cdyne.com"">" & _
"<LicenseKey>f01d89fd-5155-5455-5585-e84ab8de8591</LicenseKey>" & _
"<FirmOrRecipient></FirmOrRecipient>" & _
"<PrimaryAddressLine>505 INDEPENDENCE PARKWAY</PrimaryAddressLine>" & _
"<SecondaryAddressLine>SUITE 300</SecondaryAddressLine>" & _
"<CityName></CityName>" & _
"<State></State>" & _
"<ZipCode>23320</ZipCode>" & _
"<Urbanization></Urbanization>" & _
"<ReturnCaseSensitive>true</ReturnCaseSensitive>" & _
"<ReturnCensusInfo>true</ReturnCensusInfo>" & _
"<ReturnCityAbbreviation>true</ReturnCityAbbreviation>" & _
"<ReturnGeoLocation>true</ReturnGeoLocation>" & _
"<ReturnLegislativeInfo>true</ReturnLegislativeInfo>" & _
"<ReturnMailingIndustryInfo>true</ReturnMailingIndustryInfo>" & _
"<ReturnResidentialIndicator>true</ReturnResidentialIndicator>" & _
"<ReturnStreetAbbreviated>true</ReturnStreetAbbreviated>" & _
"</PavRequest>"
 
Dim oXMLHTTP
Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
Call oXMLHttp.Open("POST", "http://pav3.cdyne.com/PavService.svc/VerifyAddressAdvanced", False)
Call oXMLHttp.setRequestHeader("Content-Type", "text/xml")
Call oXMLHttp.send(cXML)
 
'Display response in a message box.
MsgBox oXMLHTTP.responseText
 
'Write and save response to a text file.
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, MyFile, FileName, TextLine
Set fso = CreateObject("Scripting.FileSystemObject")
FileName = "c:\Users\Desktop\pavRETURN.txt"
Set MyFile = fso.OpenTextFile(FileName, ForWriting, True)
MyFile.WriteLine oXMLHttp.responseText
MyFile.Close
Set MyFile = fso.OpenTextFile(FileName, ForReading)
Do While MyFile.AtEndOfStream <> True
TextLine = MyFile.ReadLine
Loop
MyFile.Close

Call PAV directly from MS-Access or Excel.

'Address to post request
surl = "http://pav3.cdyne.com/PavService.svc/VerifyAddressAdvanced"
 
'Create an HTTP object for sending package
Set ohtml = CreateObject("Microsoft.XMLHTTP")
 
'Create XML DOM for reading response tags - Uses PUBLIC Object xmldoc
Set xmldoc = CreateObject("Microsoft.XMLDOM")
 
'Move address info into package and send to Cdyne
vpackage = ""
vpackage = "<PavRequest xmlns=""pav3.cdyne.com"">" & vbCrLf
vpackage = vpackage & "<CityName>" & rs!City & "</CityName>" & vbCrLf
vpackage = vpackage & "<LicenseKey>your license key here</LicenseKey>" & vbCrLf
vpackage = vpackage & "<PrimaryAddressLine>" & rs!Address & "</PrimaryAddressLine>" & vbCrLf
vpackage = vpackage & "<ReturnCaseSensitive>false</ReturnCaseSensitive>" & vbCrLf
vpackage = vpackage & "<ReturnCensusInfo>true</ReturnCensusInfo>" & vbCrLf
vpackage = vpackage & "<ReturnCityAbbreviation>true</ReturnCityAbbreviation>" & vbCrLf
vpackage = vpackage & "<ReturnGeoLocation>true</ReturnGeoLocation>" & vbCrLf
vpackage = vpackage & "<ReturnLegislativeInfo>true</ReturnLegislativeInfo>" & vbCrLf
vpackage = vpackage & "<ReturnMailingIndustryInfo>true</ReturnMailingIndustryInfo>" & vbCrLf
vpackage = vpackage & "<ReturnResidentialIndicator>true</ReturnResidentialIndicator>" & vbCrLf
vpackage = vpackage & "<ReturnStreetAbbreviated>true</ReturnStreetAbbreviated>" & vbCrLf
vpackage = vpackage & "<SecondaryAddressLine nil=""true""/>" & vbCrLf
vpackage = vpackage & "<State>" & rs!State & "</State>" & vbCrLf
If IsNull(rs!Zip) Then
vpackage = vpackage & "<ZipCode nil=""true""/>" & vbCrLf
Else
vpackage = vpackage & "<ZipCode>" & rs!Zip & "</ZipCode>" & vbCrLf
End If
vpackage = vpackage & "</PavRequest>" & vbCrLf
 
'Set up the POST
ohtml.Open "POST", surl, False
ohtml.SetRequestHeader "Content-type", "text/xml"
 
'Send the package
ohtml.Send vpackage
 
'Get response in raw text
gethtml = ohtml.responseText
 
'Load the XML DOM
xmldoc.async = False
xmldoc.LoadXML (ohtml.responsexml.XML)
 
'Update database
Select Case xmldoc.DocumentElement.SelectSingleNode("ReturnCode").Text
Case 100, 102, 103
'Update address
With rs
 !Address = xmldoc.DocumentElement.SelectSingleNode("PrimaryDeliveryLine").Text
 !City = xmldoc.DocumentElement.SelectSingleNode("CityName").Text
 !State = xmldoc.DocumentElement.SelectSingleNode("StateAbbreviation").Text
 !Zip = xmldoc.DocumentElement.SelectSingleNode("ZipCode").Text
 !UserLastModified = "CDYNEScrub"
.Update
End With
 
'Addresses can be longer than 36 characters (standard label width)
If Len(xmldoc.DocumentElement.SelectSingleNode("PrimaryDeliveryLine").Text) < 36 Then
cn2.Execute vSQL
Call AddCdyneLocationInfo(vRecordType, vscrubid)
Else
Call RecordBadAddress(vRecordType, vscrubid, 9999)
End If
 
Case Else
Call RecordBadAddress(vRecordType, vscrubid, xmldoc.DocumentElement.SelectSingleNode("ReturnCode").Text)
End Select


Code Snippet from AddCdyneLocationInfo() for updating additional CDYNE fields in separate table:


'Get recordset of columns - names must be spelled same way as xmldoc (case sensitive)
vSQL = "select column_name from information_schema.columns where table_name='cdyne' "
vSQL = vSQL & "and column_name not in ('cdyneid','recordtype')"
rsCols.Open vSQL, cnlocal, adOpenForwardOnly, adLockReadOnly

'Loop through each column to update the value
'Columns must be named identically to Cdyne field names
With rs
    .Open vSQL, cnlocal, adOpenKeyset, adLockPessimistic

    rs.AddNew
    !RecordType = vRecordType
    
    Do While Not rsCols.EOF
        Set vtag = xmldoc.getElementsByTagName(rsCols!column_name)
        rs(Trim(rsCols!column_name)) = vtag(0).Text
        rsCols.MoveNext
    Loop
        
    !DateLastUpdated = Now()
    .Update

End With

Personal tools
Namespaces

Variants
Actions
Navigation
Tools

Trial Key