webquery into worksheets? 1

[Bilberry]

Hi all,

Is it possible to put the content of the site into different worksheets? The site is as follows:

http://www.mysite.com/Default.aspx?app=1&page=Network add&comid=1

I need to have the content till

http://www.mysite.com/Default.aspx?app=1&page=Network add&comid=500

How can i put the content into different worksheets whitin a workbook?

 

[SkipVought]

Hi,

Data > Import External Data > New Web Query...

get it down for ONE.

Turn on your macro recorder and EDIT the query. Return data to Excel

Trun off your recorder and post back your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bilberry]

Sub Macro4()

With Selection.QueryTable
.Connection = _
"URL;

http://www.mysite.com/Default.aspx?app=1&page=Network addn&compid=340"

.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


Skip, thanks for the quick reply...

 

[SkipVought]

Might be something like this. Should have recorded the QueryTable ADD...

    For i = 1 To 500
        With ThisWorkbook.Worksheets.Add
            With ActiveSheet.QueryTables.Add( _
                Connection:= _
        "URL;[URL unfurl="true"]http://www.mysite.com/Default.aspx?app=1&page=Network%20addn&compid="[/URL] & i, _
                Destination:=ActiveSheet.[A1])
                .Name = "WebQuery" & Format(i, "000")
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .WebSelectionType = xlEntirePage
                .WebFormatting = xlWebFormattingNone
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .Refresh BackgroundQuery:=False
            End With
        End With
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}