Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Auto_Close Save Problem (VBA Excel 2003)

    Hi,

    Ive got a macro that saves a particular workbook a folder on C: - Which works fine, however when I view the saved workbook on C: I get the save changes message box on closing, despite me trying to suppress this, as follows;

    Sub Auto_Close()
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.SaveCopyAs "C:testfileTest.XLS"
    Activeworksheet.Saved = True
    Application.DisplayAlerts = True
    End Sub

    I get a message asking if I want to save changes to 'Test.XLS' and then when I press 'Yes', I get another save message relating to 'Book1.xls'

    I know Im missing something somewhere but not sure what to try next. Any pointers will be grately appreciated.

    Cheers.
    Lee

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

    Re: Auto_Close Save Problem (VBA Excel 2003)

    Not sure why you are using SaveCopyAs?

    Are you trying to save a copy of the workbook?

    Is there no need to save the workbook with the code?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto_Close Save Problem (VBA Excel 2003)

    Hi,

    Yes, the plan is that when a user finishes reviewing their spreadsheet that it saves in a designated place. To make life easier Id plan to save the spreadsheet/workbook by autoclose rather they press the save button or a toolbar button running a save macro.

    I had thought that SaveCopyAs was probably the right route to follow, but Im a little unsure now.

    Any advice given will be much appreciated.

    Cheers.
    Lee

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

    Re: Auto_Close Save Problem (VBA Excel 2003)

    If you just need the workbook to save itself in the same location, using the same name, just use:

    ThisWorkbook.Save

    That's all.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto_Close Save Problem (VBA Excel 2003)

    Hi,

    Thanks for your response.

    Unfortunately, it'll will not only be saved locally, but also to a server location. Everyday they'll be opening the workbook ,from their desktop, run a macro to clear old data and populate with new. On closing, once they've made any necessary amendments, it needs to be saved away from their local pc, hence my thinking the SaveCopyAs was the right syntax as I could state the correct path.

    All seemd to work well until I went to the saved copy on the server and started encountering the problems stated above.

    Cheers.
    Lee

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

    Re: Auto_Close Save Problem (VBA Excel 2003)

    Do you want to save locally AND on the server?

    In that case you need both the SaveCopyAs method and the Save method.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto_Close Save Problem (VBA Excel 2003)

    I believe that you need to review the following.
    If you have a workbook open that has been changed, but has not been saved then:
    <UL><LI> Save -- The workbook is saved in its original location, the open workbook is identified as having been saved, and can be closed without an alert.
    <LI> Save As -- The workbook is saved in a location selected by the user, the open workbook is identified as having been saved, and can be closed without an alert.
    <LI> SaveCopyAs -- A copy of the workbook is saved in a location selected by the user, the open workbook is NOT identified as having been saved, and an alert will be raised on closing.[/list]
    Regards
    Don

  8. #8
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto_Close Save Problem (VBA Excel 2003)

    Hi,

    Many thanks for your response, I'll have a play and report how I get on.

    Cheers.
    Lee

  9. #9
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto_Close Save Problem (VBA Excel 2003)

    Hi,

    With your suggestions in mind Ive amended the scripting utilizing;

    ActiveWorkbook.Saved = True
    ThisWorkbook.SaveAs "S:.......XLS"

    Using SaveAs has stopped the messages when closing the version saved on the server, which SaveCopyAs was generating.

    Many thanks for everyones help on this, its much appreciated.

    Cheers.
    Lee

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

    Re: Auto_Close Save Problem (VBA Excel 2003)

    This should suffice though:

    ThisWorkbook.SaveAs "S:.......XLS"
    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
  •