![bestbuy api vba search bestbuy api vba search](https://toto-school.ru/800/600/https/www.libex.ru/img/x/36/35/8bd76.jpg)
'Add result to the sheet to an offsetting columnĪ(ServerItem).Offset(0, 2) = WorksheetFunction.FilterXML(Server.ResponseText, "/Response/ResourceSets/ResourceSet/Resources/Route/TravelDistance")Ī(ServerItem).Offset(0, 3) = WorksheetFunction.FilterXML(Server.ResponseText, "/Response/ResourceSets/ResourceSet/Resources/Route/TravelDuration") / 60 While Server.readyState READYSTATE_COMPLETE 'Iterate each XML request sent to see if done
BESTBUY API VBA SEARCH WINDOWS
tRequestHeader "User-Agent", "Mozilla/4.0 (compatible MSIE 6.0 Windows NT 5.0)" Server.Open "GET", URL, True 'Last param will make request async Set Server = CreateObject("MSXML2.ServerXMLHTTP") URL = BaseURL & Cell & "&wp.1=" & Cell.Offset(0, 1) & "&key=" & APIKey & "&DistanceUnit=mi&DurationUnit=min&output=xml" 'Send all the requests up front, but don't wait for them to complete Set Servers = CreateObject("Scripting.Dictionary") Public Sub GetDistances(Addresses As Range)
![bestbuy api vba search bestbuy api vba search](https://i.ytimg.com/vi/S1jUHP_CDPo/maxresdefault.jpg)
Private Const READYSTATE_COMPLETE As Long = 4 I did a quick benchmark, this is taking just over 4 seconds for 250 requests. Travel Distance and Travel Duration will be output offset relative to the from address (2 and 3 columns offset respectively). I've structured my data as the from address being in the first column of the range, and the destination address being in the column immediately after that.
BESTBUY API VBA SEARCH CODE
I've also changed this code to a sub, as I'd need to iterate over some sort of collection. What I've done below is limited the pull to a single request, and made the code asynchronous. Also, as others have pointed out what's killing performance is the synchronous code. Any thoughts on speeding this up?ĭepending on what you want to return you can actually leverage just one API method from Bing to retrieve the data you are after, the Driving Route API. To be clear, the process works well, just extremely slowly. GetLatLong = WorksheetFunction.FilterXML(objHTTP.responseText, "//Point//Latitude") & "," & WorksheetFunction.FilterXML(objHTTP.responseText, "//Point//Longitude")
BESTBUY API VBA SEARCH ZIP
Url = firstVal & state & secondVal & city & thirdVal & zip & fourthVal & address & lastVal Public Function GetLatLong(address As String, city As String, state As String, zip As String)ĭim firstVal As String, secondVal As String, thirdVal As String, fourthVal As String, lastVal As StringįirstVal = " States of America&adminDistrict=" GetTime = Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDuration"), 0) & " minutes"
![bestbuy api vba search bestbuy api vba search](https://cdn-0.access-excel.tips/wp-content/uploads/2019/04/Excel-VBA-search-text-in-multiple-Workbooks-02.jpg)
Public Function GetTime(start As String, dest As String) GetDistance = Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDistance"), 0) & " miles" Url = firstVal & start & secondVal & dest & lastVal Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") LastVal = "&travelMode=driving&o=xml&key=&distanceUnit=mi" The code pieces in use are: Public Function GetDistance(start As String, dest As String)ĭim firstVal As String, secondVal As String, lastVal As String This is an amalgamation of multiple different processes found through Google searches. I am not a coder, but I can functionally modify found code to my purposes. The employee population is approximately 2300, and it takes almost an hour to execute. The process is working, but it is excruciatingly slow. The process follows this general flow:ġ) Convert the employee's address to Lat-Long values using the GetLatLong functionĢ) Convert the employee's work address to Lat-Long values using the GetLatLong functionģ) Calculate the distance between these two points using the GetDistance functionĤ) Calculate the drive time between these two points using the GetTime function I am writing VBA in Excel to calculate the distance between an employee's home address and work address using Bing Maps API calls.