Macro to Close Files - getting past message

[Jack]

i have written a macro for Excel 2003 to close a group of files. I'm not
trying to save any data just close the files. The files are fairly large and
i get message

Excel Cannot complete this taks with available resources. I press OK and
the files close. I would like to have the macro handle this for me. The
message only comes up on two of the files.

sub
Windows("abc.xls").Activate
ActiveWindow.Close
Windows("dfg.xls").Activate
ActiveWindow.Close
Windows("ttt.xls").Activate
ActiveWindow.Close
close sub

 

[Dave Peterson]

Just something you can test:

application.cutcopymode = false
workbooks("abc.xls").close savechanges:=false
workbooks("dfg.xls").close savechanges:=false
workbooks("ttt.xls").close savechanges:=false

It's just a guess. But I'd just use the workbooks collection if I'm closing the
file. And maybe (a big maybe), the problem is that there's too much junk in the
clipboard.

But maybe not, too...

 

[Jack]

No still get the same message. The end of the message says "Choose less
Data or close other applications". Pressing ok closes the file no problem.
I also do not get the message when i open the files using Excel 2007.
Unfortunetly the client only has Excel 2003

 

[Dave Peterson]

You did include the .cutcopymode line, right?

My next guess (still a guess!) is that you have workbooks that have links to
these closing files. And those links point at giant ranges (entire columns???)
in those closing workbooks.

If that's the case, then maybe you could change the formulas to point at a
smaller range (just the used range plus a little bit for safety's sake).

Or maybe you can change the order of the closing of the files. Close the one
that has the links (ttt.xls???) first.

But these are just guesses.

 

[Jack]

yes i included the .cutcopymode line.

you are correct about the links. The files are very large - i have
vlookups going to these files.
1 file is 51652 rows by 14 columns
1 file is 3260 x 4 columns
these are the files that i get the message on.
In 2003 after i say ok they close quickly - in 2007 no message but the close
is very slow.

any suggestions on how to do the lookup differently -

i really appreciate your help

 

[Dave Peterson]

Are you using =vlookup() and does your lookup range have lots of columns?

Maybe using =index(match()) would be less taxing on excel.

When I was retrieving lots of data from the same table, I would use a dedicated
formula to find the match and then use that value to retrieve the values:

=match(a2,sheet2!x:x,0)
(in B2 (say))

Then
=if(iserror(b2),"no match",index(sheet2!a:a,b2))

I thought that the excel recalculated faster--but I don't know if that'll help
you.

===========

If you're closing all the files, you could close the file with the links first.

yes i included the .cutcopymode line.

you are correct about the links. The files are very large - i have
vlookups going to these files.
1 file is 51652 rows by 14 columns
1 file is 3260 x 4 columns
these are the files that i get the message on.
In 2003 after i say ok they close quickly - in 2007 no message but the close
is very slow.

any suggestions on how to do the lookup differently -

i really appreciate your help