Consulting

Results 1 to 12 of 12

Thread: Save a copy solution

  1. #1

    Save a copy solution

    Hello,

    We have an excel doc that is placed in a shared folder. Is it possible that every time this excel is saved by anyone to save o copy of it in another specific location (perhaps on my pc).?

    Thx,
    Nedy

  2. #2
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    of course.
    put this to thisworkbook codepage:
    [VBA]
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    set act=activeworkbook
    activeworkbook.saveas filename:="c:\mybackup.xls"
    activeworkbook.saveas filename:=act.fullname
    End Sub

    [/VBA]
    :-)
    L@ja

  3. #3
    It does not work :-S

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi L@ja
    Your code is crashing Excel for me. I think you're getting into an endless loop.
    Simpler to save a copy
    [VBA]
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveWorkbook.SaveCopyAs Filename:="c:\mybackup.xls"
    End Sub

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Try this

    [vba]

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    ThisWorkbook.SaveCopyAs Filename:="C:\Backups\" & Format(Date,"yyyymmdd ") & "mybackup.xls"
    ThisWorkbook.Save
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    I don't get it ! Why it doesn't save anything? ... It work for u guys ?

  7. #7
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    Where are you putting the code? It must go in the ThisWorkbook module.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    sorry, my code was really wrong :-(
    L@ja

  9. #9
    VBAX Regular
    Joined
    Jun 2005
    Posts
    51
    Location
    I had similar problem recently. See thread http:\vbaexpress.com/forum/showthread.php?t=14614

    The following worked for me in the end...(change the location to your archive folder)


    [VBA]
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sFile
    Dim location As String
    Application.EnableEvents = False
    location = "c:\log\staff authorisations archive\"
    sFile = Replace(ThisWorkbook.Name, ".xls", " Backup ") & Format(Now, "yyyymmdd hh-mm-ss")
    ThisWorkbook.SaveCopyAs location & sFile & ".xls"
    Application.EnableEvents = True
    End Sub [/VBA]

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Quote Originally Posted by L@ja
    sorry, my code was really wrong :-(
    It's called the learing process

    BTW, when you run code which will trigger the Event macro, add the line
    [VBA]Application.EnableEvents = False[/VBA] at the start, resetting to True at the end. This prevents the loop.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Quote Originally Posted by nedy_03
    Is it possible that every time this excel is saved by anyone to save o copy of it in another specific location (perhaps on my pc).?
    Apologies if I'm having a blinding flash of the obvious here, but all your examples save the copy to C: drive. Of course if another user is opening and saving this file from a shared folder, it would save to their C: drive, not Nedy's ...

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Good point GG. So can a shared folder on my PC be accessed directly from anywhere on the network? If I have time I'll try at work.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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