PDA

View Full Version : Problem launching Excel using code



muttleee
10-24-2007, 03:11 AM
Hi all,

I thought I'd seen the last of having to integrate Word and Excel reports with Lotus Notes but sadly not!

Here's the problem. We use an electronic document and record management system (EDRMS) called TRIM that basically forces users to save their Word and Excel documents in a central storage facility rather than on their own hard drives.

Some of our Lotus Notes applications have functions for exporting data in Word or Excel format and while TRIM always catches Word documents created in this way, it doesn't do the same for Excel spreadsheets. I've been asked to find out why since I wrote the export facilities in the first place (although I found the Excel one on bluestream.org at the time).

I don't know if this is an Excel problem, a TRIM problem or a Notes problem but I thought it was worth asking if anyone here had any ideas. I'll post the same question on the notes.net forum and see if that helps too. Here's the code that launches Excel:

Set xl = CreateObject("Excel.application")
Set xlWbk = xl.Workbooks.Add
Set xlSheet = xlWbk.Worksheets(1)
Call xlSheet.Activate
That looks like pretty standard code to me so I'd be surprised if there's some other way of doing it that would make a difference in this situation.

Is there something I'm missing? Excel opens fine and saves fine to the local machine. TRIM should intercept any save of a new Office document and it does, when an Excel spreadsheet is created manually. It just doesn't seem to catch it when Excel is launched programatically.

Any thoughts or suggestions gratefully appreciated!

lucas
10-24-2007, 05:25 AM
I'm just guessing here but...

TRIM should intercept any save of a new Office document and it does, when an Excel spreadsheet is created manually. It just doesn't seem to catch it when Excel is launched programatically.

I don't see where you have saved the excel file with your code in post#1

muttleee
10-24-2007, 06:29 AM
Thanks for your reply. Apologies...I should have said that the code only creates and populates the Excel document. After it's created, the user saves it manually in the usual way, by clicking the Save icon or selecting File/Save.

For any spreadsheet that's manually created, this is no problem and the document management software kicks in fine when it's first saved. For a spreadsheet that's created programatically in VBA however, clicking Save or File/Save just displays the normal dialog box asking you to save to My Documents etc. It doesn't trigger the TRIM software at all - even though it's supposed to catch any occasion when a Word or Excel document is saved.

I'm just trying to work out why TRIM works in one case and not the other. Could there be something happening behind the scenes that makes it think that an automatically created Excel spreadsheet isn't actually an Excel file at all?

Hope this helps clarify the problem.

Bob Phillips
10-24-2007, 06:49 AM
How does TRIM integrate into Word/Excel, that is what/how does it intercept the save?

jwise
10-24-2007, 07:02 AM
I think that the code must use "event code" to get to Trim. It must be two levels deep, i.e. the first level must install the second which does the save on exit. I would go into a "manual spreadsheet" and do ALT F11 to get to VBE. Then Tools | References and look for strange libraries. You should find something with a name that indicates "Trim" or whatever is actually the culprit.

This does not solve the problem, however. It does tell you how the "manual" code works. When Excel is "programmatically" invoked, this install is not being done. So you probably need to do something that causes this code to be installed.

I hope this gives you some idea of how to isolate the code.

JKwan
10-24-2007, 07:02 AM
Try looking at wether the ADDIN of TRIM is loaded or not. I bet you that TRIM addin is not loaded. This is what I discover, my Documnent Managemnet addin is not loaded when I programmatically open Excel. A matter of fact, none of my addins are loaded.

Bob Phillips
10-24-2007, 07:19 AM
If it is an addin as suggested, they are not loaded via automation, so you have to manually load it after opening the application



xl.Addins("TRIM").Installed = True


Why don't you use the existing Excel app rather than start a new one?

muttleee
10-24-2007, 09:26 AM
Thanks for all your replies.

TRIM is a proprietary bit of software that we bought in so I don't know the ins and outs of how it actually works. There is a little application called TRIMwatcher that runs in the background all the time and automatically traps any saves of Word/Excel documents and directs the user to save them on a shared drive rather than on their own machine.

I've looked at the references and add-ins issues and this is what I've found:

