Results 1 to 5 of 5

Thread: Excel - detecting open workbooks

  1. #1

    Thread Starter
    Lively Member TheFIDDLER's Avatar
    Join Date
    May 2002
    Location
    here and there and far away
    Posts
    126

    Excel - detecting open workbooks

    I have an application in Excel that once run, takes over the excel environment, locks out all menus, disables shortcut keys, and leaves very little of the visible excel environment. User has access to all commands he needs for my program and that's it.

    Problem occurs if user was say doing his taxes in another open excel workbook, opens my program and realizes too late that he is unable to switch back to his former workbook, or if using 2000 or XP, can switch back but no longer has his menus. Hasn't happened yet, but I can imagine the phone call when this happens.

    Looking for code that would identify if a user has a file already open. And also that would ensure it does not detect the default workbook that opens with excel as an open file.

    This would allow me to prompt the user to save his/her files and close them. Or to abort the opening of the program. This would be done via message boxes or forms, giving the user the chance to save+exit existing files, exit without save of existing files, or to abort program launch.
    -----
    #VBA, VB 6 Professional Edition, Office XP Developper. Excel 97, Excel 2000, Excel XP

    I miss my VIC 20.
    Never should have upgraded to my commodore 64. ...

  2. #2
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Something like this?
    VB Code:
    1. Private Sub Workbook_Open()
    2.  
    3.     Dim i As Integer
    4.    
    5.     If Workbooks.Count > 1 Then
    6.         If MsgBox("You need to save and close all your other workbooks before continuing!" & _
    7.         vbNewLine & "If you want to Save and Close your other workbooks the click 'Yes'", vbYesNo + vbQuestion, _
    8.         "TheFIDDLER") = vbYes Then
    9.             For i = 1 To Workbooks.Count - 1
    10.                 Workbooks(i).Close True
    11.             Next
    12.         Else 'vbNo 'Close theFIDDLER
    13.             Workbooks(Workbooks.Count).Close False
    14.         End If
    15.     End If
    16.        
    17. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    Lively Member TheFIDDLER's Avatar
    Join Date
    May 2002
    Location
    here and there and far away
    Posts
    126
    This might work- I will have to tweak the code a bit.

    Your example is asking me if I want to save already opened books and then proceeds to close them for me. Except, it also closes my new book, and leaves one of the files it should be closing still opened. I think to do with workbook.count

    I think I will need a solution where the user can:
    a) no solution needed - no active workbooks detected
    b) save and close existing workbooks
    c) close without save existing workbooks.
    d) check for Book1 and do not prompt if no data entered since same as case a.

    Anyone know the status of the default workbook that is opened with excel. Called Book1 by default - seems to disappear automatically if no data entered in cell. But if data entered in cells, remains as Book1 in the Window Menu.
    Book1 seems to be detected in workbook.count even if no data is entered. But it seems a shame to prompt the user to save if this will only disappear with the opening of my file. (provided no- data entry has taken place.)
    -----
    #VBA, VB 6 Professional Edition, Office XP Developper. Excel 97, Excel 2000, Excel XP

    I miss my VIC 20.
    Never should have upgraded to my commodore 64. ...

  4. #4
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    Ok, I have done a little more testing and I have determined that
    it depends on the way you open the workbooks.

    1.) If a user opens just Excel first and then clicks the Open to
    open a previous workbook (or through the recent menu list), the
    workbook is in a separate collection from any current workbooks.
    You can check the Window menu item to view a list of workbooks
    collection for that instance of Excel.

    2.) If a user double-clicks the icon of a workbook, then it is added
    to the workbook collection and will be picked up by my code.

    Here is updated code to handel #2. To handel #1 may not be
    required depending on how your program manipulates Excel to
    remove the menus and toolbars. If your program removes the
    menus and toolbars from all instances of Excel, whether all in the
    same collection or not, then we will need to code for that. If it
    doesnt then we are done.

    VB Code:
    1. Private Sub Workbook_Open()
    2.  
    3.     Dim iResp As Integer
    4.     Dim i As Integer
    5.    
    6.     If Workbooks.Count > 1 Then
    7.         If MsgBox("You need to save and close all your other workbooks before continuing!" & _
    8.         vbNewLine & "If you want to Save and Close your other workbooks the click 'Yes'", vbYesNo + vbQuestion, _
    9.         "TheFIDDLER") = vbYes Then
    10.             For i = 1 To Workbooks.Count - 1
    11.                 If Workbooks(i).Name <> "Book1.xls" And Workbooks(i).Name <> "Close all workbooks.xls" Then
    12.                     iResp = MsgBox("Do you want to save '" & Workbooks(i).FullName & "'?", vbQuestion + vbYesNoCancel, "theFIDDLER")
    13.                     If iResp = vbYes Then 'SAVEA AND CLOSE
    14.                         Workbooks(i).Close True
    15.                     ElseIf iResp = vbNo Then 'CLOSE AND DONT SAVE
    16.                         Workbooks(i).Close False
    17.                     Else 'vnCancel 'CLOSE theFIDDLER AND CANCEL
    18.                         Workbooks("Close all workbooks.xls").Close False
    19.                         Exit Sub
    20.                     End If
    21.                 End If
    22.             Next
    23.         Else 'vbNo 'CLOSE theFIDDLER
    24.             Workbooks("Close all workbooks.xls").Close False
    25.         End If
    26.     End If
    27.        
    28. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Lively Member TheFIDDLER's Avatar
    Join Date
    May 2002
    Location
    here and there and far away
    Posts
    126
    Thanks

    I will play with this a bit and see what I can come up with.
    (Fun saturday nights - I'll be glad when this project is finished)

    Workbooks("Close all workbooks.xls").Close False
    Interesting file name - wonder if my users would still click on it...

    I will likely change the code a bit such that if it is Book1 and there is no change, to simply suppress messages and to close it. I was thinking of the best way to check for any user action, and found a neat method - simply to check for the workbook "saved' property. A property of true indicates the user has just opened up excel and has not typed in anything into book1.

    I may take that one more notch, and check for all open workbooks for the saved property, and only prompt for those that require it. The less pop-up messages, the better.
    -----
    #VBA, VB 6 Professional Edition, Office XP Developper. Excel 97, Excel 2000, Excel XP

    I miss my VIC 20.
    Never should have upgraded to my commodore 64. ...

Posting Permissions

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



Click Here to Expand Forum to Full Width