I realize that this problem has been described many times on this board, but I have not been able to find a solution that works for me. Basically, I'm trying to download web data from google. It works for a random number of times (usually 20+) then gives me the 1004 error. If I exit excel and empty the temporary files folder, I get it to run for another 20+ times.
I tried to delete the temp files after each run with:
Kill Environ("UserProfile") & "\Local Settings\Temporary Internet Files\*.*"
but that doesn't seem to do the trick.
My complete code is as follows:
For i = 2 To 200
' delete all querytables for current worksheet
While ThisWorkbook.Worksheets("Sheet1").QueryTables.Count > 0
ThisWorkbook.Worksheets("Sheet1").QueryTables(ThisWorkbook.Worksheets("FundBeta").QueryTables.Count).Delete
Wend
ThisWorkbook.Worksheets("Sheet1").Activate
With ThisWorkbook.Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://finance.google.com/finance?q=" & <symbol gets added here from table indexed by loop variable i>, Destination:=Range("A1"))
.Name = "finQuery"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i
Thanks for any help.
I tried to delete the temp files after each run with:
Kill Environ("UserProfile") & "\Local Settings\Temporary Internet Files\*.*"
but that doesn't seem to do the trick.
My complete code is as follows:
For i = 2 To 200
' delete all querytables for current worksheet
While ThisWorkbook.Worksheets("Sheet1").QueryTables.Count > 0
ThisWorkbook.Worksheets("Sheet1").QueryTables(ThisWorkbook.Worksheets("FundBeta").QueryTables.Count).Delete
Wend
ThisWorkbook.Worksheets("Sheet1").Activate
With ThisWorkbook.Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://finance.google.com/finance?q=" & <symbol gets added here from table indexed by loop variable i>, Destination:=Range("A1"))
.Name = "finQuery"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i
Thanks for any help.