Closed Thread
Results 1 to 8 of 8

Macro to Open webpage, then save webpage as txt file.

  1. #1
    Registered User
    Join Date
    08-20-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    45

    Macro to Open webpage, then save webpage as txt file.

    Hi,

    I have the following code which opens a webpage from Excel and then waits until it finishes loading and that is how far I have got...

    Please Login or Register  to view this content.
    The next stage is that I am wanting it to save the webpage as a text file:
    (From IE window (File->Save As->......Save As Type-> Text File *.txt) with a file name which is located in say cell A1 in the workbook.

    I would just import it into Excel and do it that way however there is quite a lot of data.

    Is this possible?

    I have attached an example txt file of what I am looking for if it was done for google.com.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: Macro to Open webpage, then save webpage as txt file.

    See if http://www.excelforum.com/excel-prog...and-paste.html helps. It's similar to your requirement, but copies the web page to the clipboard and then pastes it to the Excel sheet. There is probably a way to write the clipboard contents to a text file instead in VBA, alternatively loop through the cells and write them to a file.

  3. #3
    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: Macro to Open webpage, then save webpage as txt file.

    Hello jasocke2,

    Here is macro that will save the web page text into a text file. Change the URL and file (both in red) to what you will be using.
    Please Login or Register  to view this content.
    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!)

  4. #4
    Registered User
    Join Date
    04-12-2012
    Location
    The Netherlands, Amsterdam
    MS-Off Ver
    Excel 2002
    Posts
    2

    Re: Macro to Open webpage, then save webpage as txt file.

    I have tried this and the weird thing is that it works depending on the website that you go to.

    For instance it works for google and also to get info from the api of a russian wiki:
    However it does not work for the api of a french wiki:You get the runtime error "Object required"

    Also I tested it yesterday for booth wikis and I think it worked then (not complete sure if I tested it on the French one). Since yesterday I downloaded a new version of the IE. Anybody an idea what the problem is?

  5. #5
    Registered User
    Join Date
    04-12-2012
    Location
    The Netherlands, Amsterdam
    MS-Off Ver
    Excel 2002
    Posts
    2

    Re: Macro to Open webpage, then save webpage as txt file.

    The problem was IE; I downgraded IE back to 8.0 and now it works again.

  6. #6
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Macro to Open webpage, then save webpage as txt file.

    other way for get source web-page with GET method
    Please Login or Register  to view this content.
    Some day I've used IE object for get web page source but it was a bit slow - using above code I speed up getting result about x 10. If you need to get result many times in loop etc. then this should be faster way.
    Last edited by MaczaQ; 04-12-2012 at 04:28 PM. Reason: some coment :)
    Best Regards
    MaczaQ
    ---------------------------------------------------------------------------------------------------------------------------
    If you are satisfied with the solution(s) provided, please mark your thread as Solved
    If you are pleased with my answer consider to rate it. To thank someone who has helped you, click on the star icon below their name.
    - This way you will add him some reputation points ... thanks in advance.

  7. #7
    Registered User
    Join Date
    06-07-2012
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Macro to Open webpage, then save webpage as txt file.

    dear maczaq,

    i've tried yours & it works.
    however, how to deal with "characters in other languages" (say coding in "Big5" chinese) for objHTTP.responseText ?
    these characters are just incorrectly displayed as "????" in the txt file.
    do u have any idea ?

    thx,
    tony

    Quote Originally Posted by maczaq View Post
    other way for get source web-page with GET method
    Please Login or Register  to view this content.
    Some day I've used IE object for get web page source but it was a bit slow - using above code I speed up getting result about x 10. If you need to get result many times in loop etc. then this should be faster way.

  8. #8
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,157

    Re: Macro to Open webpage, then save webpage as txt file.

    TonyEXL,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

Closed 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