Getting Web Data in its proper format

  • Hi,


    I am working on getting SEC Edgar filings onto Excel using its Connection wizard (ie I use the 'From Web' option in 'Data'). The issue I am facing is the text format is a complete mess and Excel gets a whole bunch of lines into a single cell.


    The website data I am trying to get is:
    http://www.sec.gov/Archives/ed…/0000950123-06-009002.txt


    However when one copies the whole text from the browser and pastes it in excel, the formatting is much better (ie each line in a seperate cell in a single column).


    I tried a whole list of options (ie No formatting to Full HTML Formatting) but somehow using the Web Query option the data just does not seem to be getting formatted properly.


    Could anyone please help me with this?


    Thanks!

  • Re: Getting Web Data in its proper format


    Norie means that rather than using Internet Explorer, you can use the XMLHTTP object to query the page directly.


    Maybe something like this:

  • Re: Getting Web Data in its proper format


    What's wrong with:


    Code
    Sub snb()
        Workbooks.OpenText "[URL]http://www.sec.gov/Archives/edgar/data/1368111/000095012306009002/0000950123-06-009002.txt[/URL]"
    End Sub
  • Re: Getting Web Data in its proper format


    snb, that would have been my suggestion, but the op wanted one cell per line - which is what you get when you copy and paste from a browser

  • Re: Getting Web Data in its proper format


    Kyle123


    Which will be accomplished using:


    Code
    Sub snb_002()
        Workbooks.OpenText "[URL]http://www.sec.gov/Archives/edgar/data/1368111/000095012306009002/0000950123-06-009002.txt[/URL]", , , , , , False, False, False, False
    End Sub
  • Re: Getting Web Data in its proper format


    Please do not post questions in threads started by other members. Start your own thread, give it an accurate and concise title that summarises your problem and explain your issue fully.


    If you think this, or any other thread, can help clarify your issue you can include a link by copying its URL from the address bar of your browser and pasting into your message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!