Import external data - web query

[sstern]

I'm trying to create an "Abroad Expense Report" form, and I would like to get the exchange rate from web site.

I would like to put a date in a cell and to get the right exchange rate.

Herewith the link to get it. in this example the date is 20030505

http://www.bankisrael.gov.il/heb.shearim/currency.php?rdate=20030505&curr=01

<link rel="File-List" href="file:///C:%5CDocuments%20and%20Settings%5Ca73569%5Cmsohtml1%5C01%5Cclip_filelist.xml"><link rel="Edit-Time-Data" href="file:///C:%5CDocuments%20and%20Settings%5Ca73569%5Cmsohtml1%5C01%5Cclip_editdata.mso"><!--[if !mso]> <style> v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style> <![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->[FONT=&quot]<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/> </v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" style='width:6in; height:231.6pt'> <v:imagedata src="file:///C:\Documents%20and%20Settings\a73569\msohtml1\01\clip_image001.png" o:title=""/> </v:shape><![endif]--><!--[if !vml]--><!--[endif]-->[/FONT]Thanks Shlomo

 

[pgc01]

Hi Shlomo
Welcome to the board

I checked the page and it's a xml page. I see 2 ways of getting the value:

- you read the document as a any other HTML text and use text functions or a regexp to find the value

- you use the fact that it's a xml document and navigate through its hierarchy using xpath.

This is an example using the latter approach:

- enable the reference to your latest Microsoft XML library (in Tools->References)
- write 20030505 in the cell A1 of the active sheet

Try:

Sub xmlGetRate()
Dim xmlDoc As New MSXML2.DOMDocument30
Dim currNode As IXMLDOMNode
Dim rDate As Range
 
Set rDate = Range("A1")
 
xmlDoc.async = False
xmlDoc.Load "[URL]http://www.bankisrael.gov.il/heb.shearim/currency.php?rdate[/URL]=" & rDate & "&curr=01"
 
If (xmlDoc.parseError.ErrorCode <> 0) Then
   Dim myErr
   Set myErr = xmlDoc.parseError
   MsgBox ("You have error " & myErr.reason)
Else
   xmlDoc.setProperty "SelectionLanguage", "XPath"
   Set currNode = xmlDoc.SelectSingleNode("CURRENCIES/CURRENCY/RATE")
   MsgBox currNode.Text
End If
End Sub

 

[sstern]

Thanks a lot