Hi there,
There is a problem in my Macro code. I really appreciate if you could advise me in this regard. I am trying to collect some information from MSN Money for some companies listed in stock market. I have some symbols (companies' name in Sheet1) and this macro takes these symbols one by one and through Web Query it imports one table of information for each symbol. When I run this code for the first time on a computer, it works. But when I try to run it more times, I will get "run time error 1004" for this line:
Refresh BackgroundQuery:=False
Could you help me. Thanks in advance
This is my code: (file is attached)
Sub Macro1()
'
' Macro1 Macro
'
size_table = 30
'
stock_symbol = "ibm"
For i = 0 To 200
stock_symbol = Worksheets("Sheet1").Cells(2 + i, 2)
bb = "A" + CStr(i * size_table + 1) + ":A" + CStr((i + 1) * size_table + 1)
Worksheets("Sheet4").Range(bb) = stock_symbol
With Worksheets("Sheet4").QueryTables.Add(Connection:= _
"URL;http://moneycentral.msn.com/ownership?Holding=Institutional+Ownership&Symbol=" + stock_symbol _
, Destination:=Worksheets("Sheet4").Range("$A$1").Offset(i * size_table, 1))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Worksheets("Sheet4").QueryTables.Item(1).Delete
Next i
' Set a = ActiveSheet.QueryTable
End Sub
Bookmarks