Really clever, Adrian.
A good refinement would be as follows:
1. Having now saved as a new filename, the old filename must be
used again to save with else you lose that. So repeat your save-as code
but this time without the random number addition. Or does your
SaveCopyAs already account for that?
2. How about making it a sequential number instead of random? You
can have the code
a. Remember the last number used and increment by one
b. Find the number using FIND("EDR/",filename) to get position, add
4, and use MID(result, length of random string) and increment by 1.
c. Use an inputbox after your messagebox to have the user enter a
4-digit text value, e.g."0001" and use that as the number.
Regards
Paul
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of Adrian Mollenhorst
Sent: Tuesday, 21 February 2012 6:36 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] 2010 Crash
So I created this rough and ready workbook BeforeSave event procedure.
So
every time I save my spreadsheet, I get a copy saved as another name in
another location. I just used a random number as an addtion to the
filename
because I'm not elegant enough with VBA yet to design something
smarter....
:-)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim Msg As String
Dim Ans As Integer
Dim FName As String
'Msg = "Would you like to make a backup of this file?"
'Ans = MsgBox(Msg, vbYesNo)
random = WorksheetFunction.RandBetween(1000000, 5000000)
'If Ans = vbYes Then
FName = "D:\Documents\EDR\" & random & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs FName
'End If
End Sub
On Tue, Feb 21, 2012 at 1:23 AM, Adrian Mollenhorst <
adrian.mollenhorst@gmail.com <mailto:adrian.mollenhorst%40gmail.com> >
wrote:
> Yep,
>
> That's what I mean -save as. In fact, I might do a quick project today
to
> create a button that does it automatically for me.
>
> Adrian
>
>
>
>
> On Mon, Feb 20, 2012 at 5:44 PM, <david.smart@ubs.com
<mailto:david.smart%40ubs.com> > wrote:
>
>> **
>>
>>
>> Doing the save twice - to the same file name - will not improve your
>> chances. The two saves would need to be to different file names,
using
>> Save As.
>>
>> I am in the habit of always using Save As during development of a
>> workbook. I simply append a new version number to the file name each
>> time. E.g. while developing Fred.xls, I would save
>>
>> Fred x1
>> Fred x2
>> etc
>>
>> The "x" is a reminder to myself that these are development versions.
>>
>> Once a workbook has finished development, I save it again, without
the
>> version number and zip up the development versions for short-term
>> archiving.
>>
>> When a workbook is "in production", I simply rely on an automated
backup
>> procedure to make sure that I always have several previous edits of
the
>> workbook if I need to regress.
>>
>>
>> Regards, Dave S
>>
>> ________________________________
>>
>>
>> From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ]
On
>> Behalf Of Adrian Mollenhorst
>> Sent: Monday, 20 February 2012 19:26
>> To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
>> Subject: Re: [ExcelVBA] 2010 Crash
>>
>>
>> Thanks Paul,
>>
>> Interesting that you suggest it might be a corrupt save. Perhaps I
>> should
>> save twice when I save, so reduce the chances of this happening on
both
>> saves.
>>
>> Adrian
>>
>> On Sun, Feb 19, 2012 at 8:21 PM, Paul Vermeulen <
>> paul.vermeulen@vulcantech.com.au
<mailto:paul.vermeulen%40vulcantech.com.au>
>> <mailto:paul.vermeulen%40vulcantech.com.au> > wrote:
>>
>> > **
>>
>> >
>> >
>> > Hi Adrian
>> >
>> > I use the 32 bit version of Office 2010. Have had no issues with
it. I
>> > ALSO use Excel 2003, and I find the 2010 Excel more stable when I
push
>> > the limits of the application's code execution. Has to do with
larger
>> > limits for workbook sizes, memory usage, etc.
>> >
>> > Glad you recovered your workbook. Sounds like a corrupt save, by
the
>> > way.
>> >
>> > Regards
>> >
>> > Paul
>> >
>> > From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
<mailto:ExcelVBA%40yahoogroups.com>
>> [mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
<mailto:ExcelVBA%40yahoogroups.com> ]
>>
>> On
>> > Behalf Of Adrian Mollenhorst
>> > Sent: Sunday, 19 February 2012 9:58 PM
>> > To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
<mailto:ExcelVBA%40yahoogroups.com>
>> > Subject: [ExcelVBA] 2010 Crash
>> >
>> >
>> > Hi All,
>> >
>> > I have recently begun using VBA code quite a bit with my 64bit
Office
>> > 2010.
>> > Today I tried to open my spreadsheet only to find Excel crashed
every
>> > time
>> > I tried to open the workbook. I have managed to go back to a
previous
>> > version of the workbook, and lost only a few hours of work, but in
my
>> > attempt to recover, I found quite a bit of info on the web about
the
>> > dubious reliability of Excel 2010. I'm not sure if this is indeed
>> true,
>> > and
>> > wanted to get the experts (you guys) opinion, and if it is true,
whats
>> > the
>> > best way to safeguard my work moving forward?
>> >
>> > I really love excel 2010, so I don't want to downgrade if I don't
have
>> > to,
>> > and don't want to speak ill if of a great product if indeed this is
>> just
>> > a
>> > once off issue.
>> >
>> > Cheers!
>> >
>> > Adrian
>> >
>> > [Non-text portions of this message have been removed]
>> >
>> > [Non-text portions of this message have been removed]
>> >
>> >
>> >
>>
>> [Non-text portions of this message have been removed]
>>
>> ----------
>>
>> Visit our website at http://www.ubs.com
>>
>> This message contains confidential information and is intended only
>> for the individual named. If you are not the named addressee you
>> should not disseminate, distribute or copy this e-mail. Please
>> notify the sender immediately by e-mail if you have received this
>> e-mail by mistake and delete this e-mail from your system.
>>
>> E-mails are not encrypted and cannot be guaranteed to be secure or
>> error-free as information could be intercepted, corrupted, lost,
>> destroyed, arrive late or incomplete, or contain viruses. The sender
>> therefore does not accept liability for any errors or omissions in
the
>> contents of this message which arise as a result of e-mail
transmission.
>> If verification is required please request a hard-copy version. This
>> message is provided for informational purposes and should not be
>> construed as a solicitation or offer to buy or sell any securities
>> or related financial instruments.
>>
>> UBS reserves the right to retain all messages. Messages are protected
>> and accessed only in legally justified cases.
>>
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>
>
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar