PDA

View Full Version : WorkbookOpen & double-clicked .XLS file from explorer...



cheapdrunk
03-12-2006, 12:40 AM
Hi all,

I hope I'm not asking something really obvious here but....

I'm using the 'WorkbookOpen' event in VBA. This is saved in a class module in a workbook that is loaded during startup with Excel (v2003 Pro SP2) via the XLSTART directory. This macro runs fine. If I start Excel, the workbook loads, my macro recognises the WorkbookOpen event and I get a splash screen to say that it's running. What it then does is sits and waits until I open particular .XLS sheets and modifies them. This is all by design and is working as it should be.

BUT - If I attempt to start Excel by double-clicking on a .XLS sheet from within Explorer (for example), the first workbook is loaded (with my macro and the Workbookopen event), but NOT the .XLS sheet I just double clicked on! I then have to go back and re-double-click on it so that Excel will load it.

For clarification: Excel starts fine and will load a double clicked XLS sheet from within explorer if I disable the Workbookopen event.

If I start Excel from a normal shortcut, my macro loads, I get a splash screen and all is well. I can then load my selected workbook from [file][open], etc... with no problems and the macro works as it should.

I have tried making the VBA project an Addin. The only difference is that Excel then loads a new (blank) worksheet after loading Excel and my macro... but still will not open a double-clicked workbook.

It would appear that WorkbookOpen is not handing off control to Excel for any further workbooks that are opened at the same time as it is. Possibly not passing along environment variables that show Excel which workbook to load next? i.e. %1 %2 (for the DOS fans out there...)

Has anyone else had this same trouble with WorkbookOpen?

Thanks!

Dan http://www.mrexcel.com/board2/images/smiles/icon_smile.gif

Killian
03-13-2006, 02:26 PM
Hi Dan and welcome to VBAX :hi:

I have this link (http://www.jkp-ads.com/Articles/StartupProblems.asp) bookmarked for times like this. Your problem may be addressed by point 2.
Let us know how you get on :thumb

cheapdrunk
03-14-2006, 03:25 AM
Hi Killian and thanks for your welcome.

No good - I examined the suggestions shown but none seem to apply to my specific problem.

I tried the 'ignore other applications' as that was an option I'd never noticed before but no change.

Also tried the 'regserver' option but still no change. Excel IS correctly associated with .XLS files and will open them but not if my macro with WorkbookOpen is loaded at the same time. If the macro is loaded first, and then I select a .XLS file to load, all's well...

I have searched for about a week on the 'net for this particular problem prior to posting it on this (and 2 other) forums but no-one has ever seen it before.

Any advice you may have in regard to this issue would be much appreciated.

May I suggest you try this yourself and see if your machine responds the same way?

Thanks!

Dan :)

Killian
03-14-2006, 01:35 PM
OK, well what you're describing is not normal behaviour. :think:
I suggest first you save a blank workbook as an xla with something simple likeMsgBox "The AddIn loaded."in the workbook open event. Drop that in the XLSTART folder and test.
If that works there presumably is something in your addin code that is causing this. I can't think off-hand what that might be, since the code appears to complete without error. We'd have to have a look.

cheapdrunk
03-17-2006, 03:28 PM
Hi Killian,

sorry for the late reply - nowdays, I really only get a chance to do any programming on the weekends.... :(

okies.... I made a basic WorkbookOpen event that only had a msgbox entry in it. I kept it all as a VBA project and placed a shortcut to the macro .XLS in XLSTART. I then double-clicked on another .XLS from explorer - and it all worked! The .XLS with the WorkbookOpen event was loaded first (I got a msgbox to say that the workbook was opened), then my double-clicked file also opened (with another msgbox of course).

SO - I started disabling bits of code in my original (problem) macro and found that this particular problem does not occur if MsgBox is used. I have actually been using a splash screen which is a UserForm with an Application.OnTime entry to close it after a couple of seconds. If a UserForm is called and then closed, it appears that this is where Excel is loosing track of the double-clicked file and therefore does not load it.

Any thoughts? I would really like to keep my very pretty splash screen (UserForm) :*)

Dan :giggle

Killian
03-18-2006, 05:04 AM
Hi Dan,

I think there's clearly a problem with the splash screen method - sounds like the form isn't unloading, leaving Excel waiting to complete the addin code before it moves on to opening the file.
There are a few ways to implement splash screens. It's easy enough to use the form to run long initialization code and unload on completion - which is what spalsh screens are for - but simulating time delays can often cause problems.
One way is just to have a loop counting up to a massive number then unloading the form. Another is to get the time when it first loads and loop a test until you've passed your startup time + preset duration.
Here's a simple example form codeDim starttime As Single
'how many seconds delay
Const duration As Single = 5

Private Sub UserForm_Initialize()
starttime = Timer
End Sub

Private Sub UserForm_Activate()
'don't let the user do anything
Me.Enabled = False

Do
DoEvents
Loop Until Timer > starttime + duration
Unload Me
End Sub

cheapdrunk
03-18-2006, 03:40 PM
Hi Killian,

well, looks like we're getting closer to a solution.

After your suggestion about timed splash screens being a problem, I disabled the Application.Ontime statement (which I was using to close the UserForm) and just opted for a Unload.UserForm straight after UserForm.Show (just to test). Of course, the form flashes up very briefly and disappears BUT the second worksheet DOES load! :)

So, I included your example script to test your method of a timed display. No luck. This script works fine and closes the UserForm after 5 seconds but still will not allow a second worksheet to be loaded.

What do you think?

Dan :)

Killian
03-19-2006, 05:42 PM
OK, that's fine, you just need to comment out the do events line. That means the form will probably need to be repainted.Private Sub UserForm_Activate()
'don't let the user do anything
Me.Enabled = False
Me.Repaint
Do
'DoEvents
'do nothing
Loop Until Timer > starttime + duration
Unload Me
End Sub

cheapdrunk
03-20-2006, 02:13 AM
Hi Killian,

woohoo!

yes, that fixed it! :clap:

It now shows the splash screen, waits for the specified time, then loads the double-clicked sheet.

Thanks for your assistance with this one, Killian. You are the only person that has had any idea as to what the problem might be. I'm buying the first round!

Dan :) :beerchug:

Killian
03-20-2006, 04:50 PM
Excellent news. :peace: Especially about the first round. :beerchug: