Solved: Open MS Excel session

[d4vem]

Can anyone help?

What I have have is a bespoke application written in MS Excel with customized menus and icons etc, so when the users open the short cut to the file they are not aware that the application is actualy an excel file which works fine.

Here's my problem when this file is active if someone opens a file via MS Explorer it opens in the current active Excel session (ie this app). Is there anyway to prevent a file being opened in this existing open app and open in a new MS excel session with the standard excel layout ?

Thankyou in advance, any help would be appriecated

[Zack Barresse]

Hi there,

What you could do is create your menus/toolbars in this workbook via code and then just have a couple of events in your ThisWorkbook module for Activate and Deactivate, so when any other workbook is activated (which includes opening) the toolbars will be reverted back to "normal".

[d4vem]

Thanks for the reply.

I have thought about using the activate/deactive file method. However I have an issue in the custom menus are created by a user name and password entered into a sign on userform on opening the file and I am reluctant to have the users signing in each time the file is activated thereafter.

The other issue that I may have is that if the user opens the customized file then opens a second file and closes the session by closing the window bar close button it will ask whether each workbook open in that session needs to be saved which is another thing that I am trying to avoid.

Thanks again for your interest

[Zack Barresse]

Have the sign in on the workbook open event, not in the toolbar code, then with the activate/deactivate you can have it check the login status and either display or not display.

[Insomniac]

if someone opens a file via MS Explorer it opens in the current active Excel session (ie this app). Is there anyway to prevent a file being opened in this existing open app and open in a new MS excel session with the standard excel layout ?

Another approach would be to use Application.IgnoreRemoteRequests = False.
This will prevent files opening from explorer.
Be aware that excel stores this setting between sessions so be carfull to reset it.

A simple code would be:Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.IgnoreRemoteRequests = False
End Sub
Private Sub Workbook_Open()
Application.IgnoreRemoteRequests = True
End Sub

If you needed a workbook to always run in its own application with self regulating code (in case it was opened after other files), something like:Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'important to reset this
Application.IgnoreRemoteRequests = False
End Sub

Private Sub Workbook_Open()
'need to use ontime to allow xl to initialise fully
Application.OnTime Now, "ThisWorkbook.OnlyOneOfMe"
End Sub

Private Sub OnlyOneOfMe()
Dim XlApp As Excel.Application
On Error GoTo BAD
With Application
If Me.ReadOnly Or .Workbooks.Count > 1 Then
Me.ChangeFileAccess Mode:=xlReadOnly
Set XlApp = New Excel.Application
XlApp.Visible = True
XlApp.Workbooks.Open (Me.FullName)
GoTo BAD
Else
'stop opening from explorer (but not from excel)
.IgnoreRemoteRequests = True
End If
Exit Sub
End With
BAD: If Err Then MsgBox Err.Description, vbCritical, "ERROR"
Set XlApp = Nothing
Me.Close False
End Sub

[d4vem]

That worked a treat. The only issue is I had to create a seperate file to reset excel in the event that during testing the file containing the code failed. Not sure if this can be trapped in your last version of code?:dunno