Error in Macro Excel

Error in Macro Excel: (Refresh BackgroundQuery:=False)

Page 1 of 1

1 Replies - 1791 Views - Last Post: 05 December 2008 - 04:05 PM Rate Topic: -----

#1 abehnamian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 26-October 08

Error in Macro Excel

Post icon  Posted 26 October 2008 - 10:19 PM

Hi there,
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:

Refresh BackgroundQuery:=False

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




Is This A Good Question/Topic? 0
  • +

Replies To: Error in Macro Excel

#2 dcfvaxman  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 05-December 08

Re: Error in Macro Excel

Posted 05 December 2008 - 04:05 PM

I also had the same problem. It turns out that you can only have so many temporary files of a certain type before you get this error. Look in the Temporary Internet file director for hp?s=XXXX where XXXX is the stock symbol. If you delete all of these and then restart your macro it will again run until you get about 15 more of these files built up. It is a pain to keep going in and deleting them but at least it works. I am going to create a macro that will go in and automatically delete these files whenever I get the error which should make it less painful. I wish there was a way you could increase the limit.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1