Save "speed up"

[Guest]

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

 

[Norman Jones]

Hi Mike,

Try clearing accumulated tmp files from your Temp directory:

Close Excel
Windows Start button| Run | %temp% | OK
Delete all files and folders.

 

[Guest]

Well. The file location is in a network, with limited access (for me) so that
doesnt help much.

 

[Norman Jones]

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.

 

[Nigel]

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)

 

[Martin Brown]

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

 

[Guest]

Thank's guy's.

But it seem's that i can't do anything about this small problem.