Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Saving copies automatically (XL97/WinNT)

    Could someone help me with this, please? I've had the following macro (created with the help of fellow loungers) in my normal.dot (in Word) for quite sometime now, because network problems occasionally throw my work away with no warning and for no apparent reason!, but I've recently started doing a lot of work in XL and need to convert it to use there too. Unfortunately, nothing I've tried will succeed in converting this to use in XL! What am I missing?!

    <pre>Sub filesave()
    Dim docName As Boolean, tmpFullname As String
    docName = ActiveDocument.name Like "Document#*"
    tmpFullname = ActiveDocument.FullName
    If docName = True Then
    Dialogs(wdDialogFileSaveAs).Show
    Else
    ActiveDocument.SaveAs FileName:="c:databacup" + ActiveDocument.name, _
    AddToRecentFiles:=False
    ActiveDocument.SaveAs FileName:=tmpFullname
    End If
    End Sub
    </pre>

    Beryl M


  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Jan Karel,
    You left a couple of occurrences of ActiveDocument in there - they need to be replaced with ActiveWorkbook (or tmpFullName if it's ActiveDocument.Name).
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Thanks, Jan (and Rory), that's exactly what I want - but where do I put it? I tried putting it in the ThisDocument area of my personal.xls, but it's not picking it up - when I put a breakpoint in the first line of code it takes no notice whatever and does not save a copy.

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Beryl M


  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Oops. Should have done a search and replace in the VBE instead of editing in the editbox of the lounge reply page <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    In the VBE, select the file you want to add the code to. Choose Insert, module. Then paste the code into that.
    Now in the Thisworkbook module, you might consider setting a shortcut-key (control-shift-s, remove the plus signs to get control-s) to that macro:

    Private Sub Workbook_Open()
    Application.OnKey "+^s", "FileSave"
    End Sub

    Private Sub Workbook_BeforeClose()
    Application.OnKey "+^s"
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Yes, but that's exactly the point - I don't want it to be in one workbook, I want it to run globally, on whatever book I try to save, the same as it does in Word!

    Surely there must be a global area where a FileSave macro will hijack the standard one?! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Beryl M


  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Yes, just put the macro in the Personal.xls file.

    In XL, workbooks that reside in the XLSTART directory are all opened at startup, so any code in them is available to each excel session.
    Personal.xls is a standard excel file excel creates (in the appropriate directory) when you record a macro and tell excel to put it in you personal macro workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Yes, but as I said in my post 180505 above, I tried that and it didn't work!
    Beryl M


  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Beryl,
    First, I wouldn't put the macro into the ThisWorkbook module, I'd put it into a normal module. Second, in addition to Jan Karel's Application.OnKey macros, you will need to assign this macro to the save button on your toolbar if that's what you use for saving rather than shortcut keys. Excel does not automatically override built-in commands (that I'm aware of). You could also use application level events to achieve this.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    No, XL doesn't, but it is relatively simple to assign the save macro to the standard save toolbar button, just rightclick the toolbar , choose customize, rightclick the button itself, choose attach macro and choose from the macro list.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Ah - I think that's probably the bit of information that I was missing - XL won't automatically hijack the macros like Word does!

    Thanks, Rory, I think you might have solved the mystery!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Rory and Jan
    Beryl M


  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving copies automatically (XL97/WinNT)

    Somthing like this:

    <pre>Sub filesave()
    Dim wkbkName As Boolean, tmpFullname As String
    wkbkName = ActiveWorkbook.name Like "Book#*"
    tmpFullname = ActiveWorkbook.FullName
    If wkbkName = True Then
    application.Dialogs(xlDialogSaveAs).Show
    Else
    ActiveWorkbook.SaveCopyAs FileName:="c:databacup" & ActiveDocument.name
    ActiveDocument.Save
    End If
    End Sub
    </pre>



    Consider downloading my Autosafe addin too, timed saving of your work in a backup directory with recovery options after a crash of XL:

    http://www.bmsltd.ie/mvp

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •