| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Close Files - getting past message
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to Close Files - getting past message
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to Close Files - getting past message
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to Close Files - getting past message
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to Close Files - getting past message
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to Close Files - getting past message
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why excel close all files when I just want to close one files | Excel Discussion (Misc queries) | |||
| Macro to save & close all opened files | Excel Discussion (Misc queries) | |||
| I want alert message when i close xl window if cells in are blank | Excel Discussion (Misc queries) | |||
| When shutting down my computer, message says Excel can't close. | Excel Discussion (Misc queries) | |||
| Warning message on file close | Excel Worksheet Functions | |||
Linear Mode
