Run-time error '7': Out of memory --- HELP---

J

Jazzer

Board Regular
Joined
Jun 14, 2011
Messages
71
Hi Guys,

I use the following code to import text files into my spreadsheet it has been working fine until today - the difference being that one of my text files is empty (i still need them saved down on a daily basis because it may contain data again on any given day) this has now caused a Run-time error '7': Out of memory message to appear

The issue seems to be with the .Refresh BackgroundQuery:=False part of my code on the empty file

full code as follows

HTML: 
Update_Nomad_Data Macro
'
'
    Rows("7:7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.DELETE Shift:=xlUp
    Rows("7:7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.DELETE Shift:=xlUp
    Range("A7").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;\\gold\shared\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GILT_OPEN_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        
            
        .Name = "NOMAD_GILT_OPEN_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
 
NextBlankLine
    
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GILT_CLOSE_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        .Name = "NOMAD_GILT_CLOSE_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    
  NextBlankLine
    
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GERMAN_OPEN_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        .Name = "NOMAD_GERMAN_OPEN_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    
     NextBlankLine
    
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_RAG_OPEN_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        .Name = "NOMAD_RAG_OPEN_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
  
    End With
    
         NextBlankLine
    
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_RAG_CLOSE_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        .Name = "NOMAD_RAG_CLOSE_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
   
    End With
    
             NextBlankLine
    
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_DUTCH_OPEN_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        .Name = "NOMAD_DUTCH_OPEN_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
                 NextBlankLine
    
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_DUTCH_CLOSE_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        .Name = "NOMAD_DUTCH_CLOSE_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
  NextBlankLine
    
    
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GERMAN_CLOSE_TODAY.txt" _
        , Destination:=Range(ActiveCell.Address))
        .Name = "NOMAD_GERMAN_CLOSE_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:Y").Select
    Columns("A:Y").EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.DELETE Shift:=xlToLeft
    Columns("K:M").Select
    Selection.DELETE Shift:=xlToLeft
    Columns("L:L").Select
    Selection.DELETE Shift:=xlToLeft
    Columns("O:S").Select
    Selection.DELETE Shift:=xlToLeft
    Range("I7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I8").Select
    ActiveCell.FormulaR1C1 = _
        "=CHOOSE(MATCH(RC[-6],{""Q"",""R"",""U"",""T""},0),RC[-1],-RC[-1],-RC[-1],RC[-1])"
    Range("I8").Select
    Selection.AutoFill Destination:=Range("I8:I500")
 
    Range("I8:I500").Select

' Delete part
    Dim r As Long
Dim lrow As Long
Application.ScreenUpdating = False
lrow = Range("L" & Rows.Count).End(xlUp).Row
For r = lrow To 8 Step -1
If Cells(r, 12).Value <> 710 And Cells(r, 15).Value <> 1788 Or Cells(r, 10).Value = 3 Or Cells(r, 11).Value = "GB00B1347K44" Then
    Rows(r).DELETE
End If
Next r
Application.ScreenUpdating = True
' Sort it
Range("A8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("NOMADDATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NOMADDATA").Sort.SortFields.Add Key:=Range( _
        "K8:K500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("NOMADDATA").Sort
        .SetRange Range("A8:O500")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A8").Select
 
 
 End Sub
    
  
    

Sub NextBlankLine()
Range("A7").Select
'move to the last cell with data
Selection.End(xlDown).Select
'move to one row below it
ActiveCell.Offset(1, 0).Select
End Sub

Basically I need to insure that code works irrespective of some files being blank or not... is this possible to achieve?

Many Thanks
James
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
How many times has this macro been run? The code seems to add a new querytable definition (on the same location) on each run, which may be the cause for your memory error.
Instead of adding the QT everytime, simple refresh the existing one.
 
Upvote 0
Couldn't you use OpenText instead of query tables to import the data?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
prompting the file selection
Replies
1
Views
181
myall_blues
myall_blues
S
  • Solved
QueryTables bug. If one query fails, all subsequent queries will fail unless restart Excel
Replies
4
Views
450
Micron
M
A
  • Solved
Macro for CSV Import
Replies
9
Views
1K
Legacy 143009
L
T
  • Solved
CSV import overwriting the next column after the last one in the CSV file
Replies
7
Views
328
Joe4
Joe4
A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top