Excel 2010 VBA Creating Weird Named Ranges After Pulling .CSV Data from Web

[smcmahon83]

Hi everyone,

I have the following VBA code, that is pulling .csv data from a website into a table. For some reason, everytime I run it, the macro will create a weird named range called "External Data_xxx". The xxx represents a number series. So for example, if I run the macro once, a named range of "External Data_1000" will be created. If I run it again, "External Data_1001" will be created and so on and so on.

Can anyone tell me if there is something I can add to the code below to to prevent these named ranges from being created? Or if that is not possible, perhaps you can provide an addition to the code below to simply delete them once they are created?

Any help would be greatly appreciated as these named ranges are really slowing my macro down once they accumulate within the workbook (I have code running 1000 iteration loop so it gets bulky quick).

Thanks.



Sub GetData()
Dim ParameterSheet As Worksheet
Dim DataSheet As Worksheet
Dim ticker As String
Dim exchange As String
Dim interval As Integer
Dim numPastTradingDays As Integer
Dim qurl As String


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


Set ParameterSheet = Sheets("Parameters")
Set DataSheet = Sheets("Data")


DataSheet.Cells.Clear
ticker = ParameterSheet.Range("ticker").Value
exchange = ParameterSheet.Range("exchange").Value
interval = ParameterSheet.Range("interval").Value
numPastTradingDays = ParameterSheet.Range("numTradingDays").Value


qurl = "http://www.google.com/finance/getprices?" & _
"q=" & ticker & _
"&i=" & interval & _
"&p=" & numPastTradingDays & "d" & _
"&f=d,o,h,l,c,v"


QueryQuote:
With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With


DataSheet.Range("a1").CurrentRegion.TextToColumns Destination:=DataSheet.Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False


DataSheet.Columns("A:G").ColumnWidth = 12


'===Convert Google timestamp to Excel timestamp (only for Windows)
Dim timeStamp As Double
Dim timeStampRaw As String
Dim timeZoneOffsetRaw As String
Dim timeZoneOffset As Variant
Dim numRows As Integer
Dim i As Integer
numRows = DataSheet.UsedRange.Rows.Count - 1


timeZoneOffsetRaw = DataSheet.Range("a7")
timeZoneOffset = (Mid(timeZoneOffsetRaw, InStr(timeZoneOffsetRaw, "=") + 1, 10))


For i = 8 To numRows


If Not IsNumeric(DataSheet.Range("a" & i)) Then


timeStampRaw = DataSheet.Range("a" & i)
timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) - 1))
timeStamp = (timeStamp + timeZoneOffset * 60)
DataSheet.Range("g" & i) = timeStamp / 86400 + 25569


Else


DataSheet.Range("g" & i).FormulaR1C1 = "=(RC[-6]*" & interval & "+" & timeStamp & ")/86400+25569"


End If


Next


DataSheet.Range("g8:g" & numRows).NumberFormat = "d mmm yyyy h:mm;@"
DataSheet.Range("G:G").Columns.AutoFit


Application.Calculation = xlCalculationAutomatic
End Sub