Excel XP - xlstart

Hi All,

Well I've finally upgraded from good old Excel 97 to Excel XP.

Must say it looks and feels very pretty

Slight problem though, I have placed a file in the xlstart directory that has code in the "This Workbook" Module that's meant to run with Workbook_Open. Problem is that it dosen't run.

I set the security level to low but that didn't work. So as per the recommendations in help I set it to medium which is then supposed to prompt me to allow the code to run, again that didn't work. All that happens is that Excel locks up when I launch it ??

Anyone got any ideas? I could spend hours trying to figure it out but would appreciate any pointers from people who may have experienced the same problems.

P.S I haven't played around with digital signatures yet.

Thanks

AJW

OK, quick update.

I've tried everything I can think of (including digital signing) to get code to trigger with the Workbook_Open event in Excel XP but to no avail.

If anyone has any idea please let me know ASAP.

Point of clarrification:

The Workbook_open event does not work when the file is loaded through the xlstart directory.

C:\Program Files\Microsoft Office\Office10\XLStart

It does however work if I manually load the file. The problem is that I need it to work through XLStart as it triggers a series of other addins to be loaded and managed.

My dumb users gripe about clicking too many buttons let alone having to manually load a file :~(

Thanks

AJW

Dave,

I need to distribute the addin to a wide user group, if I used the Personal.xls file I might overwrite their own existing code.

"I guess the other thing to check is whether Events are disabled?"

How do I do this ??

"All that happens is that Excel locks up when I launch it ?? "

I've overcome this part of the problem by recoding the Workbook_Open and Workbook_Close events but it still wont trigger.

"It sounds like a re-installation might be in order."

No go, work machine and my IT department are already irrate with me and my project. I've been banned from the software directory on the server.

"Are you sure it's not the code in the Workbook that is causing lock-up? "

100% OK when manually loaded, problem resides with load through XLStart.

Thanks again

AJW

mmmmm................... I'll look into it.

TA

AJW

No it wont work. It's not manually installed as an Addin through the Tools>Addins Menu.

It's an *.xls workbook with the IsAddin property set to True and VBE code password protected.

I didn't want a workbook with sheets hidden or very hidden as someone could get into the worksheet databases it contains, setting the workbook property to addin makes it harder as they need to get into it through the VBE. The databases are still available to lookup from other workbooks.

This file is placed in the xlstart directory and in XL97 works fine with the Workbook_Open event.

For some reason XP is not allowing the Workbook_Open to trigger through the XLStart directory. I've messed with all the security settings and still can't get it to happen.

AJW

"This would make it an *.xla not *.xls Go to Tools>Add-ins and browse for it."

Nope. It's an *.xls workbook with the IsAddin property set to True. By setting the IsAddin property for ThisWorkbook to True it becomes an *.xls ADDIN. Not an *.xla Addin which is the usual.

It has all the features of an *.xla Addin in that you can not see any of the worksheets even though it has an *.xls extension. The sheets are not "hidden" like through the Format > Sheet > Hide or the Window > Hide commands, they are hidden like the worksheets in an *.xla Addin.

"Who suggested "a workbook with sheets hidden or very hidden" ?"

No one in particular, I've seen it done this way and it did not suit my purposes.

"Have you an *.xls or *.xla in there."

C:\Program Files\Microsoft Office\Office10\XLStart\Load MM.xls

It is however a xls file with the properties set to Addin.

" If the former try placing it in: C:Documents and SettingsOwnerApplication DataMicrosoftExcelXLSTART"

I don't have this path available. the only XLStart I have is as per the path above.

"You also haven't answered my question "

Which One?

AJW

Hi Dave,

Sorry to take so long to respond, I’ve been a little busy trying to get XLXP working.

I’ll start at the beginning and hopefully make it a little less confusing.

I have a file called MMLoad.xls which has the ISAddin property found in the VBE under ThisWorkbook set to True. This file is stored in the XLStart directory and loads when Excel is launched. The XLStart directory also normally contains the personal.xls workbook that is used to store peoples custom Macro’s, Personal.xls is also loaded at launch time and can be viewed through Windows > Unhide. MMLoad.xls however cannot be viewed through Windows > Unhide as it is seen by Excel as being an Addin even though it has an .xls extension.

MMLoad.xls controls the loading and unloading of my main code repository (MM.xls), MMLoad.xls also audits the users machine for username, operating system 95 / 98 / 2000 / XP / NT, Excel version 97 / 2000 / XP, connection status LAN / Dial Up / Stand Alone / ADSL / ISDN etc. When these parameters are defined a VBS (Visual Basic Script) is run external to Excel that brings the MM.xls addin into the XLStart directory and opens it with a password. MM.xls is also a Workbook with the IsAddin property set to true, it is also password protected which essentiall makes it a password protected addin that can only be accessed through MMLoad.xls

MMLoad.xls also audits the local C drive for the latest version of a package of Templates and Addins that I have developed and if a later version exists on the network it will download it external to Excel through a VBS.

All this as confusing as it sounds is in place due to security requirements. So to answer your question simply the *.xls Addin is loaded at launch time.

The problem I was having was that at load / launch time the code contained in ThisWorkbook Workbook_Open would not trigger. I tried many various combinations of coding from having the code I needed to run in Worbook_Open through to calling it from a separate module with Application.Run “My Macro”. Nothing was working, I has tried all various levels of security. I checked my XLStart folder location and discovered there was another one under My Documents folder (just like you said), I put MMLoad.xls in there and still the code would not trigger at startup, it would however trigger if I manually launched the file after Excel was running but still would not run at load time.

It dawned on me that I had had a similar problem before when running a macro and jumping to another before the first one had finished. I solved that problem by using OnTimeNow so I recoded my Workbook_Open code to Application.OnTime Now + TimeValue("00:00:01"), "My Macro". Hey presto it worked, I can only assume that having the code running while Excel was opening caused some kind of conflict that would open freeze up Excel. By using the delay of OnTime Now I was able to resolve the conflict by giving XLXP time to launch completely.

I am now faced with a new problem however in that the location for the XLStart folder is variable based on user name i.e C:\My Documents\watermaj\application\Microsoft\xlstart with watermaj being the variable for every different machine. I’m 90% of the way to resolving this by ripping the username from the registry as a variable and passing it to a hardcoded string.

Anyway I hope that serves to clarify my complex security and Addin loading process a little. If anyone else is having similar problems hopefully they can learn from my experience.

AJW