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.

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.anglingreport.com/online_extra/article_details.cfm?id=0002", _
        Destination:=Range("$A$1"))
        .Name = "article_details.cfm?id=0002"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "15"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=-30
    Range("A12").Select
End Sub

[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.

[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