+ Reply to Thread
Results 1 to 8 of 8

Loop through columns and copy website data

  1. #1
    Registered User
    Join Date
    07-23-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Loop through columns and copy website data

    Hello,

    I was hoping someone could point me in the right direction, I have a spreadsheet i am trying to get going but in order to do so i need a macro or VBA to extract a lot of information from external sources to put into the spreadsheet, basically the spreadsheet has one column which has links in it "B2 - B16643" then I have another two columns (C & D) which i want to store the data in from the link, basically when the webpage loads for the link in the cell Bx, it has two or three lines on it, all just text, so i would like to only copy the first two lines, the first line would go into cell Cx where x is the row number, and the second line of the website would go into cell Dx again where x is the row number, and it would loop through the rows until it got to the end. The data is different for the different links on each line though, so i don't think i could just do a direct web import otherwise i would have given that a go.

    Does anyone has a similar example where it grabs data from a website and puts the text into cells, the lines are directly at the top of the page, and they are the only writing on the page, no images or anything else.

    If anyone could help it would be greatly appreciated.


  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: Loop through columns and copy website data

    Hello travisdh,

    It you can post a link or two and show me the text you are interested in then I can help you further.
    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
    07-23-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Loop through columns and copy website data

    Hello,

    This is the link i was interested in:

    http://www.plantthis.com.au/selector...ostcodeID=3810

  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: Loop through columns and copy website data

    Hello travisdh,

    The only text that appears when I go to the web page is this...

    > plant hardiness zone: 10
    > days above 30°C: 15-30

    Is that all there is? Are all the pages similar?

    Can you provide me with a short list of links for testing?

  5. #5
    Registered User
    Join Date
    07-23-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Loop through columns and copy website data

    All the pages are more or less like that, ald I want to import the numbers or the whole line,
    If you change the number at the end to 3168 or 2001 or 3175 it will show similar info but the numbers might slight change

  6. #6
    Registered User
    Join Date
    07-23-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Loop through columns and copy website data

    Sorry about that, I got here on a mobile device so could not really give a good description, essentially in the link the postcode changes, but the text on the page more of less stays the same, just a few numbers may change, but the layout will also change somewhat, for example on some postcodes it might say long hot dry summers after the first two lines, it is mainly the first two lines that i am interested in, but if the third line where it exists could be imported it would be very helpful:

    Links are below

    http://www.plantthis.com.au/selector...ostcodeID=3810
    http://www.plantthis.com.au/selector...ostcodeID=3175
    http://www.plantthis.com.au/selector...ostcodeID=0200

    http://www.plantthis.com.au/selector...ostcodeID=4020 is an example of the extra line.

    Basically what i want to do is be able to extract that data into an excel spreadsheet, would anyone be able to give me an example macro or VBA which might be able to help?

    Thanks Again!

  7. #7
    Registered User
    Join Date
    07-23-2010
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Loop through columns and copy website data

    Do the links above help at all?
    I understand the loop bit, but i am not so sure how to extract the lines of the page.

  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: Loop through columns and copy website data

    Hello travisdh,

    I finally got it working. On the surface, this appears to be a simple task. Once you get into it, it is rather complex. The attached workbook has the links you posted. Click the button and the macro automatically retrieves the HTML source for page and extracts the relavent data.
    Attached Files Attached Files

+ 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