Results 1 to 4 of 4

Cascaded Open / Close / Save not working

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Question Cascaded Open / Close / Save not working

    Ok, firstly an overview
    I have a 'Summary' workbook which collates values from a series of 'sub-workbooks', (and can also update values in those sub workbooks).
    the subworkbooks are setup so that when they are saved they also copy certain values out to another 'backup' file.
    So there are 2 possible routes that the files will be used in:

    1) SubWorkbook opened directly
    - User opens SubWorkbook and makes some changes.
    - User closes the SubWorkbook saving changes, or clicks the save button. The BeforeSave event opens the "restore" workbook, copies over the key values from the subworkbook, then saves & closes the "restore" workbook.

    2) SubWorkbook opened remotely
    - User / Admin opens the "Summary" workbook and changes an option.
    - The summary workbook opens a subworkbook and makes changes, then saves it. The subworkbook should (as before), then open the restore workbook and save the key values etc.

    The problem is in the second scenario the 'restore' workbook doesn't get opened.

    I have created a set of 3 workbooks to illustrate the problem here.
    Book1.xls = Summary workbook
    Book2.xls = Subworkbook
    Book3.xls = Restore workbook.

    If you open book2 and then save it the 'BeforeSave' event will cause it to open book3 and write the output of 'Now()' to the next free row of book3-columnA, before then closing and saving book3.

    If you open Book1 and run the only macro present then it will open Book2 and write the output of 'Now()' to the next free row of book2-columnA, before then closing and saving book2, which in turn triggers the 'BeforeSave' event of Book2, but book2 doesn't manage to open book3

    I've tried using .Close savechanges:=True as well as [i].Save[//i] followed by .Close savechanges=False in Book1 in case the Excel was refusing to open a workbook whilst another was saving but it made no difference.

    Here is the code for the test:
    Book1.xls - Standard module
    Sub test_saving()
    
    Dim wb As Workbook
        Workbooks.Open Filename:=ThisWorkbook.Path + "\Book2.xls", _
                       UpdateLinks:=False, _
                       ignorereadonlyrecommended:=True
        Set wb = Workbooks("Book2.xls")
        With wb.Worksheets(1)
            .Cells(.UsedRange.Rows.Count + 1, 1).Value = Now()
        End With
        wb.Close savechanges:=True
    End Sub
    Book2.xls - "ThisWorkBook" section:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wb As Workbook
        Workbooks.Open Filename:=ThisWorkbook.Path + "\Book3.xls", _
                       UpdateLinks:=False, _
                       ignorereadonlyrecommended:=True ' Doesn't work if Book1 opened thisworkbook, but does if opened directly!
        Set wb = Workbooks("Book3.xls")
        With wb.Worksheets(1)
            .Cells(.UsedRange.Rows.Count + 1, 1).Value = Now()
        End With
        wb.Close savechanges:=True
    End Sub
    Any ideas of a workaround or solution to this?

    Thanks
    Attached Files Attached Files
    Last edited by Phil_V; 08-04-2009 at 07:47 AM.
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1