When I look at the references of a manually created .xls file or a programatically created one, they look identical. Only the following four out of the hundreds listed are ticked/checked in both cases:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library

There are others related to TRIM in the list but none of them is checked in either case, ie:

TRIM Outlook COM AddIn
TRIM Queue Notes
TRIM SDK Type Library
TRIM TSJTEM Type Library
TRIMwatcher 1.0 Type Library
TRIMworkflow 1.0 Type Library
TrimWorkgroup 1.0 Type Library

If I check the TRIM ones manually, it doesn't make any difference. I don't even know if it should but I thought it was worth a try...

When I look at the AddIns menu option, there's nothing listed there at all. I notice however that there is a file called 'Addin Set for [CompanyName]' listed under references and it's in a folder named after the people who wrote TRIM. I tried checking this manually but again, it didn't seem to make a difference. Examining this folder in Windows Explorer, it contains two .dlls and a .tlb file.

I tried adding a line of code that might install this automatically before opening a new Excel doc:

Set xl = CreateObject("Excel.application")
xl.Addins("Addin Set for CompanyName").Installed = True

but this threw an error.

As for why I'm using a new application instead of reusing an existing one, that's the way it was done in the code I found when I did this originally and I'm afraid I just stuck with it because it appeared to work.

I don't know if any of this makes much sense to anyone but if it does, any further insight would be much appreciated. :)

jwise
10-24-2007, 09:57 AM
You are beyond my understanding now, but I believe you are on the correct trail. JKwan's suggestion that it is probably an addin is also my belief now. I think you should be able to find this addin, however. I would continue looking in a "manually started" spreadsheet. Then use XLD's code to cause it to install in your dynamically invoked code.

Good luck

muttleee
10-25-2007, 06:18 AM
One more thing that might help point to the solution to this...when I do Alt+F11 and look at the references window in a manually created Excel spreadsheet, I see references to both VBAProject and a ContextAddIn file called TSJXL9.XLA in the VBAProject window.

When I look at the VBA Project window in a programatically created document, there is only the reference to VBAProject.

How can I add force the programatically created document to reference the ContextAddIn? If I try something simple like this (as mentioned above):

Set xl = CreateObject("Excel.application")
xl.Addins("TSJXL9.XLA").Installed = True

it just throws an error. Is there a way to force Excel to use this add in when it is launched via code? Is this syntax right? Do I need to reference its full file path?

Thanks again for any prodding in the right direction. :)

lucas
10-25-2007, 07:20 AM
Do I need to reference its full file path?

It's possible that if the addin was installed by an installer that it wasn't placed in the Excel default directory for addins so this is very possibly the problem....search your hard drive for the xla to establish the path.

Bob Phillips
10-25-2007, 07:21 AM
The registry would be quicker IMO.

muttleee
10-25-2007, 08:37 AM
I copied the ContextAddIn file into the default folder (C\Documents and Settings\myname\Application Data\Microsoft\AddIns) and the next time I launched Excel programatically and tried to save it, TRIM kicked in exactly like it's supposed to. Hurrah!

However...the next time I tried it, TRIM didn't do anything. I re-added the AddIn (by browsing to it again and overwriting the one that was already there) and suddenly everything worked again. It seems it only works when the AddIn is added for the first time, for some reason.

At least I feel like I'm getting somewhere because now I know for sure that the AddIn is indeed the source of the problem. I assume I just need to get it to install/run every time Excel is started programatically?

Thanks to everyone for all your help so far!

lucas
10-25-2007, 09:05 AM
I may be wrong but the problem may be that you should not move it from it's installed locatin.....might try what you were doing in thread #10 and add the path to where it is installed by the installer.....

jwise
10-25-2007, 09:47 AM
I like this kind of problem, so here goes:

The dynamically invoked Excel will always need XLD's code to load the add-in. Normal Excel would only need the add-in loaded once. Your install did that but from a non-standard library.

If you move the add-in, you may create problems. If you copy it to the standard location then you prevent these problems, but you must remember to recopy every time you do maintenance. This can be a real pain. It would not surprise me that there are some "reference libraries" that must be "dynamically added" too. The reason is that it is common for developers to "hook" code which then calls other code from some location. At least a lot of old mainframe code does this kind of stuff.

Good luck.