Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
Hi, I wrote an Excel/VBA application using a lot of modules and class
modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
You could try disc cleanup and defragmentation. That might help some, but I
doubt that is the real cause. I have had files of about 1 meg that took longer to save than files of three or four meg and have never figured out exactly why, although I suspect it is a protocol problem. I have no idea how Excel writes files to disc but it probably has a certain protocol and if the file contains formulas it does one thing, if it has code it does another and if it has both the it does something else, but I have no idea which causes what and how the formatting decisions are made to write the files and index them in the FAT. I have just learned to accept the fact that some do better than others. "sk005" wrote in message ... Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
One other thing. Some antivirus programs will scan your files before saving. Check to see if you can turn that off if you have that type of security software installed. "JLGWhiz" wrote in message ... You could try disc cleanup and defragmentation. That might help some, but I doubt that is the real cause. I have had files of about 1 meg that took longer to save than files of three or four meg and have never figured out exactly why, although I suspect it is a protocol problem. I have no idea how Excel writes files to disc but it probably has a certain protocol and if the file contains formulas it does one thing, if it has code it does another and if it has both the it does something else, but I have no idea which causes what and how the formatting decisions are made to write the files and index them in the FAT. I have just learned to accept the fact that some do better than others. "sk005" wrote in message ... Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
Hi JLGWhiz, Thanks for the quick reply. I am begining to wonder if this has
got anything to do with the no. of modules and class modules this application has got. Microsoft KB says it stores one control at a time to the disk. So more no.of modules and class modules more time it may take to save. Any thoughts? Thanks "JLGWhiz" wrote: You could try disc cleanup and defragmentation. That might help some, but I doubt that is the real cause. I have had files of about 1 meg that took longer to save than files of three or four meg and have never figured out exactly why, although I suspect it is a protocol problem. I have no idea how Excel writes files to disc but it probably has a certain protocol and if the file contains formulas it does one thing, if it has code it does another and if it has both the it does something else, but I have no idea which causes what and how the formatting decisions are made to write the files and index them in the FAT. I have just learned to accept the fact that some do better than others. "sk005" wrote in message ... Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
My suggestion is to reduce the file size. Some stuff to consider... 1. Delete (not clear) all rows/columns below and to the right of the last cell with data. 2. Run a count of objects on each sheet. I've seen 1000 copies of the same button stacked one on top of the other. 3. Eliminate the "pretty" cell formatting and any charts you don't actually need. 4. Clean out your regular code modules by... -copying and pasting the code to Notepad -delete the modules and replace them with new ones -copy and paste the code back from Notepad 5. If you are using userforms, check the list of controls against the actual ones in use. Several times I have found unused/forgotten controls positioned beyond the actual form width or height. 6. Buy low, sell high. <g -- Jim Cone Portland, Oregon USA "sk005" wrote in message Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
Like I said, I have no expertise in the mechanics of the save activity. Jim
had some suggestions that looked pretty good for tightening up your code and file structure. I don't know that the number of modules would make that much difference, but stacking the controls as Jim pointed out certainly would. That is if it actually saved the same control for each module that had a call for it. Again, I do not know the technical protocol for the save activity, so your guess is as good as mine in that case. "sk005" wrote in message ... Hi JLGWhiz, Thanks for the quick reply. I am begining to wonder if this has got anything to do with the no. of modules and class modules this application has got. Microsoft KB says it stores one control at a time to the disk. So more no.of modules and class modules more time it may take to save. Any thoughts? Thanks "JLGWhiz" wrote: You could try disc cleanup and defragmentation. That might help some, but I doubt that is the real cause. I have had files of about 1 meg that took longer to save than files of three or four meg and have never figured out exactly why, although I suspect it is a protocol problem. I have no idea how Excel writes files to disc but it probably has a certain protocol and if the file contains formulas it does one thing, if it has code it does another and if it has both the it does something else, but I have no idea which causes what and how the formatting decisions are made to write the files and index them in the FAT. I have just learned to accept the fact that some do better than others. "sk005" wrote in message ... Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
If a significant proportion of the file size is the VBA code I'd strongly
recommend you split the code and data into separate workbooks. Regards, Peter T "sk005" wrote in message ... Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
Thanks very much for your suggestions.
Hi Peter T: Could you please elaborate a bit more on your suggestion? Are you suggesting to make an Add-in of the code? Do you have any examples I can refer to? Thanks in advance. sk005 "Peter T" wrote: If a significant proportion of the file size is the VBA code I'd strongly recommend you split the code and data into separate workbooks. Regards, Peter T "sk005" wrote in message ... Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too long to save an Excel file
Whilst there are no hard rules, in general with large overall projects it's
a good idea to split processing (code), logic (formulas), data, input and output (not necessarily a separate file for each). If you have serious work to do there are excellent examples in the book "Professional Excel Development" by Bullen, Bovey & Green. I think I saw it has been updated for 2007. The code could indeed be in an Addin. You could install in the Addin manager which will cause it to load at startup, or you might only want it to load if/when your data workbook opens. Maybe you have several data workbooks, something like the following could go in each one - Sub OpenEventStuff() ' Workbook_Open or Auto_Open Dim sPath As String Dim wb As Workbook Const cCodeFile As String = "vvvTmp.xls" sPath = "C:\Documents and Settings\Owner\My Documents\" On Error Resume Next Set wb = Workbooks("myAddin.xla") On Error GoTo errH If wb Is Nothing Then Set wb = Workbooks.Open(sPath & cCodeFile) End If Exit Sub errH: ' errer handler code End Sub I have no idea what your code does but you might want a small amount of code in the data workbook to liase with the addin. You might ant to set a reference in the data workbook to the addin, if you do best to rename the default VBAProject in the addin. Not knowing what you have, but say you have a small amount of worksheet cells which are for manual data entry, instead of an addin you might combine the code and the entry into a normal workbook, then have the code validate the data and save it to a data file. I could go on with many more ideas but it all depends on what you are doing. Regards, Peter T "sk005" wrote in message ... Thanks very much for your suggestions. Hi Peter T: Could you please elaborate a bit more on your suggestion? Are you suggesting to make an Add-in of the code? Do you have any examples I can refer to? Thanks in advance. sk005 "Peter T" wrote: If a significant proportion of the file size is the VBA code I'd strongly recommend you split the code and data into separate workbooks. Regards, Peter T "sk005" wrote in message ... Hi, I wrote an Excel/VBA application using a lot of modules and class modules. the file size of this application tempate is 6.8M. It takes just over two minute to save to network share and above a minute to save locally. Is there any way to improve the saving time? I tried to implement the Micosoft solution of saving it locally (c:\temp ) mostly and background copy to the share drive and then re-opening newly coped file from the share but the best I can hope for is about a minute. Is there anyway I can improve the saving time significantly? Thanks in advance. sk005 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I save an excel file with long number columns in cvs form. | Excel Discussion (Misc queries) | |||
I can't save long file names, only short one, how can I do this? | Excel Discussion (Misc queries) | |||
Long Save Times with File-Save | Excel Discussion (Misc queries) | |||
Excel file takes a long time to save | Excel Discussion (Misc queries) | |||
Takes long to save the file (Speed problem) | Excel Programming |