VBA code error: "out of memory"

Question

i'm currently working on a project where Excel automatically fetches financial data of publicly traded companies. Sometimes I get the error:"out of memory". Is there a way to fix this? I'm using 64-bit Excel.

Code:

Sub Get_IS1()
Dim x As Integer
x = 0  

execute:
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


Dim ws As Worksheet
Set ws = Sheets("Summary")
ws.Activate

Dim qurl, symbol As String

ticker = ws.Range("C9").Value
Exchange = ws.Range("C8").Value

'Delete Prior Connections
For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn

'Clear Prior Data
Sheets("COMP1").Activate
Sheets("COMP1").Cells.Clear

'URL
qurl = "http://financials.morningstar.com/ajax/ReportProcess4CSV.html?&t=" & Exchange & ":" & ticker & "&region=usa&culture=en-US&cur=&reportType=is&period=12&dataType=A&order=asc&columnYear=5&curYearPart=1st5year&rounding=3&view=raw&r=618279&denominatorView=raw&number=3"

'Get Data Via Text File
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & qurl & "" _
    , Destination:=Sheets("COMP1").Range("B1"))
    .Name = _
    "Table 1"
    .FieldNames = True
    .PreserveFormatting = False
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 65001
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    On Error GoTo ends
    .Refresh BackgroundQuery:=False
  End With

Set ticker = Nothing
Set Exchange = Nothing
Set qurl = Nothing
Set ws = Nothing

Get_BS1

Exit Sub

'Error Handle for Invalid Entry
ends:
x = x + 1

If x = 5 Then
MsgBox ("No response was recived from Morningstar. Either an invalid ticker was entered or no prior records exist for the chosen symbol.")
ws.Activate
ElseIf x < 5 Then
GoTo execute
End If

End Sub

This piece of code fetches the Income Statement, Get_BS1 is called to get the Balance Sheet and after that the Cash Flow Statement

@MatteoNNZ The debugger highlights: ".Refresh BackgroundQuery:=False" — Nerd Zilla
– Nerd Zilla, Commented Jul 12, 2018 at 23:47

HackSlash · Accepted Answer · 2018-10-09 18:58:06Z

0

This happens to me when I am attempting to refresh against an empty file. How peculiar that the thing that should use the least memory appears to use the most...

In your case the text is coming from a webpage. You would have to check the result up front before you run your query.

Let's check one thing real quick. Change your connection string to a URL instead of TEXT:

'Get Data Via Text File
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl & "" _

answered Oct 9, 2018 at 18:58

HackSlash

6,0282 gold badges28 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA code error: "out of memory"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related