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.
Jack wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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 wrote:
> > > > >
> > > > > 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
> > > >
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
|
Similar Threads
