Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro?? to access Webpage for data extract (2003 )

    Greetings,

    I have a question regarding the ability to run a macro to access data from a webpage.

    Firstly is it possible to do so? Secondly, if so, then would it be overly difficult? this will be for a intranet site within our org.

    Thanks,
    Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro?? to access Webpage for data extract (2003 )

    If you want to get data from a table on a web page, you can use Data | Import Data | New Web Query to import the table into a worksheet. You can then use code to refresh the data (using the Refresh method of the QueryTable object), and read the contents of the table. See <post:=667,511>post 667,511</post:>, or do a search for querytable in this forum.

    If you want to get arbitrary data from a web page, you can use the InternetExplorer object. This is considerably more complicated, since you have to know how the web page is structured. You'll find some (confusing) threads if you search for internetexplorer in this forum.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro?? to access Webpage for data extract (20

    Yes it's possible, and no it's not overly difficult, provided the format of the web page stays consistent. You'll need to add the Microsoft HTML Object Library (from the Visual Basic Editor, click Tools, References, then check the box next to Microsoft HTML Object Library).

    The following code will read the html code of a web page into a string:

    Dim objMShtml As New MSHTML.HTMLDocument, objdocument As MSHTML.HTMLDocument
    Dim URL As String, myCode As String
    URL = "http://www.google.com"
    Set objdocument = objMShtml.createDocumentFromUrl(URL, vbNullString)
    While objdocument.readyState <> "complete"
    DoEvents
    Wend
    myCode = objdocument.body.innerHTML

    Hope that helps
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro?? to access Webpage for data extract (20

    Wellll,

    Tried both methods, and I think since the data listed on our internal web page is generated via java, I may be out of luck in this particular instance. any ideas?


    Brad

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro?? to access Webpage for data extract (20

    No idea, that is not an Excel question. Perhaps you should ask in the Web Design, Coding and Scripting forum.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro?? to access Webpage for data extract (20

    The odd thing is, I can highlight, copy, and paste into a worksheet manually. One would think using the data import method it would grab the information and populate.

    Oh well, never one to give up, I will keep trying. If I find a solution, I will post it.

    thanks and Regards,
    Brad

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro?? to access Webpage for data extract (20

    The Data | Import External Data | New Database Query option will only work if the target page contains one or more HTML tables. It will not work with loose data elements, or with tables in a picture.

    The method proposed by Waggers requires that you parse the HTML code for the target page to find the data you need.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro?? to access Webpage for data extract (2003 )

    Reposted in the Web Design etc. forum as <post:=668,528>post 668,528</post:>. Please post all further replies there. This thread is locked to avoid duplication.

Posting Permissions

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