Save "speed up"

[Mike]

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.

---
Regards,
Norman



"Mike" <Mike@discussions.microsoft.com> wrote in message
news:729C8DE1-9008-43CA-A45E-F9CE1DA02605@microsoft.com...
> 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

[Mike]

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" <Mike@discussions.microsoft.com> wrote in message
> news:729C8DE1-9008-43CA-A45E-F9CE1DA02605@microsoft.com...
> > 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,

> 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" <Mike@discussions.microsoft.com> wrote in message
news:B69666BA-1020-4CB3-B279-4B7E9C6D348E@microsoft.com...
> 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" <Mike@discussions.microsoft.com> wrote in message
>> news:729C8DE1-9008-43CA-A45E-F9CE1DA02605@microsoft.com...
>> > 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
>>
>>
>>

[Martin Brown]

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

[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)

--
Cheers
Nigel



"Mike" <Mike@discussions.microsoft.com> wrote in message
news:729C8DE1-9008-43CA-A45E-F9CE1DA02605@microsoft.com...
> 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

[Mike]

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" <Mike@discussions.microsoft.com> wrote in message
> news:729C8DE1-9008-43CA-A45E-F9CE1DA02605@microsoft.com...
> > 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
>
>
>