PAVv3 VBA

From CDYNE Wiki
Jump to navigation Jump to search
 1 Dim cXML
 2 cXML = "<PavRequest xmlns=""pav3.cdyne.com"">" & _
 3 	"<LicenseKey>f01d89fd-5155-5455-5585-e84ab8de8591</LicenseKey>" & _
 4 	"<FirmOrRecipient></FirmOrRecipient>" & _
 5 	"<PrimaryAddressLine>505 INDEPENDENCE PARKWAY</PrimaryAddressLine>" & _
 6 	"<SecondaryAddressLine>SUITE 300</SecondaryAddressLine>" & _
 7 	"<CityName></CityName>" & _
 8 	"<State></State>" & _
 9 	"<ZipCode>23320</ZipCode>" & _
10 	"<Urbanization></Urbanization>" & _
11 	"<ReturnCaseSensitive>true</ReturnCaseSensitive>" & _
12 	"<ReturnCensusInfo>true</ReturnCensusInfo>" & _
13 	"<ReturnCityAbbreviation>true</ReturnCityAbbreviation>" & _
14 	"<ReturnGeoLocation>true</ReturnGeoLocation>" & _
15 	"<ReturnLegislativeInfo>true</ReturnLegislativeInfo>" & _
16 	"<ReturnMailingIndustryInfo>true</ReturnMailingIndustryInfo>" & _
17 	"<ReturnResidentialIndicator>true</ReturnResidentialIndicator>" & _
18 	"<ReturnStreetAbbreviated>true</ReturnStreetAbbreviated>" & _
19         "</PavRequest>"    
20  
21 Dim oXMLHTTP
22 Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
23 Call oXMLHttp.Open("POST", "http://pav3.cdyne.com/PavService.svc/VerifyAddressAdvanced", False)
24 Call oXMLHttp.setRequestHeader("Content-Type", "text/xml")        
25 Call oXMLHttp.send(cXML)
26 
27 'Display response in a message box.
28 MsgBox oXMLHTTP.responseText  
29 
30 'Write and save response to a text file.
31 Const ForReading = 1, ForWriting = 2, ForAppending = 8
32 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
33 Dim fso, MyFile, FileName, TextLine
34 Set fso = CreateObject("Scripting.FileSystemObject")
35 FileName = "c:\Users\Desktop\pavRETURN.txt"
36 Set MyFile = fso.OpenTextFile(FileName, ForWriting, True)
37 MyFile.WriteLine oXMLHttp.responseText
38 MyFile.Close
39 Set MyFile = fso.OpenTextFile(FileName, ForReading)
40 Do While MyFile.AtEndOfStream <> True
41 TextLine = MyFile.ReadLine
42 Loop
43 MyFile.Close

Call PAV directly from MS-Access or Excel.

 1 'Address to post request
 2 surl = "http://pav3.cdyne.com/PavService.svc/VerifyAddressAdvanced"
 3 
 4 'Create an HTTP object for sending package
 5 Set ohtml = CreateObject("Microsoft.XMLHTTP")
 6 
 7 'Create XML DOM for reading response tags - Uses PUBLIC Object xmldoc
 8 Set xmldoc = CreateObject("Microsoft.XMLDOM")
 9 
10 'Move address info into package and send to Cdyne
11 vpackage = ""
12 vpackage = "<PavRequest xmlns=""pav3.cdyne.com"">" & vbCrLf
13 vpackage = vpackage & "<CityName>" & rs!City & "</CityName>" & vbCrLf
14 vpackage = vpackage & "<LicenseKey>your license key here</LicenseKey>" & vbCrLf
15 vpackage = vpackage & "<PrimaryAddressLine>" & rs!Address & "</PrimaryAddressLine>" & vbCrLf
16 vpackage = vpackage & "<ReturnCaseSensitive>false</ReturnCaseSensitive>" & vbCrLf
17 vpackage = vpackage & "<ReturnCensusInfo>true</ReturnCensusInfo>" & vbCrLf
18 vpackage = vpackage & "<ReturnCityAbbreviation>true</ReturnCityAbbreviation>" & vbCrLf
19 vpackage = vpackage & "<ReturnGeoLocation>true</ReturnGeoLocation>" & vbCrLf
20 vpackage = vpackage & "<ReturnLegislativeInfo>true</ReturnLegislativeInfo>" & vbCrLf
21 vpackage = vpackage & "<ReturnMailingIndustryInfo>true</ReturnMailingIndustryInfo>" & vbCrLf
22 vpackage = vpackage & "<ReturnResidentialIndicator>true</ReturnResidentialIndicator>" & vbCrLf
23 vpackage = vpackage & "<ReturnStreetAbbreviated>true</ReturnStreetAbbreviated>" & vbCrLf
24 vpackage = vpackage & "<SecondaryAddressLine nil=""true""/>" & vbCrLf
25 vpackage = vpackage & "<State>" & rs!State & "</State>" & vbCrLf
26 If IsNull(rs!Zip) Then
27     vpackage = vpackage & "<ZipCode nil=""true""/>" & vbCrLf
28 Else
29     vpackage = vpackage & "<ZipCode>" & rs!Zip & "</ZipCode>" & vbCrLf
30 End If
31 vpackage = vpackage & "</PavRequest>" & vbCrLf
32 
33 'Set up the POST
34 ohtml.Open "POST", surl, False
35 ohtml.SetRequestHeader "Content-type", "text/xml"
36 
37 'Send the package
38 ohtml.Send vpackage
39 
40 'Get response in raw text
41 gethtml = ohtml.responseText
42 
43 'Load the XML DOM
44 xmldoc.async = False
45 xmldoc.LoadXML (ohtml.responsexml.XML)
46 
47 'Update database
48 Select Case xmldoc.DocumentElement.SelectSingleNode("ReturnCode").Text
49     Case 100, 102, 103
50 	     'Update address
51             With rs
52                 !Address = xmldoc.DocumentElement.SelectSingleNode("PrimaryDeliveryLine").Text
53                 !City = xmldoc.DocumentElement.SelectSingleNode("CityName").Text
54                 !State = xmldoc.DocumentElement.SelectSingleNode("StateAbbreviation").Text
55                 !Zip = xmldoc.DocumentElement.SelectSingleNode("ZipCode").Text
56                 !UserLastModified = "CDYNEScrub"
57                 .Update
58             End With
59         
60        'Addresses can be longer than 36 characters (standard label width)
61        If Len(xmldoc.DocumentElement.SelectSingleNode("PrimaryDeliveryLine").Text) < 36 Then
62            cn2.Execute vSQL
63            Call AddCdyneLocationInfo(vRecordType, vscrubid)
64        Else
65             Call RecordBadAddress(vRecordType, vscrubid, 9999)
66        End If
67         
68     Case Else
69         Call RecordBadAddress(vRecordType, vscrubid, xmldoc.DocumentElement.SelectSingleNode("ReturnCode").Text)
70 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