Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Command Replacement (VBA Excel 2000)

    I recently used a very handy feature in Word 2000 for replacing the SaveAs command by simply writing a macro and calling it Sub FileSaveAs()

    I would now like to do the same thing in Excel but the same method doesn't seem to work !

    Does anyone know how to achieve this in Excel ??

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    I would use the Workbook BeforeSave event procedure.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    In word I found that the before save event didn't realy occure before the save, it actualy occured before the show save dialog. I was not able to trap the selected path.
    When is the BeforeSave event in Excel triggered ?

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Command Replacement (VBA Excel 2000)

    There is a more or less generic procedure in Office to intercept menu and toolbar commands by their ID. A recent thread that might be helpful begins with Post #147709. Hope this helps.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    In Excel, it happens before any part of the save, so the dialog has not been shown. This is the way you want it since otherwixe you would not be able to prevent the dialog from being shown if you don't want it shown. However, you can display the dialog yourself using the GetOpenFilename method which will get a path and filename from the user without saving the file. Then you can do whatever you need to do.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    Thanks

    That works well.

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    Thanks for the info
    LargareColeman's suggestion works well in this case, but your link will be very usefull if I ever need to trap a different command, Thanks

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    I have another related question you may be able to help me with.

    I need to know which of Save or SaveAs has initiated the OnSave Event.

    Is there some way to detect this ??

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    The SaveAsUi parameter to the event routine will be true if SaveAs and False if Save.
    Legare Coleman

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    Thanks
    But this doesn't seem to work ?
    The SaveAsUi can be set by the routine to determine if the dialog is displayed or not.
    At least thats what the MS doco implies !
    But I can't get it to do anything Cancel seems to control the whole shabang !
    SaveAsUi appears to remain True whichever of Save or SaveAs initiates the Save Event.
    Got any other ideas.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    On my system, the SaveAsUi is set to False when the routine is entered if I select Save, and is set to True if I select SaveAs. The only exception to this is if I do a File/Save on a new workbook that has never been saved. This is because Excel defaults to a SaveAs on a workbook that has never been saved. The BeforeSave event routine template indicated that the SaveAsUi parameter is passed ByVal, which would indicate that you can not set it in the routine, any change should be ignored.

    The Cancel parameter should always be set to False when the routine is entered. If you set it to True, then the Save or SaveAs is not performed by Excel after the event routine is completed. This is so that you can do the save yourself, or prevent the save from being done at all.

    This works for me. If it is not working for you, then could you post your code so we can see what you are doing?
    Legare Coleman

  12. #12
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Replacement (VBA Excel 2000)

    Thanks

    You are correct.

    My code uses SavesAs in order to change the name of the workbook so it is always an unsaved workbook
    That's why it didn't work !

    The user is unaware of this since they have opened a data sheet which imports the data to a template where they can opperate on the data and where the SaveEvent macro is.
    The data sheet is closed once the data has been put in the template.
    When they Save a new data workbook is created with the same name, and transparently overwrites the old one. (If I can detect it is a Save)
    I needed to know which of Save or SaveAs was selected so I could simulate the normal behaviour where Save doesn't bring up the dialog unless the document had not been saved before, but SaveAs does, so the user has the option of renaming or saveing the data elswhere.
    I have used this stratagy because the Template is large and many datasheets will be produced on many different computers. It is easier to distribute the template than an addin since many computers both desktop and laptops are involved.
    It sounds like I can't make the process completly transparent and the user will have to put up with seeing the SaveAs dialog even when they save. Or back to the drawing board, perhaps I can set a flag to indicate when it is new or imported data.
    It is only a minor problem

    Thanks for the Help

Posting Permissions

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