How to fetch the table from ASP web site and save in Excel

rajen87 · Nov 28th 2011

their is search tool web site (.aspx), which will give the list of data in a table format (Rows & Columns) upon clicking submit button. i want to fetch the second column data from the table and have to save that in a excel. I have already wrote the code for opening the web site and clicking submit botton. I have attached the source code of the web site in this thread. Can anyone give me the sample code to fetch the second colume in the 12th table of the web site.

I have to add the sample code which you are going to provide in below code,

Code

Sub Button1_Click()




    Dim obj As Object
    Dim Scrn As String
    Dim objIE As SHDocVw.InternetExplorer 'microsoft internet controls (shdocvw.dll)
    Dim htmlDoc As MSHTML.HTMLDocument 'Microsoft HTML Object Library
    Dim htmlInput As MSHTML.HTMLInputElement
    Dim htmlColl As MSHTML.IHTMLElementCollection
         
    Set objIE = New SHDocVw.InternetExplorer
    If (objIE Is Nothing) Then
        MsgBox "Could not create the Internet Explorer object.  Stopping macro playback."
        Stop
    End If
 
    With objIE
        .Navigate "http://devts296:81/spiqatools/ui/PolicyFinder.aspx" ' Main page
        .Visible = 1
        Do While .readyState <> 4: DoEvents: Loop
            Application.Wait (Now + TimeValue("0:00:02"))
            Set htmlDoc = .document
            
            objIE.document.getElementById("LOBList").Value = "A"
            objIE.document.getElementById("DataFilterEnv").Value = "L"
            objIE.document.getElementById("ctl04_rdbAutoTransitionSignal_1").Checked = True
            objIE.document.getElementById("ctl04_AutoTransitionSignal").Value = "L"
            objIE.document.getElementById("btnsubmit").Click
                While objIE.Busy
                    DoEvents
                Wend
                While objIE.readyState <> 4
                    DoEvents
                Wend
    End With                    
End Sub

Display More

Eggcel · Nov 28th 2011

Re: How to fetch the table from ASP web site and save in Excel

Try something like this.

Code

Option Explicit
Sub Button1_Click()
     
    Dim IeTbl As Object
    Dim obj As Object
    Dim Scrn As String
    Dim objIE As SHDocVw.InternetExplorer 'microsoft internet controls (shdocvw.dll)
    Dim htmlDoc As MSHTML.HTMLDocument 'Microsoft HTML Object Library
    Dim htmlInput As MSHTML.HTMLInputElement
    Dim htmlColl As MSHTML.IHTMLElementCollection
    Dim i As Integer, j As Integer, n As Integer
    
    Set objIE = New SHDocVw.InternetExplorer
    If (objIE Is Nothing) Then
        MsgBox "Could not create the Internet Explorer object.  Stopping macro playback."
        Stop
    End If
     
    With objIE
        .Navigate "[URL]http://devts296:81/spiqatools/ui/PolicyFinder.aspx[/URL]" ' Main page
        .Visible = 1
        Do While .ReadyState <> 4: DoEvents: Loop
            Application.Wait (Now + TimeValue("0:00:02"))
            Set htmlDoc = .Document
             
            objIE.Document.getElementById("LOBList").Value = "A"
            objIE.Document.getElementById("DataFilterEnv").Value = "L"
            objIE.Document.getElementById("ctl04_rdbAutoTransitionSignal_1").Checked = True
            objIE.Document.getElementById("ctl04_AutoTransitionSignal").Value = "L"
            objIE.Document.getElementById("btnsubmit").Click
            While objIE.Busy
                DoEvents
            Wend
            While objIE.ReadyState <> 4
                DoEvents
            Wend
        End With
    Set IeTbl = objIE.Document.getelementsbytagname("Table").Item(12)
     For n = 0 To IeTbl.Rows.Length - 1
            For j = 0 To IeTbl.Rows(n).Cells.Length - 1
              If j = 2 Then
                i = i + 1
                Sheet1.Cells(i, 1).Value = IeTbl.Rows(n).Cells(j).innertext 'change sheet and cell destination per your requirements
              End If
            Next j
        Next n
End Sub

Display More

This is untested but should do what you want based on your description.

rajen87 · Nov 30th 2011

Re: How to fetch the table from ASP web site and save in Excel

Hi,

I tried the code you provided above, but i am getting the "Runtime error 91: Object variable or With block variable not set[FONT=arial]" for the below line.

Can you help me with this.

rajen87 · Nov 30th 2011

Re: How to fetch the table from ASP web site and save in Excel

In the below line

"For n = 0 To IeTbl.Rows.Length - 1"

Eggcel · Nov 30th 2011

Re: How to fetch the table from ASP web site and save in Excel

It may be that it is not really the 12th table you are after. Try changing the item number in

Code

Set IeTbl = objIE.Document.getelementsbytagname("Table").Item(12)

to lower numbers and see if that helps

Edit: I just tried the code with a random web page and got the same error until I lowered the item number. Verify the table number you are after.

rajen87 · Dec 19th 2011

Re: How to fetch the table from ASP web site and save in Excel

I tried all the number back and front. still i am getting the same error.

"Runtime error 91: Object variable or With block variable not set[FONT=arial]" for the below line

Set IeTbl = objIE.Document.getelementsbytagname("Table").Item(12)

I am badly need this for my project. Instead of copying the cells, if the whole table is copied to excel then it is fine for me.

Could you please help me how to copy the table from ASP web site to excel?

Thanks in advance.

rajen87 · Jan 1st 2012

Re: How to fetch the table from ASP web site and save in Excel

Bump

Eggcel · Jan 4th 2012

Re: How to fetch the table from ASP web site and save in Excel

I am not sure what further advice I can give you without being able to access the web page. The code that I posted is pretty standard for grabbing tables. You could loop through all of the TD tags and write those to cells and then pick out what you need possibly.

How to fetch the table from ASP web site and save in Excel

Participate now!

Share