Automate a Web Query For Multiple Pages In the Same Sheet

[CarlJ17098]

Hey,

I'm a beginner here. I've read a few posts similar to this topic, but nothing I've tried from them has worked. I'm trying to automate a web query for multiple pages. The pages are password protected, but an example of one is: http://www.anglingreport.com/online_...ls.cfm?id=0001

Each page follows in order, =0001, =0002, etc. I'm pulling information from the same table on each page, and each page is formatted identically. When I import the information the Table takes up 11 cells in one row (including skipping a line at the top and bottom). Ideally I'd like to import many of these tables into one row so I can then work with that data, and automate this to keep happening so that I don't have to manually enter in all the addresses.

Thanks in advance for your help. I apologize if this was covered somewhere else (I've searched several times).

Thanks.

[Leith Ross]

Hello CarlJ17098,

Welcome to the Forum!

While the log on page is of interest, it is not the focus of what you want to achieve. Can you post an example of this table? I would ask for the workbook, but since access to this the site is secure, that isn't an option. Even a picture of the web page with the information you want for this table would be helpful.

[CarlJ17098]

Leith Ross, thanks so much for the response! Here are two things that I think will help. The first is an attached .jpg of the bottom of the webpage. It's just a screen shot and I had to zoom out pretty far, so it doesn't look all that great, but I'm interested in the table at the bottom (which i boxed in red) that has the contact information of the people mentioned in each article.

The second is what I get when I record a macro to pull the information from this table on one of these pages (in this case "0002"). All of the information across the pages is found on web table 15.

Please Login or Register  to view this content.

[Leith Ross]

Hello CarlJ17098,

Thanks for the jpg of the web page. I have a favor to ask of you. To automate this in VBA will require less code than Web Query. To write the code, I need to see the HTML source document of one of the pages.

If you open the web page in Internet Explorer and click View > Source, it will create a Notepad document containing the HTML source code. If you would save that and post it for me, it would be a tremendous help.

[CarlJ17098]

Thanks, Leith!

Here's the source code. This is for the article URL: http://www.anglingreport.com/online_...ls.cfm?id=0002

[Leith Ross]

Hello CarlJ17098,

Perfect! Thank you for doing that. That will make my job much easier.

[Leith Ross]

Hello CarlJ17098,

Seemed like every time I started on this project, I was interrupted. I haven't had a chance to test it out, but it should work for you. You will need to change the user name and password in the code. The default worksheet is "Sheet1" and the data will be added starting with cell "A2" and going down column "A". The pages start at 0001 and go up to 0015. You can change the values to what you want. Here are the macros that have been added to the attached workbook.

Module1 Macro Code

Please Login or Register  to view this content.

[CarlJ17098]

Sorry to not get back to you for a couple days. Leith, thanks so much! You are awesome! Works perfectly. Much appreciated

[Leith Ross]

Hello CarlJ17098,

I am happy that my shot in the dark hit the mark. Thanks for posting back about. Glad I could help.

[shiva_ial]

Hi
i am newbie and need help in getting data from multiple web pages and save it in single sheet or multiple sheet.

when i run .iqy it runs for single url and it ends and also it doesn't close the excel sheet
after importing the data required.

so i requirement is to run URL starting in a1cell for about 3000 urls down to a3000and save data in individual seperate sheet and close all the sheet saving it

i am new to VBA, webquery and macro could someone help me.

[chrissmorrow]

I am attempting to gather data from a series of identical tables within a web site. I know that I can use a web query to gather data from each table one at a time. However, I would like to use a solution to web query all of these table and place the information onto a single worksheet. The pages on the website are numerically ordered Here is the link to the 1000th page:

http://www.usgbc.org/myUSGBC/Members....aspx?CID=1000


Thanks for your time,
Chris