-
Apr 9th, 2004, 06:44 PM
#1
Thread Starter
Lively Member
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. ...
-
Apr 9th, 2004, 10:22 PM
#2
Something like this?
VB Code:
Private Sub Workbook_Open()
Dim i As Integer
If Workbooks.Count > 1 Then
If MsgBox("You need to save and close all your other workbooks before continuing!" & _
vbNewLine & "If you want to Save and Close your other workbooks the click 'Yes'", vbYesNo + vbQuestion, _
"TheFIDDLER") = vbYes Then
For i = 1 To Workbooks.Count - 1
Workbooks(i).Close True
Next
Else 'vbNo 'Close theFIDDLER
Workbooks(Workbooks.Count).Close False
End If
End If
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Apr 10th, 2004, 12:45 PM
#3
Thread Starter
Lively Member
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. ...
-
Apr 10th, 2004, 01:31 PM
#4
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:
Private Sub Workbook_Open()
Dim iResp As Integer
Dim i As Integer
If Workbooks.Count > 1 Then
If MsgBox("You need to save and close all your other workbooks before continuing!" & _
vbNewLine & "If you want to Save and Close your other workbooks the click 'Yes'", vbYesNo + vbQuestion, _
"TheFIDDLER") = vbYes Then
For i = 1 To Workbooks.Count - 1
If Workbooks(i).Name <> "Book1.xls" And Workbooks(i).Name <> "Close all workbooks.xls" Then
iResp = MsgBox("Do you want to save '" & Workbooks(i).FullName & "'?", vbQuestion + vbYesNoCancel, "theFIDDLER")
If iResp = vbYes Then 'SAVEA AND CLOSE
Workbooks(i).Close True
ElseIf iResp = vbNo Then 'CLOSE AND DONT SAVE
Workbooks(i).Close False
Else 'vnCancel 'CLOSE theFIDDLER AND CANCEL
Workbooks("Close all workbooks.xls").Close False
Exit Sub
End If
End If
Next
Else 'vbNo 'CLOSE theFIDDLER
Workbooks("Close all workbooks.xls").Close False
End If
End If
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Apr 10th, 2004, 08:33 PM
#5
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|