Results 1 to 3 of 3

Thread: SaveAs (XP)

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SaveAs (XP)

    My issue is that the active file (book2.xls) is not saved when code from book1.xls tries to save the active file; in this case book2.xls.

    I have the following code as part of the "This Workbook" object in book2.xls

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sFileSaveName As Variant
    If SaveAsUI Then
    sFileSaveName = Application.GetSaveAsFilename("" & GetSpecialFolder(CSIDL_DESKTOP) & "Copy Of " & ThisWorkbook.Name)
    Application.EnableEvents = False
    If sFileSaveName <> False Then
    ' Run some more code
    ThisWorkbook.SaveAs Filename:=sFileSaveName, FileFormat:=xlNormal
    End If
    Application.EnableEvents = True
    Else
    Application.EnableEvents = False
    ' Run some more code
    ThisWorkbook.Save
    Application.EnableEvents = True
    End If
    Cancel = True
    End Sub


    The intent of the code in book2.xls is to take control over the "save as". If the user selects file/save as then a copy of the workbook is saved to the user's desktop. If the save button is selected, then the file is saved to it's orginal location. The code in book1.xls is quite simple "activeworkbook.save" where the activeworkbook = book2.xls. The file book2.xls is located on a server.

    Any assistance would be appreciated.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SaveAs (XP)

    The BeforeSave event only applies to the workbook that contains the code, not to other workbooks. If you want to run code before any workbook is saved, you must use the WorkbookBeforeSave event of the Application object. Writing application level events involves creating a class module.

    Steps to take:
    <UL><LI>Activate the Visual Basic Editor
    <LI>Select Personal.xls in the Project Explorer
    <LI>Select Insert | Class Module. Name this module clsBeforeSave.
    <LI>Add the following code to the class module:

    Public WithEvents App As Application

    Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' Your code goes here; use Wb instead of ActiveWorkbook
    End Sub

    <LI>Select Insert | Module.
    <LI>Add the following code to the new module:

    Public MySaveHandler As New clsBeforeSave

    Sub InitializeApp()
    Set MySaveHandler.App = Application
    End Sub

    <LI>Call InitializeApp from a macro called Auto_Exec in a standard module or from the Workbook_Open event handler in the ThisWorkbook module of Personal.xls.[/list]Type "using events with the application" (without quotes) in the Answer Wizard to learn more about application level event handlers.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SaveAs (XP)

    Hans,

    Thanks for your reply. After reviewing the code in book1.xls I determined that the application EnableEvents (False and True) needed to be included.

    John

Posting Permissions

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