+ Reply to Thread
Results 1 to 6 of 6

QueryTables from a Site that requires login

  1. #1
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Question QueryTables from a Site that requires login

    I'm guessing something changed here between Excel 2003 and Excel 2007.

    I have an existing VBA macro that grabs some tables from a website that requires you to be logged in.

    With Excel 2003 I achieved this by first creating an instance of Internet Explorer (InternetExplorer.Application) and using that to negotiate the login pages & check the 'Remember Me' box etc.
    Once all of that was complete my VBA code then closed the IE instance, did some work in the workbook, and then ran the QueryTable.
    When Excel ran the QueryTable it presumably used the same cache / cookies (or something else?) as IE, and I was able to grab the tables that I required.

    The same macro running under Excel 2007 still manages to create the IE instance and log in etc, however when I come to do the QueryTables Excel reports that the conenction ot the server was successful, but the data was not available, which is what I would expect IF it hadn't logged in correctly.
    I can only assume at this stage that the 'Query' from the QueryTables is appearing to the website as a different connection to the one established by the IE instance.

    What changed?
    What is the work around? (I'm presuming that there IS some way to access pages behind a login screen using 2007!)

    In both cases the PC was WinXP SP3 with IE8.

    Thanks
    Last edited by Phil_V; 04-19-2011 at 10:09 AM. Reason: Marking as solved
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

  2. #2
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: QueryTables from a Site that requires login

    I'm guessing no ideas?

    Is more info needed, or does nobody else use QueryTables from websites that require you to log in?
    Surely people must access that data in some way, so perhaps you don't use QueryTables... in which case what do you use?

    Thanks

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

    Re: QueryTables from a Site that requires login

    I would suspect that people parse the data out of the page returned in IE.
    Remember what the dormouse said
    Feed your head

  4. #4
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: QueryTables from a Site that requires login

    Hmm, time for me to learn some more about doing that then...

    At least I know where to look now

    Thanks!

  5. #5
    Registered User
    Join Date
    04-17-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: QueryTables from a Site that requires login

    I go in directly and parse data from the table. It needs to be modified with your log-in details, all I have given is producing an IE instance with a URL.

    Here is the code:

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: QueryTables from a Site that requires login

    Excellent, many thanks Rotolytics that is a great piece of code for me to familiarise myself with

    I've played around with it a bit, and it looks like I should be able to shape it to my needs. The great thing about it is it has helped me understand a bit more about how the HTML objects work.

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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