Not Enough Memory

the_sleeper · Mar 12th 2003

Have any of you guys had this damn message!!!

I have come across it recently when working with a large file.

According to Micorsoft this is the cache memory of Excel being exceeded (64MB)

Is there any smart away around this?

To explain how I am trying to get around it at the moment:

I have one file that links to multiple file across the network, each file contains about 5000 entries. I have set up a link in the first range of cells and then written a macro that autofills the data down 5000 rows, however I recieve the 'Not enought Memory' message. So I tried to break up the autofill macros to copy down 250 rows at a time and still I recieve the message.

Is there any other way to achieve what I want (to have the file link to everything)?

Any assistance would be greatly appreciated

DW

Egad · Mar 12th 2003

You have over 5000 rows of links per file to multiple files?
I'd recommend discarding the "live link" approach and use copy/paste via a macro button to get the data into your file.

Dave Hawley · Mar 12th 2003

Hi the_sleeper

To expand somewhat on Egad's reply. What I have done in the past (works very well) is have a Workbook_Open macro insert your external links in a small loop that also converts them to values only.

The code would like something like:

Code

Private Sub Workbook_Open()
Dim i As Integer
Dim strCol As String
     
    For i = 1 To 5
    strCol = Choose(i, "A", "B", "C", "D", "E")
        With Sheet1
            .Range(strCol & "2", .Range(strCol & "65536").End(xlUp)).FormulaR1C1 = _
            "=SUM('C:\Ozgrid Likom\Testings\[Book3.xls]Data table'!R1C1:R1000C1)"
            .Range(strCol & "2", .Range(strCol & "65536")).End(xlUp).Copy
            .Range(strCol & "2").PasteSpecial xlPasteValues
        End With
    Next i
End Sub

Display More

the_sleeper · Mar 12th 2003

Thanks guys it look like I'll be working hard today to change my existing code to a much simpler arrangement.

Dave-Thanks for the hint on incorporating the choose function in a loop, I'd never thought of that before, very logical and smart

Thanks again

DW

Rennie · Apr 2nd 2003

Dave I have setup a location called - C:\Ozgrid Likom\Testings\Book3.xls in my computer but am unable to get the desired result.

Could you kindly send me the file that you are linking from and linking to please.

Appreciate your help.

Dave Hawley · Apr 2nd 2003

Hi Rennie

I just noticed there was a space in the Range(strCol & " 2", ...... this should be

Range(strCol & "2", ......

Also, you may have to use

Code

Private Sub Workbook_Open()
Dim i As Integer
Dim strCol As String
     
    For i = 1 To 5
    strCol = Choose(i, "A", "B", "C", "D", "E")
        With Sheet1
            .Range(strCol & "2", .Range(strCol & "65536").End(xlUp)).FormulaR1C1 = _
            "=SUM('C:\Ozgrid Likom\Testings\[Book3.xls]Data table'!R1C1:R1000C1)"
            .Range(strCol & "2", .Range(strCol & "65536")).End(xlUp).Copy
            .Range(strCol & "2").PasteSpecial xlPasteValues
        End With
    Next i
End Sub

Display More

mkh · Apr 27th 2003

I have similar problem- 10 worksheets, 3 of which are huge and now I can't get it to open.ANy way to seperate into oindividual sheets without opening it in excel. I can view it but it wont open as read only or for editing.
This data is for a non profit organization and I have put tons of time into it. Any help willbe ratefully appreciated.
thank you

mkh · Apr 27th 2003

sorry-windows 98 & excel 97

Not Enough Memory

Participate now!

Share