Excel Web Query with url parameter

[N Cudmore]

Folks,

I have a sheet containing 1000+ network MAC addresses.

Now I can manually create a Web query with a single URL via Data, From Web and enter the URL for example

http://www.macvendorlookup.com/api/ANExOHE/58:6d:8f:fa:15:f0

With the mac address at the end of the URL the web site returns the Manufacture, and the address of the Manufacturer, in this example it will return
Cisco-linksys, Llc|121 Theory Drive||Irvine California 92612|United States

After some reading I tried the web query with

http://www.macvendorlookup.com/api/ANExOHE/["macaddress"]

then on clicking Import I was expecting to be prompted for the Parameter where I could place a cell value of a Mac address. I was then hoping to copy this down the sheet with the incremented value for the row and all would be fine.

However, instead Excel keeps changing the URL to

http://www.macvendorlookup.com/api/ANExOHE/[%22macaddress%22]

I'm getting the feeling that I'm going to have to resort to VBA, but anyone any insight on what's going on here?

Thanks in advance...

Neil.

[6StringJazzer]

Welcome to the Forum Neil!

I have not used this feature but after reviewing the documentation and trying a couple of examples I have concluded that this feature is only for providing a parameter to a URL that has parameters. It is not for substituting a string at any arbitrary place in a URL.

When you see a URL that has a "?" after the target file name, that is an indicator that what follows is a parameter list. This has the general form of

<URL file descriptor>?<parameter name>=<parameter value>[&<parameter name>=<parameter value>]...

For example, the URL below will bring up your post that started this thread, using the color coding above to highlight parameter name & value:

http://www.excelforum.com/excel-formulas-and-functions/952453-excel-web-query-with-url-parameter.html?p=3393113&viewfull=1#post3393113

As far as I can tell, Excel will parse the URL and look for a prompt expression in brackets where a value would be expected.

So for your format I think you are going to have to resort of VBA. Post back if you need help with that; I have done that for looking up stock prices.

[N Cudmore]

Hi Jeff

Thanks for that.. I spent sometime 'playing' with passing the query with a .iqy file in the loop, only to find the linking when copying the cell get's 'broken'....

I guess I will have to learn the VBA answer, then I have two basic approaches:-

1. Start with the first row, check for a mac address, add it to the URL, get the result, paste the result into the correct cell, then add one to the row counter and loop till I reach the bottom of the list of mac addresses. I'll have to assign this to a button or something on the sheet to run this.

2. Write this as a function/macro and then call this with parameters, with the row number (better still get the existing row number), and the mac address.

As I'm new to this any pointers on which would be the more efficient route, given the 1200+ (and growing) Mac addresses I have in the sheet?

Thanks in advance...

Neil.

[6StringJazzer]

I'm not clear on what your file looks like or your overall use case.

You have a number of MAC addresses. You somehow want to select one of them to perform a web query and someplace you want to display the results of that query.

What is your overall scenario for selecting a MAC address, and where do you want to put the query results? You talk about looping, which suggests you want to do this for all of them. But it's hard to figure out what you want to do with the results. Do you want to build 1200+ web queries?

For example, I can imagine the following scenarios:

You need to provide the user an interface so they pick one MAC address and then see the results of the query

You need to execute queries for all of the MAC addresses and create a big repository of all the query results for all the MAC addresses.

[N Cudmore]

The spread-sheet is being built from site scans of devices on the networks.

This returns the active mac addresses on the network at the time of the scan but fails to include details of what the device is. At this time I'm not permitted to do a more detailed/invasive scan of the network via Nessus - fear of overloading the network even more.

Once I can show the results of the basic scan, and see there are things on the network showing up which are not expected I can more forward.

The existing scan returns just the mac address which I can use to workout what the device is, for which I then use the web query to get the manufacture of the device. Clearly this has major flaws - spoofed/changed mac addresses, chipsets for nic makers and not the device and so on, but it's a first shot to show on 1800+ devices that there are is little/no control over what is being used and what BYOD's are sneaking in the door and connecting to the network.

In short, yes your correct in the "You need to execute queries for all of the MAC addresses and create a big repository of all the query results for all the MAC addresses" scenario.

[6StringJazzer]

I was starting to see what I could do with this. I tried your example but I get

Error: API key not found. Have you registered?

It would be useful to have some sample data to play with, but I can't even set up the web query to start with if I don't have a real site I can query. Any ideas?

[N Cudmore]

I was starting to see what I could do with this. I tried your example but I get

Error: API key not found. Have you registered?

It would be useful to have some sample data to play with, but I can't even set up the web query to start with if I don't have a real site I can query. Any ideas?

The site offers free API key, just go to http://www.macvendorlookup.com and register free....

Or email me direct and I'll send you both sample sheet with 20 or more mac addresses and my API key, for you to use....

In effect we're 'reinventing the wheel', as Cisco have a product that does all of this, but they won't spend the money... If you look at http://www.macvendorlookup.com/image...astructure.png you'll see almost exactly what the final result is I'm hoping for.

[6StringJazzer]

I registered for a key and it works fine when I paste the URL directly into a browser address bar. However, when I paste the same URL into an Excel web query, I get the same error message. I have no idea what is going on there but will get back to you as soon as I figure it out. My first thought is that maybe Excel doesn't preserve the upper/lower case of the URL characters, but that would be stupid. Keep your fingers crossed, this sounds like a tough one.

Adding: Tried to do it all in VBA using this code as a test and still got the error message. Code approach courtesy of Kyle123 in post #2 in this thread.

Please Login or Register  to view this content.