Excel not creating full schema - please help!

MrSolar

New Member
Joined
Jan 26, 2014
Messages
5
<rate<font color="#000000"><rate name="SEATTLE" code="1726" staterate="0.065" localrate="0.036">I have a working spreadsheet which uses a vba to query a web service and return a tax value and other information.

However, recently I found a bug that stems from excel having not created the schema fully, therefore I am not getting enough of the values I need refreshed in Excel.

Without modifying the vba, I think I can get what I need with the correct schema.

To be clear, the spreadsheet works refreshing the xml, but the bug is that in some search queries, the rate and localrate under response are not matching the staterate and localrate under rate below. So I am returning an incorrect tax rate. Please take a look and see if you can help. :)

Here is the xml output from the web service:
<rate name="SEATTLE" code="1726" staterate="0.065" localrate="0.036">
PHP:
<response loccode="1726" localrate="0.036" rate="0.101" code="0"><addressline houselow="700" househigh="700" evenodd="E" street="5TH AVE" state="WA" zip="98104" plus4="3743" period="Q32017" code="1726" rta="Y" ptba="King PTBA" cez="Duwamish"/><rate name="SEATTLE" code="1726" staterate="0.065" localrate="0.036"/></response>

Here is the url which brings up that xml return:
Code:
http://dor.wa.gov/AddressRates.aspx?output=xml&addr=700%205th%20Ave&city=Seattle&zip=98104


The part of the xml that Excel is not creating schema for is:
<rate<font color="#000000">
PHP:
<rate name="SEATTLE" code="1726" staterate="0.065" localrate="0.036"/>

Here is a picture in excel of what that looks like, I have circled the area where Excel is not loading the schema:
KZIrM

85bvF

Code:
http://imgur.com/a/85bvF

Also, in case I have done something wrong, or you can modify my VBA to get the excel file to return all refreshed values, here is the VBA I'm using to call for the tax rate:
Code:
Private Sub CommandButton1_Click()Dim Cit As Variant
Dim Zip As Variant
Dim Stree As Variant
Dim rate As Variant
Zip = Cells(10, 10)
Cit = Cells(9, 10)
Stree = Cells(8, 10)
rate = Cells(12, 42)
Dim Map As XmlMap


Set Map = ActiveWorkbook.XmlMaps(1)
Map.DataBinding.LoadSettings "http://dor.wa.gov/AddressRates.aspx?output=xml&addr= " & Stree & "   &city=" & Cit & " &zip= " & Zip & ""
Map.DataBinding.Refresh


End Sub

And here is the link to download the Excel file:
Code:
https://ufile.io/3bejw

Thanks so much for any help, it is greatly appreciated, I am stumped.

</rate</rate></rate
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I am not opposed to manually editing the xmlmap schema in the excel file itself, but turning my .xlsm into a .zip, and manually editing the schema in a text editor.

But of course, I am sort of hacking my way through it with no success.

Here is the schema Excel created which I have opened from the zipped file contents:
PHP:
<MapInfo xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" SelectionNamespaces=""><Schema ID="Schema1"><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns=""><xsd:element nillable="true" name="response"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" nillable="true" name="addressline" form="unqualified"><xsd:complexType><xsd:attribute name="houselow" form="unqualified" type="xsd:integer"></xsd:attribute><xsd:attribute name="househigh" form="unqualified" type="xsd:integer"></xsd:attribute><xsd:attribute name="evenodd" form="unqualified" type="xsd:string"></xsd:attribute><xsd:attribute name="street" form="unqualified" type="xsd:string"></xsd:attribute><xsd:attribute name="state" form="unqualified" type="xsd:string"></xsd:attribute><xsd:attribute name="zip" form="unqualified" type="xsd:integer"></xsd:attribute><xsd:attribute name="plus4" form="unqualified" type="xsd:integer"></xsd:attribute><xsd:attribute name="period" form="unqualified" type="xsd:string"></xsd:attribute><xsd:attribute name="code" form="unqualified" type="xsd:integer"></xsd:attribute><xsd:attribute name="rta" form="unqualified" type="xsd:string"></xsd:attribute><xsd:attribute name="ptba" form="unqualified" type="xsd:string"></xsd:attribute><xsd:attribute name="cez" form="unqualified" type="xsd:string"></xsd:attribute></xsd:complexType></xsd:element><xsd:element minOccurs="0" nillable="true" name="rate" form="unqualified"><xsd:complexType><xsd:attribute name="name" form="unqualified" type="xsd:string"></xsd:attribute><xsd:attribute name="code" form="unqualified" type="xsd:integer"></xsd:attribute><xsd:attribute name="staterate" form="unqualified" type="xsd:double"></xsd:attribute><xsd:attribute name="localrate" form="unqualified" type="xsd:double"></xsd:attribute></xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="loccode" form="unqualified" type="xsd:integer"></xsd:attribute><xsd:attribute name="localrate" form="unqualified" type="xsd:double"></xsd:attribute><xsd:attribute name="rate" form="unqualified" type="xsd:double"></xsd:attribute><xsd:attribute name="code" form="unqualified" type="xsd:integer"></xsd:attribute></xsd:complexType></xsd:element></xsd:schema></Schema><Map ID="1" Name="response_Map" RootElement="response" SchemaID="Schema1" ShowImportExportValidationErrors="false" AutoFit="false" Append="false" PreserveSortAFLayout="true" PreserveFormat="true"><DataBinding FileBinding="true" ConnectionID="2" DataBindingLoadMode="1"/></Map></MapInfo>
</div>
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top