Running a VBA form as a seperate process from Excel or workaround

[sugarflux]

Hi guys

Long shot but hoping there might be an API genius out there somewhere or an imaginative vba-er with a workaround!

I'm writing an app in vba (Excel 2000) running purely off of userforms (i.e. no excel functionality whatsoever). Would have written it in VB but don't have access to this.

So. Here's the clever bit. I'm getting rid of the excel window by making it invisible. I'm using an API call (DrawMenuBar from user32.dll) to create my own button on the explorer taskbar, assigning my own icon, minimise, maximise etc so the app looks like an executable.

The problem: App runs fine disguised as it's own program until another .xls file is opened. Then it's obviously sharing a process (excel.exe) with another workbook. Even though i can hide the workbook window and trap the close process so it doesn't ask to save, when the user chooses to exit excel, my app is closing.

I've sorted if a workbook is already open when the app opens by creating another process with the following:
Private Sub Workbook_Open()
If Workbooks.Count > 1 Then
Dim xl As New Excel.Application
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ActiveWorkbook.FullName, , True
Set xl = Nothing
ActiveWorkbook.Close False
Else:
Windows("myApp.xls").Visible = False
Application.Visible = False
frmTM.Show False
End If
End Sub

If this doesn't make sense please let me know - i've been racking my brains trying to find a way round this but so far can't come up with anything. Any workaround considered!
Thanks guys,

sugarflux

 

[kjv1611]

I'm not sure of the real solution here, but I think your problem is when someone opens a new workbook within the same Excel application. That would explain them closing one workbook, and your application closing as well. If they open a new instance of Excel, then when they close it, it will not close your application - but if they open a workbook within the same instance of Excel, then it will definitely close your workbook and application.

At least a temporary workaround would be to simply let anyone know that is using this app to open a second instance of Excel for any other worksheets as apposed to opening them directly. Basically, the way it works is that if you have Excel open, and just go and double-click another Excel file, it will open it within the same instance of the application unless you instead open a new instance of the application, and then open the file.

Seems like not the best work around, but it might at least fix your immediate problem while you try to find a better solution.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Rofeu]

HI,


can you not always have the workbook opened in a seperate session of Excel and hide the application.

Then only the userform should show and the user wouldn't be able to close the session directly.

Otherwise, you could use the BeforeClose event of your workbook to prevent it being closed when the user tries to close the app.

Cheers,

Roel

 

[sugarflux]

Hi
Thanks to both of you..

Kjv - You got the problem right and worded it much better than i did! Unfortunatley i need something a bit more sturdy than letting people know to open the workbook in a seperate instance - i need to force this to happen programmatically.

Rofeu - this is exactly what i'm doing at the moment - opening the app in a seperate session of excel so any workbooks that are already opened are completely seperate.

My problem occurs when the app is already open and the user opens another workbook. Because i can't force other workbooks to open in a seperate session it is merging with my app and then when they close down Excel, it takes my app with it!

Anyone got any further ideas? Thanks in advance... keep em coming!

sugarflux

 

[Rofeu]

ok, you mean the user opens another workbook from, say explorer, the workbook automatically opens in the same session your app is in?

But...if your application is hidden, they won't see it...
(not a 100% sure about this behaviour).


2 ways I can think of:

1. when the user closes the session of Excel that your app is in, the workbook_beforeclose event will fire before the session is terminated. You could intercept the closing and just have all the workbooks, except your app closed.

2. use application events to capture another workbook being opened and use the same code you use to redirect your app to another session. (look in the excel help for application events to find a description of how to set these up).

Hope this helps.

Cheers,

Roel

 

[sugarflux]

Hi Rofeu

Option 2 sounds exactly what i'm looking for. In VBA help i've found the topic 'Using events with the Application Object' which seems to be the closest to what i need. However, it would seem that the class needs to be initialised from code as aposed to trapping the user opening the file from explorer.

Are you able to expand on how i might capture the open event of another workbook please?

Thanks again

sugarflux

 

[kjv1611]

sugarflux,

What about just moving the code from your application for opening in a new session out to the Open event within PERSONAL.XLS?

Wouldn't that do it? The reason I say move instead of copy is that if you have it still in your application, I'd think it'd be doing the same work twice, which would be overkill..

--

"If to err is human, then I must be some kind of human!" -Me

 

[Rofeu]

Hi,

you only need to initialise it once. So put this piece of code into your workbook_open event in your app:

Sub InitializeApp()
    Set X.App = Application
End Sub

It will then fire the App_WorkbookOpen event whenever another workbook is opened from that session.

Look in the help for 'Application Object Events' to get a list of all the events available at application level.

Cheers,

Roel

 

[Rofeu]

kjv1611,

what will happen when another user opens the app when the code is in his PERSONAL.XLS?

Cheers,

Roel

 

[kjv1611]

What I am thinking (don't know, haven't tested) is that if the code for opening a separate session were located in the App_WorkbookOpen event of the PERSONAL.XLS file, then any file then opened would automatically open in a separate session. That would eliminate the localized code to the one particular worksheet. But, the only thing would be whether or not this is desired for other sheets or not.

If your method, Rofeu, works 100% correctly, then I think that'd be the correct way to go. That way, the user could still open multiple workbooks in one session, just not the session where the application is located. I am sure that is what is desired, rather than forcing a new session every time. It was just all I thought of, b/c I did not think that same event would fire from a localized workbook as apposed to the PWERSONAL.XLS.

--

"If to err is human, then I must be some kind of human!" -Me

 

[sugarflux]

Sorry Roel.

I've created a class with the code:

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

End Sub

I have my opening code in the 'ThisWorkbook' object - workbook_open()

Whereabouts do i need to put:

Sub InitializeApp()
Set X.App = Application
End Sub

Thanks once again
sugarflux

 

[Rofeu]

Well, if the code would reside in a localized workbook, it wouldn't fire when another user opens the app, hence the diversion to another session would not occur. Therefore, the code would need to be in sugarflux' app.

Now, whenever the app is opened, the application events for that session of the application will be enabled and opening another workbook in that session will fire the App_WorkbookOpen event.

Best way will be test if another session of Excel is already open and have the opened workbook reopen itself in that session, but I must admit that I'm not sure how to go about that.

Here's some API that I found somewhere (don't remember where) that'll return the handle of Excel (returns 0 if no session is open):

Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
                    ByVal lpWindowName As Long) As Long

If I remember correctly,

Dim ExcelProcess as Long
ExcelProcess = FindWindow("EXCEL.EXE", 0)

returns the handle of the window, which you can use with GetObject to get the session. I'm uncertain how it handles multiple session of the same application though.

Try dumping the entire API call into google and browse around. Maybe there's something there about this.

Cheers,

Roel

 

[Rofeu]

Ah, my bad, you can put that sub in any module and then call it from your workbook_open event.

Cheers,

Roel

 

[sugarflux]

Wow. Now i'm confused.

Sorry - be patient with me Roel!
The above API i've sorted so it's all ready for the workbook open bits.

I have a class module with:

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Class module event: Workbook Opened"
End Sub

I have a module with:

Sub InitializeApp()
Set X.App = Application
MsgBox "InitializeApp event fired"
End Sub

and my Thisworkbook object with:
Private Sub Workbook_Open()
InitializeApp
MsgBox "Workbook_open event fired"
end sub

When my app is running and i open a .xls from explorer i get no messages and the other workbook doesn't open...
Where have i gone wrong?

sugarflux

 

[Rofeu]

You haven't instantiated the class yet:

In your module, on the top, put in:

Dim X As New EventClassModule

Name your Class Module 'EventClassModule'

Cheers,

Roel