none
Communicating with WebPages/Intranet via Excel VBA

    Question

  • Hello Experts,

    1. My objective for this VBA project is to grab values from webpage (http://money.rediff.com/) and put it into excel. (Enter company name in inputbox, hit GetQuote, Grab stock value)

    2. So by adding ShDOcvw COM object I have written following code, which opens desired URL, but I am not able to move forward.

    Sub InterntAuto()
    Dim objIE As SHDocVw.InternetExplorer
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True
    objIE.Navigate ("http://money.rediff.com/")
    End Sub
    


    3. I am facing two problem:
    a)If multiple tabs are opened, then how can I connect with desired tab with my URL already being opened.
    b)If it is already opened then, how do I access its web page elements

    4. I got multiple COM object references in forum when I searched for solution. Some suggest to add below COM reference, other than SHDocVW (which I used)

    WinHTTP com object
    DHTML com object
    MSHTML com object

    so what is difference between them?

    5. In one thread:
    http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/42fa2636-0f30-4fc7-9bec-7e89261869d4

    I saw code :

    If Not oIE.Document.Forms(1).Elements("login") Is Nothing Then
                 oIE.Document.Forms(1).Elements("login").Value = "YourLoginName"
                 oIE.Document.Forms(1).Elements("password").Value = "YourPassword"
                 oIE.Document.Forms(1).Elements("password").Select
        End If
    
    

    which gave me slight hint for accessing web page element in hierarchal manner, i.e first IE > Document > Form > Element > then Element name. So does Document here implies Tab no./name/index?

    6. While browsing through Object browser could not find more details about this Document Object. (Right click and selecting Help option don't give anything)

    7. Below is image:

    Thanking you for your time and consideration : Ajay Check

    Monday, February 06, 2012 9:06 PM

Answers

All replies

  • I have tried to do this in the past and gave up using IE VBA commands.  I had a hard time figuring out how to login to a secure site.  There is a commercial product called iMacros that works well.  It has a .com interface that allows it to be easily called from VBA.  It is easy to use and they have a trial period.  If you use Firefox there is also a free addin called iMacros that allows you to control the web browser.  It may do what you want.  It can export to Excel.  You can program it via javascript.  I use the commercial tool since I use a lot of features it has.
    Monday, February 06, 2012 10:09 PM
  • HI Experts,

    1. I used WSH, since I was not able to do same task with VBA.

    2. Following is sample script I wrote to login on to Technet forum. '

    To manipulate HTML Elements

    dim fobj Set fobj = createobject("scripting.filesystemobject")

    Set a= CreateObject("wscript.shell")

    a.Run "http://www.microsoft.com/"

    wscript.Sleep (5000)

    a.SendKeys "{TAB 1}", True

    a.SendKeys "{TAB 1}", True

    a.SendKeys "{TAB 1}", True

    a.SendKeys "Technet forum", True

    a.SendKeys "{ENTER}", True

    a.SendKeys "{ENTER}", True

    wscript.Sleep (5000)

    counter = 1

    Do

    a.SendKeys "{TAB 1}", True

    counter = counter +1

    Loop until counter = 16

    a.SendKeys "{ENTER}", True

    wscript.Sleep (3000)

    counter = 1

    Do

    a.SendKeys "{TAB 1}", True

    counter = counter +1

    Loop until counter = 6

    a.SendKeys "{ENTER}", True

    wscript.Sleep (5000)

    a.SendKeys "mail_id", True

    a.SendKeys "{TAB 1}", True

    a.SendKeys "password", True

    a.SendKeys "{TAB 1}", True

    a.SendKeys "{TAB 1}", True

    a.SendKeys "{TAB 1}", True

    a.SendKeys "{ENTER}", True

    3. Now one problem arises when I copy paste this code in Excel Visual Basic Editor as a subroutine, it fails @ wscript.Sleep (5000)and when I bypass it as comment, Sendkeys instead of hitting Tab and Enter on internet explorer window, puts tabs in Visual Basic Editor with in code.

    4. Can I further modify /change it to accomplish what I want to do.

    5. Is it possible to access HTML Elements using this script. If Yes/No how do I acheive it.

    Thanks for consideration and time : AjayCheck

    • Edited by Ajay Check Friday, February 10, 2012 2:52 AM
    Friday, February 10, 2012 2:45 AM
  • Hi Ajay,

    You can check simular thread out to retrieve data from that site into Excel using VBA:

    http://www.vbaexpress.com/forum/showthread.php?t=14084

    Check also below thread in how to create a Web Query:

    http://office.microsoft.com/en-us/excel-help/get-external-data-from-a-web-page-HA010218472.aspx

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    • Proposed as answer by Shasur Sunday, February 12, 2012 1:50 AM
    • Marked as answer by Ajay Check Sunday, February 12, 2012 4:50 AM
    Saturday, February 11, 2012 2:41 PM
  • Readers, I have got following solutions for my quoted problem:

    1.Using Web Query : Simple one, and did my Job finally.

    Link:http://office.microsoft.com/en-us/excel-help/get-external-data-from-a-web-page-HA010218472.aspx

    Say thanks to danishani . 

    2.Using QueryTable: A bit tough for me

    Link: http://www.vbaexpress.com/forum/showthread.php?t=14084.

    Say thanks to danishani .

    3. For more technical reading about VBA : ( Currently I am reading for automating Internet Explorer automation)

    Blog: http://www.jpsoftwaretech.com/an-exploration-of-ie-browser-methods-part-ii/

    4. With regards to Experts | Ajay Check

    • Edited by Ajay Check Sunday, February 12, 2012 5:47 AM
    Sunday, February 12, 2012 4:49 AM