LinkBack URL
About LinkBacks
Hello,
I'd like to be able to copy data from a list of websites into Excel. It would take too long to navigate to do an Excel web query for each site, so is it possible to write a macro that could do that?
Thank you!
ML
Hello,
I'd like to be able to copy data from a list of websites into Excel. It would take too long to navigate to do an Excel web query for each site, so is it possible to write a macro that could do that?
Thank you!
ML
Hello mlexcelhelpforum,
That's not much information to work with. What do you want to copy from the websites: the whole page, tables, pictures, etc?
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 Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hello Leith,
I managed to figure out how to do it by recording a web query with the macro recorder and touching up the code. Because the info on the website wasn't organized into tables, I imported the entire page and programmed the macro to cut out the unnecessary cells. So now it's working, although I counted that it takes about 2 minutes just to do one web query and I have a column of many web addresses I must go through-- is that normal? Can it be sped up somehow?
As you can see, the code copies the varying pieces of string of the web address from Column A to form the complete web addresses it must query, posts the web query in Column D, cuts out the useful part from Column D, pastes it into Column R in succession, and then deletes any remaining web query stuff in Columns B to Q.
Thank you,
Please Login or Register to view this content.
ML
Hello mlexcelhelpforum,
I can speed this up[ to take only a few seconds. However, it is necessary for me to see an example of the web page you want to scrape data from. Can you post the URL you will be using?
Hi Leith,
I too would like to be able to copy specific data (namely any information listed for today's or a future date) from a group of hyperlinks to websites and copy it into a worksheet. As with the original poster, it would take too long to create a separate Excel web query for each site associated with each hyperlink, so is it possible to write a macro that would automate the process of extracting this information from each site and copying it to cells in the that same worksheet associated with the site that produced it?
Thank you in advance for any help with this!
Here is an example of a hyperlink to one of the sites:
http://killersports.com/nhl/query?sdql=A+and+game+number<%3D42+and+70>%3DWP>%3D60+and+p%3AW+and+pp%3AW+and+o%3AWP<50+and+season>%3D2010+&submit=++S+D+Q+L+%21++
Lee
Hello Lee,
After looking at the link you posted, I believe the answer would be "yes". Before I can give you a definite yes, it would help to see an example worksheet with the data on it.
The simpler the format, the faster the retrieval process will be. The more the data is formatted to match the original, the slower the process becomes.
The example link has several related pages (Showing 1 to 20 of 20 entries). Do you want the macro to pull data from these pages also?
Hi Leith,
The only data to extract is any team and opponent team shown on any row containing the date when the macro is run (or for the next active date game in the future). In the example link, I'm also looking to get that information to be copied back to the worksheet with the hyperlinks on it and next to the hyperlink(s) that produced it.
Attached is sample worksheet with a some of the hyperlinks, and to the right, the relevant information that has been active in the past: date, trend#,teams active in the past
Hello Lee,
I have looked around the site but can not find the data matching what is in the workbook. Where it did it come from?
click on the NHL tab. I changed the example on the right side of the worksheet to reflect information in the site linked to by one of the hyperlinks in that list. It applies to NHL121. Note that although games for other future dates are active in other sites from other hyperlinks, I'm only interested in active games for the next soonest game date, which is Dec 27, 2014. That's why I included the results for the hyperlink that goes with trend# NHL121 and the teams that will be playing on that date (as you see when you click the hyperlink for NHL121 and scroll down to the last row...) Note that not all hyperlinks will indicate an upcoming game (or game that will happen later in the day or in a few days). Please let me know if you need clarification.Originally Posted by Leith Ross
The new worksheet is attached.
Last edited by emceeaye1; 12-26-2014 at 02:40 AM.
Hello Lee,
Are yo using the SDQL to retrieve this information or just accessing a site URL?
SDQL code was used to produce each web page that pops up every time you click a different hyperlink in the list. I just bookmarked each results page after entering uniquely different SDQL in each one--you can see the code in the coding dialogue box at the top of each page you get linked to when you click each hyperlink. So all I need help with is a macro that accesses each webpage of each hyperlink to look for any information(games) that applies to the date when the macro is run and copy it back into the workbook next to the hyperlink that produced it or in a cell with the trend# that goes with the hyperlink that produced it.
Last edited by emceeaye1; 12-27-2014 at 12:08 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks