Skip to main content

VBA in Excel to automate IE for crawling a web page


If you want to open a website and go through the results of a webpage using VBA 
you can achieve it by first including a reference to Microsoft HTML Object Library 
in your VBA editor.


The following snippet of code should be a good starting point of how you can achieve the same
Sub GoToWebSiteAndPlayAround()

Dim appIE As Object ' InternetExplorer.Application
Dim sURL As String

Application.ScreenUpdating = False
Set appIE = CreateObject("InternetExplorer.Application")

'URL with the search term 'Cancer' at Science
sURL = "http://www.google.com?q=vba" 'this URL to be replaced by your target web page

With appIE
    .navigate sURL
    ' uncomment the line below if you want to watch the code execute, or for debugging
    '.Visible = True
End With

' loop until the page finishes loading
Do While appIE.readyState <> 4
    DoEvents
Loop

'Get info from HTML by ID and Name
Dim outerDiv, innerSpan, requiredtext, HTMLDoc
Dim spanCollection, outerSpan
   Set HTMLDoc = appIE.document
   Set outerDiv = HTMLDoc.getElementById("outerDivClassName")
        Set spanCollection = outerDiv.getElementsByTagName("SPAN")
       
        For Each outerSpan In spanCollection
            If outerSpan.className = "outerSpanClassName" Then
                requiredtext = outerSpan.innerHTML
                Debug.Print requiredtext
            End If
        Next

Application.ScreenUpdating = True
appIE.Quit
End Sub



Comments

  1. Hi Hitesh... what if you want to determine the final URL for the page?

    ReplyDelete
  2. Hi Jon,

    In case the url is redirected you can wait for the required url /web page title to appear. Please have a look at this link for more help http://vba-corner.livejournal.com/4623.html. Let me know if this resolves your requirement

    ReplyDelete

Post a Comment

Popular posts from this blog

VBA Procedure to apply borders to a excel range of cells

The following VBA procedure can be used to apply borders to a range of cells Public Sub ApplyBorder(ToRange As Range, intColorIndex As Integer)      On Error Resume Next      With ToRange         With .Borders(xlDiagonalDown)             .LineStyle = xlNone         End With         With .Borders(xlDiagonalUp)             .LineStyle = xlNone         End With                 With .Borders(xlEdgeLeft)             .LineStyle = xlContinuous             .Weight = xlThin        ...

Handy Tips on working on Microsoft Word or Outlook

I have complied a small set of handy tips for working on Microsoft Word or Microsoft Outlook – 1. To change case (toggle between upper case, lower case and camel case),  select the text and press Shift+F3 2. While editing documents, it is not uncommon to delete words, phrases, and the like. Different people take different approaches to the task. For instance, some people just select the text and press Delete, while others may simply hold down the Delete or Backspace keys until the unwanted characters disappear. If you are in the latter group, and you spend a lot of time pressing Delete or Backspace, you may be interested in a handy shortcut provided by Word. All you need to do is hold down the Ctrl key to speed up your editing tasks. Here is a list of what you can achieve with this Ctrl+ <-- (left arrow key)        Goto Previous word Ctrl+ --> (right arrow key)      Goto Next word Ctrl+Delete     ...

Configuring Hyper-V for seamless access to VM on Windows 8

If you have Windows 8 Pro or EnterPrise and need to use Hyper-V on your machine look no further. In this post I will explain how you can start using Hyper-V up on your machine but before I get into the usage instructions I would like you to read the below link to understand how you can enable Hyper-V on your machine http://www.techrepublic.com/photos/use-windows-8-client-hyper-v-to-set-up-a-test-machine/6387407?tag=siu-container;thumbnail-view-selector Once you have enabled Hyper-V and created a Virtual Machine the next step is to be able to access this VM seamlessly. The way you can achieve this is listed below- Goto Hyper-V Console and click on Virtual Switch Manager Select the network type as Internal and click Create Virtual Switch. This network switch enables your guest Virtual Machine to communicate with the host machine. Next run ncpa.cpl in your host machine's run window. Right click the Internal network adapter and click properties. Double click on...