Help with looping import data from web

DJBG1

New Member
Joined
Jun 11, 2013
Messages
4
Hi All,

I am trying to import data from a web page with a standard web address with an incremental number that identifies the web page required (weather data from a web page).

However, the macro I have at the moment is not copying in the web page. I think that the issue is that the web address is not being copied from the designated cell and the clipboard is being wiped. Does anybody know how to work around that, or how to insert a loop to state the required web page?

Code:
Sub ImportData()
'
' ImportData Macro
' Retrieves the next set of data from the website.
'The Usual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
LineNumber = Sheets("Summary").Range("G2")
For i = LineNumber To 10
                Sheets("Summary").Range("G2") = LineNumber
                Sheets("Summary").Calculate  ' these 2 lines make sure a new URL is generated on the summary tab
                URLToGet = Sheets("Summary").Range("G7")

'The Macro
    Sheets("Summary").Select
    Range("G7").Select
    Selection.Copy
    Sheets("Import").Select
    Application.CutCopyMode = True
    With ActiveSheet.QueryTables.Add(Connection:= _
       "URL; " & URLToGet _
        , Destination:=Range("$A$1"))
        .Name = _
        "current?LANG=en&DATE=1199059200&CONT=ukuk&LAND=UK&KEY=UK&SORT=1&UD=0&INT=06&TYP=temperatur&ART=tabelle&RUBRIK=akt&R=310&CEL=C&SI=mph_1"
        .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
        
        
    End With
    
    Sheets("weather test").Select
    Range("C2").Select
    Selection.Copy
    Sheets("Summary").Select
    Dim FirstBlankCell As Range
    Set FirstBlankCell = Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
    FirstBlankCell.Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Import").Select
    Cells.Select
    Selection.ClearContents
    
    
    Next i
    
'The usual
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub

If you would like to see the web address:
http://www.weatheronline.co.uk/weather/maps/current?LANG=en&DATE=1072915200&CONT=ukuk&LAND=UK&KEY=UK&SORT=1&UD=0&INT=06&TYP=temperatur&ART=tabelle&RUBRIK=akt&R=310&CEL=C&SI=mph</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>


The section in colour is what changes per page, increasing by 1800 each time.

Thanks for your time.

D
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,187,068
Messages
5,961,395
Members
438,539
Latest member
muimonk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top