时间戳

首页 > 解决方案 > VBA 代码错误：“内存不足”

excel - VBA 代码错误：“内存不足”

问题描述

我目前正在做一个 Excel 自动获取上市公司财务数据的项目。有时我会收到错误消息：“内存不足”。有没有办法来解决这个问题？我正在使用 64 位 Excel。

代码：

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

这段代码获取损益表，调用 Get_BS1 来获取资产负债表，然后是现金流量表

标签： excelvbafinance

解决方案

当我尝试刷新一个空文件时，就会发生这种情况。应该使用最少内存的东西似乎使用最多的东西是多么奇怪......

在您的情况下，文本来自网页。在运行查询之前，您必须预先检查结果。

让我们快速检查一件事。将连接字符串更改为 URL 而不是 TEXT：

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

推荐阅读