Excel Macro: Need to download website data to spreadsheet

[Habibi14]

Hello Forum,

For a spreadsheet, which looks like this

Industry (eng) Quick Name (eng)
Shipping 9101 Nippon Yusen Kabushiki Kaisha
Shipping 9104 Mitsui O.S.K.Lines,Ltd.
Shipping 9107 Kawasaki Kisen Kaisha,Ltd.
Shipping 9110 Shinwa Kaiun Kaisha,Ltd.
Shipping 9113 INUI STEAMSHIP CO.,LTD.
Shipping 9115 MEIJI SHIPPING CO.,LTD.
Shipping 9119 IINO KAIUN KAISHA,LTD.
Shipping 9130 KYOEI TANKER CO.,LTD.
Shipping 9132 DAIICHI CHUO KISEN KAISHA

I want to download day closing prices for each Quick code (9101-...) into the column following the comp. name. So far, I have created a macro, which gives me the first, but only the first value (for 9101), but does not automatically download the remaining closing prices (up until 9132). Independently of the quick code, the macro shall basically look up each value in the 2nd column and then download the day closing prices from Google Finance.
It shall furthermore not insert new columns to the right of the spreadsheet.

My macro so far looks like this:

Sub TopixIndustryPullData()
'
' TopixIndustryPullData Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.google.com/finance/company_news?q=TYO:" & Range("C3").Value, Destination:= _
Range(("E3"), ActiveCell.Offset(1, 0)))
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With

Do
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub


I know, I have to define the Range in line 3 and adjust the loop until Column B shows no value anymore, but I don't know how. Can someone please help me? Thank you very much in advance.

PS: I am relatively new to VBA programming and am trying to practice on my own, but at some point, I do need some outside help. Yours is much appreciated. Thank you.
Habibi

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Cell As Range
    For Each Cell In Range(Range("C3"), Range("c3").End(xlDown))
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://www.google.com/finance/company_news?q=TYO:" & Cell.Value, Destination:= _
                Cell.Offset(, 2))
            .FieldNames = True
            .RowNumbers = True
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = False
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "1"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    Next Cell
End Sub

 

[Habibi14]

Andrew,

thank you so much for your help. The Dim Cell Range was what I needed. Now the macro is doing what I intended it to do. Thank you.
May I ask two more tiny issues?
1. For some reason, I get a "Run-time error 1004" in the macro, once the query reached the end of column C. Can I tell the macro to stop automatically once there is nothing in column C anymore?
2. Is there any opportunity to overwrite old information once I do a second query? Right now, the macro inserts additional columns to the left of the old results (and leaves the old results without deleting them) once I do a second query.

Otherwise the macro works perfectly. Thank you very much for your kind help.