There is a problem in my Macro code in Excel. I really appreciate if you could advise me in this regard. I am trying to collect some information from MSN Money for many companies listed in stock markets. 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:
Sometimes it works for the first couple of times and after repeating the process 10 times it gives errors.
Could you help me how I can avoid facing this error. Thanks in advance
This is my code: (file is attached)
Sub Macro1() ' ' Macro1 Macro ' size_table = 30 ' stock_symbol = "ibm" For i = 0 To 100 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