I have a project with a lot of code that opens other workbooks and copies
large amounts of data from workbook to workbook.
Some aspect of the code must tie up a large amount of system resources and
keep it tied up after the code has finished executing because it often
returns a "not enough resources to complete the operation" error from
excel.
The user steps through a number of processes by clicking command buttons
(on a worksheet) sequentially. In brief, these a
- Enter a month period in a cell (like "2007-03"
- Run a sub that copies a file from a remote server to a local server and
give the file a name based on the text they entered in step 1.
- Run a sub that opens the downloaded file and copies a heap of data into
the main workbook (using copy, pastespecial, cutcopymode = false)
- Run a sub that copies 52 worksheets into a new workbook then copies every
cell on every sheet (cells.copy) in the new book and paste it all as values
(to remove the worksheet formulas). /This is the bit that causes the
resources issue/.

Is this just too much to ask a little dell workstation to do?
Do I need to release the memory used to copy the whole worksheets into a
new book?
Is there a better way to convert the worksheet formulas in 52 worksheets
into values?

I can post parts of the code if it will help.

TIA
--
Damien

Damien, what version of Excel are you using? I have run into memory
problems opening and saving llots of files in versions 97/2000. By lots, I
mean 400-500.

One cure may be to add more ram to your PC. Minimum I recommend is 1024.
With 2x that I have found that Windows performance and speed is really good.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Damien McBain" wrote in message
. ..
I have a project with a lot of code that opens other workbooks and copies
large amounts of data from workbook to workbook.
Some aspect of the code must tie up a large amount of system resources and
keep it tied up after the code has finished executing because it often
returns a "not enough resources to complete the operation" error from
excel.
The user steps through a number of processes by clicking command buttons
(on a worksheet) sequentially. In brief, these a
- Enter a month period in a cell (like "2007-03"
- Run a sub that copies a file from a remote server to a local server and
give the file a name based on the text they entered in step 1.
- Run a sub that opens the downloaded file and copies a heap of data into
the main workbook (using copy, pastespecial, cutcopymode = false)
- Run a sub that copies 52 worksheets into a new workbook then copies
every
cell on every sheet (cells.copy) in the new book and paste it all as
values
(to remove the worksheet formulas). /This is the bit that causes the
resources issue/.

Is this just too much to ask a little dell workstation to do?
Do I need to release the memory used to copy the whole worksheets into a
new book?
Is there a better way to convert the worksheet formulas in 52 worksheets
into values?

I can post parts of the code if it will help.

TIA
--
Damien

Bob Flanagan wrote:

Damien, what version of Excel are you using? I have run into memory
problems opening and saving llots of files in versions 97/2000. By lots, I
mean 400-500.

I'm using 2003 SP2.

One cure may be to add more ram to your PC. Minimum I recommend is 1024.
With 2x that I have found that Windows performance and speed is really good.

Unfortunately All the computers are "standard" on the corporate network.

I think I'll have to use another method.

Damien,
If you are copying/pasting within the same WB, then you can get memory
problems after x iterations. This is due to something to do with the Names
in the WB and according to MS only solution is to save, then close/reopen
the WB.
Don't have the MSKB link handy, but you can Google.

NickHK

"Damien McBain" wrote in message
. ..
I have a project with a lot of code that opens other workbooks and copies
large amounts of data from workbook to workbook.
Some aspect of the code must tie up a large amount of system resources and
keep it tied up after the code has finished executing because it often
returns a "not enough resources to complete the operation" error from
excel.
The user steps through a number of processes by clicking command buttons
(on a worksheet) sequentially. In brief, these a
- Enter a month period in a cell (like "2007-03"
- Run a sub that copies a file from a remote server to a local server and
give the file a name based on the text they entered in step 1.
- Run a sub that opens the downloaded file and copies a heap of data into
the main workbook (using copy, pastespecial, cutcopymode = false)
- Run a sub that copies 52 worksheets into a new workbook then copies
every
cell on every sheet (cells.copy) in the new book and paste it all as
values
(to remove the worksheet formulas). /This is the bit that causes the
resources issue/.

Is this just too much to ask a little dell workstation to do?
Do I need to release the memory used to copy the whole worksheets into a
new book?
Is there a better way to convert the worksheet formulas in 52 worksheets
into values?

I can post parts of the code if it will help.

TIA
--
Damien

NickHK wrote:

Damien,
If you are copying/pasting within the same WB, then you can get memory
problems after x iterations. This is due to something to do with the Names
in the WB and according to MS only solution is to save, then close/reopen
the WB.
Don't have the MSKB link handy, but you can Google.

Thanks Nick, I'll check it out.

I tried smaller chunks by cycling through the worksheets and copying only
the usedrange, and wb.usedrange = wb.usedrange.value but all that caused
the same error.