+ Reply to Thread
Results 1 to 6 of 6

Web query and query by VBA produce different result than URL in browser

  1. #1
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Web query and query by VBA produce different result than URL in browser

    I am helping a member set up some VBA to execute a series of web queries. The URL is for a site that offers a free service to look up hardware MAC addresses. An example URL is

    http://www.macvendorlookup.com/api/AZSPMzJ/00:01:29:A4:CA:E0

    The last component is the MAC address; the next-to-last component is my API key. You need an API key to make the call but they are available for free registration.

    If I enter this into the address box of my browser I get this expected result:
    "000129000000-000129FFFFFF","Dfi Inc.","100, Huan-ho Street","","Hsi-chih City Taipei Hsien","Taiwan, Province Of China"

    However, if I use it in Excel to create a web query, or use VBA code to make a GET call, I get this result:
    Error: API key not found. Have you registered?

    I only have one theory but not sure how to test it. Does Excel send the MAC address in the HTTP header, like a browser would? I am wondering if the site requires the MAC address of the submitting machine to match the one used when I registered for the key. If Excel does not send the MAC address then the site may reject my call as above. I am not familiar with the underlying implementation of how Excel does this.

    The following is the VBA code I am using:
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Web query and query by VBA produce different result than URL in browser

    Jeff

    When I enter the URL you posted in my browser address bar I'm getting the 'API key not found' message.
    If posting code please use code tags, see here.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Web query and query by VBA produce different result than URL in browser

    That's what leads me to my theory--you are submitting the request from a different MAC address than the one used to create the key. The URL works for me.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Web query and query by VBA produce different result than URL in browser

    I'll have a look when I'm in front of a computer, it may be IP based or need certain headers setting, but there's no way a remote server can get hold of your mac address.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Web query and query by VBA produce different result than URL in browser

    FYI, your code works for me using the key they provided me.
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Web query and query by VBA produce different result than URL in browser

    Me too, it's a little short sighted if they've tied the API key to an IP address though

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Web query and query by VBA produce different result than URL in browser

    Yes, and I'm getting the same error trying the same code from a different machine, so maybe they are mapping IP addresses to the keys. No idea why they would want to, unless they are collecting some sort of statistics.

    And as much as I appreciate the support, none of this explains why I can get this to work in a browser but not from Excel (on the same machine). How does Excel send an HTTP request that looks any different to a server than what my browser sends?

    Edit: Rory, if the code works for you then maybe I just need to do some more homework....that's a head-thumper stumper romperstomper....
    Last edited by 6StringJazzer; 09-09-2013 at 03:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Web Query browser must have javascript enabled
    By treetops in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-02-2014, 01:46 AM
  2. [SOLVED] Pivot table query: How to produce cumulative data?
    By The_Snook in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-31-2012, 08:09 AM
  3. Excel Web Browser Query (workbook included)
    By spursrule68 in forum Excel General
    Replies: 1
    Last Post: 07-03-2012, 05:58 PM
  4. how do web query excel 2003 from site in browser?
    By Sayville Library in forum Excel General
    Replies: 0
    Last Post: 05-03-2006, 03:40 PM
  5. Replies: 0
    Last Post: 03-15-2006, 09:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1