+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    01-01-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    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 attached the source code of the web site in this thread. 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.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Leith Ross; 01-01-2012 at 04:15 PM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Rajen87,

    Welcome to the Forum!

    I have attached a file that shows the table that I believe you are referring to. Please have a look and let me know if this the table you want to import. Do want just the text or text and links copied to the worksheet?
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    01-01-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    Thanks for looking ino my query. Yes, i need the same table which you have attached. Only Text is fine. Please provide the sample code which fits in my code.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Rajen87,

    I will copy only the text. Was the picture I posted of the correct table? Where should the text be copied to on the worksheet?

  5. #5
    Registered User
    Join Date
    01-01-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    Yes that is the correct table. It should be pasted in new worksheet like "sheet1".

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Rajen87,

    Here is the modified macro. If you have any problems, let me know.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-01-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    Hi Leith,

    Thanks a lot for your sample code. I tried that in my tool. I am getting error message as "Runtime Error '91': Object variable or With block variable not set error" in the line

    Please Login or Register  to view this content.
    I have tried to remove the error, but couldn't able to do that. Could you please help me on that?

    Once again. Thanks for your help. Really it is helpful.
    Thanks,
    Raj

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Raj,

    This problem occurs often with Internet Explorer. Excel often gets ahead of Internet Explorer in processing. I have added a 2 second delay to the macro before it begins processing the document object. Replace the old macro with the code below.
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 01-04-2012 at 04:00 AM.

  9. #9
    Registered User
    Join Date
    01-01-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    Still it is not working. Even i made 10 sec delay. I guess their is no value in TableX.Rows.length object. It is coming as null value. If i comment the line "R = TableX.Rows.length - 2", then same error is coming for the line "For R = 1 To TableX.Rows.length - 2". As per my analysis, wherever we have TableX.Rows we are getting this error.

    Please help me here. I am badly needed this for my project.

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

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

    I think you'd better use a HTTPRequest.
    BTW is the address correct ??



  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Raj,

    The macro was created and tested using the HTML file you posted. I am using Internet Explorer 8. If you are using a different version that may be the cause. The code in question is standard and runs on all browsers. However, I am unable to reproduce the error on my system. Since the last update changed nothing then something else is at work. What that may be is difficult to say since this is being run on private network that I know nothing about nor have access to.

  12. #12
    Registered User
    Join Date
    01-01-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    Hi Leith,

    I also tried with HTML saved page, it is working perfect. But the actual web page is .aspx formate. Do you know whether .aspx formate will have any differant procedure or referance to add. Do you have any other idea to get the table?

    Thanks,
    Raj

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Raj,

    I must not understand what you want to do. Active server pages simply create HTML documents that are sent to the browser. The server side scripts create the HTML document when the ASP file is executed. Provided that the information in the example HTML file is the same as that generated by your ASP file, the code should work.

    Do you have the necessary network permissions to run the macro and this file on your network?
    Does the table named "GridView1" exist in your ASP file code?

    If you are not certain about the last question then open the ASP file using Notepad. Use CTRL+F to search for "GridView1" in the file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1