| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Faster closing of large spreadsheet
I use a Trend.xls to compile several other spreadsheets into. I copy and
paste some data into the master, in this case Trends.xls. When I close "Impromptu DataDump rev3 2005 to present.xls", it takes up to 15 minutes. It does not have any "upon close" code. Is there a better (faster) method to use than the sample I have included below? Thanks Steve Workbooks.Open Filename:= _ (WhereDump & "Impromptu DataDump rev3 2005 to present.xls"): ReadOnly = True Worksheets("Sheet1").Range("A2:BW40000").Select Selection.Copy Workbooks("Trends.xls").Activate Worksheets("Product_Code").Range("A2").Select ActiveSheet.Paste Application.StatusBar = "Closing Data Dump........." Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close SaveChanges:=False |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Faster closing of large spreadsheet
You don't need to select cells before copying them. The Copy Method
has a Destination argument where you can specify where you want the copied values to be pasted. Worksheets("Sheet1").Range("A2:BW40000").Copy Destination:=Workbooks ("Trends.xls").Sheets("Product_Code").Range("A2 ") Also, do you have a lot of formulas in "Impromptu DataDump rev3 2005 to present.xls"? If so, they're probably recalculating when you do the copy. --JP On Nov 26, 10:56 am, Steve wrote: I use a Trend.xls to compile several other spreadsheets into. I copy and paste some data into the master, in this case Trends.xls. When I close "Impromptu DataDump rev3 2005 to present.xls", it takes up to 15 minutes. It does not have any "upon close" code. Is there a better (faster) method to use than the sample I have included below? Thanks Steve Workbooks.Open Filename:= _ (WhereDump & "Impromptu DataDump rev3 2005 to present.xls"): ReadOnly = True Worksheets("Sheet1").Range("A2:BW40000").Select Selection.Copy Workbooks("Trends.xls").Activate Worksheets("Product_Code").Range("A2").Select ActiveSheet.Paste Application.StatusBar = "Closing Data Dump........." Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close SaveChanges:=False |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Faster closing of large spreadsheet
There are no formulas in the DataDump file that I am trying to close. It is
not the copy that is taking so long but the Close. Thanks for the Copy Destination idea though. Steve "JP" wrote: You don't need to select cells before copying them. The Copy Method has a Destination argument where you can specify where you want the copied values to be pasted. Worksheets("Sheet1").Range("A2:BW40000").Copy Destination:=Workbooks ("Trends.xls").Sheets("Product_Code").Range("A2 ") Also, do you have a lot of formulas in "Impromptu DataDump rev3 2005 to present.xls"? If so, they're probably recalculating when you do the copy. --JP On Nov 26, 10:56 am, Steve wrote: I use a Trend.xls to compile several other spreadsheets into. I copy and paste some data into the master, in this case Trends.xls. When I close "Impromptu DataDump rev3 2005 to present.xls", it takes up to 15 minutes. It does not have any "upon close" code. Is there a better (faster) method to use than the sample I have included below? Thanks Steve Workbooks.Open Filename:= _ (WhereDump & "Impromptu DataDump rev3 2005 to present.xls"): ReadOnly = True Worksheets("Sheet1").Range("A2:BW40000").Select Selection.Copy Workbooks("Trends.xls").Activate Worksheets("Product_Code").Range("A2").Select ActiveSheet.Paste Application.StatusBar = "Closing Data Dump........." Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close SaveChanges:=False |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Faster closing of large spreadsheet
I've never seen a workbook take that long to close -- especially if I'm closing
it without saving. If you open it manually and close it manually, does it work ok? Just a silly guess... Are there any events that could be running when you close that workbook? application.enableevents = false Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close _ SaveChanges:=False application.enableevents = true And even a sillier guess... Clean up your windows temp folder. Try closing excel and cleaning up your windows temp folder--it might help and it can't hurt. windows start button|Run %temp% is a quick way to get there. Steve wrote: I use a Trend.xls to compile several other spreadsheets into. I copy and paste some data into the master, in this case Trends.xls. When I close "Impromptu DataDump rev3 2005 to present.xls", it takes up to 15 minutes. It does not have any "upon close" code. Is there a better (faster) method to use than the sample I have included below? Thanks Steve Workbooks.Open Filename:= _ (WhereDump & "Impromptu DataDump rev3 2005 to present.xls"): ReadOnly = True Worksheets("Sheet1").Range("A2:BW40000").Select Selection.Copy Workbooks("Trends.xls").Activate Worksheets("Product_Code").Range("A2").Select ActiveSheet.Paste Application.StatusBar = "Closing Data Dump........." Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close SaveChanges:=False -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Faster closing of large spreadsheet
Hi Dave
Manually closing is seconds. THere are no events (No code at all just data). I moved the close statement after a block of code that opens and closes a third spreadsheet (which closes quickly) and it closes very quickly when the Close statement is in this new location. "Dave Peterson" wrote: I've never seen a workbook take that long to close -- especially if I'm closing it without saving. If you open it manually and close it manually, does it work ok? Just a silly guess... Are there any events that could be running when you close that workbook? application.enableevents = false Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close _ SaveChanges:=False application.enableevents = true And even a sillier guess... Clean up your windows temp folder. Try closing excel and cleaning up your windows temp folder--it might help and it can't hurt. windows start button|Run %temp% is a quick way to get there. Steve wrote: I use a Trend.xls to compile several other spreadsheets into. I copy and paste some data into the master, in this case Trends.xls. When I close "Impromptu DataDump rev3 2005 to present.xls", it takes up to 15 minutes. It does not have any "upon close" code. Is there a better (faster) method to use than the sample I have included below? Thanks Steve Workbooks.Open Filename:= _ (WhereDump & "Impromptu DataDump rev3 2005 to present.xls"): ReadOnly = True Worksheets("Sheet1").Range("A2:BW40000").Select Selection.Copy Workbooks("Trends.xls").Activate Worksheets("Product_Code").Range("A2").Select ActiveSheet.Paste Application.StatusBar = "Closing Data Dump........." Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close SaveChanges:=False -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Faster closing of large spreadsheet
I just noticed that your Open statement and Close statement have
different workbook names. Workbooks.Open Filename:=(WhereDump & "Impromptu DataDump rev3 2005 to present.xls") Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close --JP On Nov 26, 11:21*am, Steve wrote: There are no formulas in the DataDump file that I am trying to close. It is not the copy that is taking so long but the Close. Thanks for the Copy Destination idea though. Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB to clear any filters when closing a spreadsheet | Excel Discussion (Misc queries) | |||
| Closing Excel spreadsheet | Excel Discussion (Misc queries) | |||
| Making a spreadsheet calculate faster | Excel Discussion (Misc queries) | |||
| Make excel run large spreadsheets faster | Excel Discussion (Misc queries) | |||
| Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Linear Mode
