Results 1 to 2 of 2

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    We have a search tool web site (.aspx), which will give the list of policy data in a table format (Rows & Columns) upon clicking submit button. i want to fetch the entire 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 the source code of the web site,please let me know if you want i will mail you. Can anyone give me the sample code to fetch the entire 12th table of the web site and save it in the excel worksheet. Thanks for your help.


    Please remember that, i have to include your sample in the below query. So please provide the code which fit into this.


    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 "link" ' 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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Are you still looking for help with this?

    It's been a long time since I've done any web scraping, but as long as you have access to the table we should be able to pull out the data.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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