Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "speed up"
Hi!
I have a workbook that includes a macro (size is 750 kb). When the user opens the workbook a number is generated (+ 1). The workbook is saved to a temp location with the new number and the original workbook is saved and closed (also with the new number). My question is: is it possible to speed up this (now it takes 20-30 sec). The code: 'This code saves the original workbook with the new number: If Me.Saved = False Then Me.Save 'This code saves the temp workbook: FileSaveName = ActiveWorkbook.Path FileSaveNameEnd = FileSaveName & "\Arkiv\" varName = ActiveSheet.Name varDir = FileSaveNameEnd varYear = Format(Date, "yyyy") varNr = ReportNumber ActiveSheet.SaveAs Filename:=varDir & varName & varNr & varYear Can i do something else to speed up the saving process? Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "speed up"
Hi Mike,
Try clearing accumulated tmp files from your Temp directory: Close Excel Windows Start button| Run | %temp% | OK Delete all files and folders. --- Regards, Norman "Mike" wrote in message ... Hi! I have a workbook that includes a macro (size is 750 kb). When the user opens the workbook a number is generated (+ 1). The workbook is saved to a temp location with the new number and the original workbook is saved and closed (also with the new number). My question is: is it possible to speed up this (now it takes 20-30 sec). The code: 'This code saves the original workbook with the new number: If Me.Saved = False Then Me.Save 'This code saves the temp workbook: FileSaveName = ActiveWorkbook.Path FileSaveNameEnd = FileSaveName & "\Arkiv\" varName = ActiveSheet.Name varDir = FileSaveNameEnd varYear = Format(Date, "yyyy") varNr = ReportNumber ActiveSheet.SaveAs Filename:=varDir & varName & varNr & varYear Can i do something else to speed up the saving process? Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "speed up"
Well. The file location is in a network, with limited access (for me) so that
doesnt help much. "Norman Jones" wrote: Hi Mike, Try clearing accumulated tmp files from your Temp directory: Close Excel Windows Start button| Run | %temp% | OK Delete all files and folders. --- Regards, Norman "Mike" wrote in message ... Hi! I have a workbook that includes a macro (size is 750 kb). When the user opens the workbook a number is generated (+ 1). The workbook is saved to a temp location with the new number and the original workbook is saved and closed (also with the new number). My question is: is it possible to speed up this (now it takes 20-30 sec). The code: 'This code saves the original workbook with the new number: If Me.Saved = False Then Me.Save 'This code saves the temp workbook: FileSaveName = ActiveWorkbook.Path FileSaveNameEnd = FileSaveName & "\Arkiv\" varName = ActiveSheet.Name varDir = FileSaveNameEnd varYear = Format(Date, "yyyy") varNr = ReportNumber ActiveSheet.SaveAs Filename:=varDir & varName & varNr & varYear Can i do something else to speed up the saving process? Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "speed up"
Hi Mike,
Well. The file location is in a network, with limited access (for me) so that doesnt help much Whilst it is frequently true that the quality of assistance bears a proportional relationship to the ability and imagination of the respondent -your bad luck in this case! - there is a similar relationship to the sufficiency of proffered information. Given the network restrictions, perhaps you should speak to your network administrator. In any event, I suspect that it is unlikely that your code has any bearing on the save time issue. --- Regards, Norman "Mike" wrote in message ... Well. The file location is in a network, with limited access (for me) so that doesnt help much. "Norman Jones" wrote: Hi Mike, Try clearing accumulated tmp files from your Temp directory: Close Excel Windows Start button| Run | %temp% | OK Delete all files and folders. --- Regards, Norman "Mike" wrote in message ... Hi! I have a workbook that includes a macro (size is 750 kb). When the user opens the workbook a number is generated (+ 1). The workbook is saved to a temp location with the new number and the original workbook is saved and closed (also with the new number). My question is: is it possible to speed up this (now it takes 20-30 sec). The code: 'This code saves the original workbook with the new number: If Me.Saved = False Then Me.Save 'This code saves the temp workbook: FileSaveName = ActiveWorkbook.Path FileSaveNameEnd = FileSaveName & "\Arkiv\" varName = ActiveSheet.Name varDir = FileSaveNameEnd varYear = Format(Date, "yyyy") varNr = ReportNumber ActiveSheet.SaveAs Filename:=varDir & varName & varNr & varYear Can i do something else to speed up the saving process? Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "speed up"
Since you are saving the same workbook twice, why not consider copying the
first saved workbook as a temp version, then change its name ? Also if you do nothing to the workbook when it first opens, you could apply the above logic to both the first and second copy, in this case a save is not even involved. (If your network restrictions do not allow it - please ignore this proposal) -- Cheers Nigel "Mike" wrote in message ... Hi! I have a workbook that includes a macro (size is 750 kb). When the user opens the workbook a number is generated (+ 1). The workbook is saved to a temp location with the new number and the original workbook is saved and closed (also with the new number). My question is: is it possible to speed up this (now it takes 20-30 sec). The code: 'This code saves the original workbook with the new number: If Me.Saved = False Then Me.Save 'This code saves the temp workbook: FileSaveName = ActiveWorkbook.Path FileSaveNameEnd = FileSaveName & "\Arkiv\" varName = ActiveSheet.Name varDir = FileSaveNameEnd varYear = Format(Date, "yyyy") varNr = ReportNumber ActiveSheet.SaveAs Filename:=varDir & varName & varNr & varYear Can i do something else to speed up the saving process? Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "speed up"
Mike wrote:
Well. The file location is in a network, with limited access (for me) so that doesnt help much. Your best bet then is to have the workbook remain static and load and save a tiny file over the network that contains only the dynamically changing data. The workbook then loads and increments the counter file when it loads (possibly with a lock mechanism to prevent two concurrent users getting the same tag ID). Regards, Martin Brown |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "speed up"
Thank's guy's.
But it seem's that i can't do anything about this small problem. "Nigel" wrote: Since you are saving the same workbook twice, why not consider copying the first saved workbook as a temp version, then change its name ? Also if you do nothing to the workbook when it first opens, you could apply the above logic to both the first and second copy, in this case a save is not even involved. (If your network restrictions do not allow it - please ignore this proposal) -- Cheers Nigel "Mike" wrote in message ... Hi! I have a workbook that includes a macro (size is 750 kb). When the user opens the workbook a number is generated (+ 1). The workbook is saved to a temp location with the new number and the original workbook is saved and closed (also with the new number). My question is: is it possible to speed up this (now it takes 20-30 sec). The code: 'This code saves the original workbook with the new number: If Me.Saved = False Then Me.Save 'This code saves the temp workbook: FileSaveName = ActiveWorkbook.Path FileSaveNameEnd = FileSaveName & "\Arkiv\" varName = ActiveSheet.Name varDir = FileSaveNameEnd varYear = Format(Date, "yyyy") varNr = ReportNumber ActiveSheet.SaveAs Filename:=varDir & varName & varNr & varYear Can i do something else to speed up the saving process? Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
"Subscript out of range" error for: Workbooks("Test1.xls").Save | Excel Programming | |||
save and restore "Workbook Menu Bar" & "Cell" menus | Excel Programming |