[archive] Launch Excel using ActiveX control

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

You don't have to use an ActiveX control for that. I use the following code with Thin Client. The file I create is a tab-delimited file, but I think a comma delimited file will work just fine.

           add csys-desktop, csys-async, csys-compatibility
               giving csys-flags
           call "c$system" using
                "C:\\Program Files\\Microsoft Office\\Office\\EXCEL.EXE c:\\f
      -         "ile.txt", csys-flags

I you don't use Thin Client, you can omit csys-desktop.

The only thing I don't like with this code, is I have to specify the full path to excel.exe. If I don't do that, excel is not executed.

Andr

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

Click the forum Search button and use Excel keyword for search and an Excel OLE sample will be in the list. This sample shows how to create an excel spreadsheet and place data into cells. Also shows how to format cell borders and colors.
There are two tips I can give you about learning MS Office OLE automation.
First, reference the MSDN Library Office developer documentation at the MSDN website(http://msdn.microsoft.com/library/default.asp). Once there, drill down into Office Solutions Development, then into Microsoft Office Developer. If you happen to have the MSDN Library CD or DVD(comes with VB 6 pro and enterprise), then you can find the Office Developer doc on there as well(office 97/2000 probably).
Second, use the macro recorder in the office products, and then analyze the code it generates, this can be very helpful when trying to figure what properties you need.

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

I have tried this method. My problem is that I can't predict where the user's copy of Excel is located. That's why I wanted to use an Active X control. Thanks for the help!

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

SET ENVIRONMENT "DLL-CONVENTION" TO 1.
*
CALL "Shell32.dll".
CALL "ShellExecuteA" USING
BY VALUE NULL-STRING,
BY REFERENCE OPEN-STRING,
BY REFERENCE W-FILE-PATH,
BY VALUE NULL-STRING,
BY VALUE NULL-STRING,
1
GIVING W-ERROR-RETURN.
*
IF W-ERROR-RETURN
PERFORM BX-ERROR-MESSAGE
END-IF.
*
CANCEL "Shell32.dll".

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

I am new at this forum so the answer was not ready when I sent it the last time.
If you know what excel file to open you can use the following:

Variable declaration:

78 NULL-STRING VALUE 0.
78 OPEN-STRING VALUE "open".
*
01 W-AREA.
03 W-STRING PIC X(50).
03 W-FILE-PATH PIC X(100).
*
*********************************************
Source Code:

SET ENVIRONMENT "DLL-CONVENTION" TO 1.
*
CALL "Shell32.dll".
CALL "ShellExecuteA" USING
BY VALUE NULL-STRING,
BY REFERENCE OPEN-STRING,
BY REFERENCE W-FILE-PATH,
BY VALUE NULL-STRING,
BY VALUE NULL-STRING,
1
GIVING W-ERROR-RETURN.
*
*if w-error-return is smaller than 33 the operation has failed.
*otherwise it succeded.
*
IF W-ERROR-RETURN
DISPLAY MESSAGE BOX "Error"
END-IF.
*
CANCEL "Shell32.dll".


I hope this is what you were looking for.

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

it is not working for me..

I put in w-file-path the of the file and it give a error (w-error-return = 2)

If i do understand it will start excel with w-file-path...

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

First off all, I am impressed of the various techniques being proposed here, I feel it shows that we do have a set of users that now how to tweak the limits and get things to work!

Though, as for this particular case, using Excel with Thin Client, the COM approach is no doubt the most simple and reliable approach.

If you are not familiar with COM instantiation, just have a look at all the fine samples that has already been posted.

One thing to remember when dealing with COM in contradiction to ActiveX, is that a COM object lives on its own, OUTSIDE your application, whereas an ActiveX component integrates like a part of your application. What this means in practise is among other things that you have to take care of your "belongings". With ActiveX components we are used to that they are all contained, so that whatever we do with it, generally, doing a DESTROY on the component itself will clean up everything.

Not so with COM, with COM, if you create/inquires additional component interfaces from the COM factory itself, you will have to make sure you destroy it when you are done, otherwise it will remain by, representing a memory / resource leak.

Because COM application live on their own, you also have to explicitely shut them down before destroying the handle to them, because just destroying the handle is "just closing the door, it doesn't stop the car".

Also, if you have accessed additional interfaces to the COM object that you have not released, you will not be able to shutdown the COM object, because the COM object knows it still has "users". Thus it will stay in memory.

Finally, COM has a beautiful technique in that if you preceed it with a 'D', it becomes DCOM, which is short for Distributed COM, which means that you do not actually have to have it installed on the computer in question, but can run it off on another machine.

Here is a small sample app. I challenge you to make excel.def yourself (hint, look at tab 2 in AxDefgen.exe)
*=================================================================
* ExcelFrmAcu.cbl
* Show how to use MS Excel
*=================================================================
IDENTIFICATION DIVISION.
PROGRAM-ID. EXCELFRMACU.

ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.

COPY "EXCEL.DEF".
.

DATA DIVISION.
WORKING-STORAGE SECTION.
01 EXCEL-OBJECTS.
03 hExcelApp HANDLE OF APPLICATION OF EXCEL.
03 hExcelWkb HANDLE OF WORKBOOK OF EXCEL.
03 hExcelWkss HANDLE OF WORKSHEETS OF EXCEL.
03 hExcelWks HANDLE OF WORKSHEET OF EXCEL.

PROCEDURE DIVISION.
MAIN SECTION.
*Create an Excel instance
CREATE Application OF Excel
*If you are using DCOM replace "myserver" with
*the name of your server and uncomment the next line
*Remember access privileges in the network!!
* SERVER-NAME "myserver"
HANDLE IN hExcelApp.

*Tell Excel to open the spreadsheet c:\\Book1.xls
MODIFY hExcelApp Workbooks::Open(
By Name FileName "C:\\Book1.xls")
GIVING hExcelWkb.

*Inquire the workbook we got for the handle to the
*worksheets
INQUIRE hExcelWkb Worksheets = hExcelWkss.

*Inquire for the current worksheet
INQUIRE hExcelWkss Item(1) = hExcelWks.

*Set cell A1 content to "Cell content"
MODIFY hExcelWks Range("A1")::Value =
"Cell content".

*Let the user see what we do! Of course if we started this
*remotely, doing this would have no effect.
MODIFY hExcelApp @Visible = 1.

*Pause, let the user enjoy the view in three seconds.
ACCEPT OMITTED BEFORE TIME 3000.

*Cleanup act: Destroy the handle to the current worksheet
DESTROY hExcelWks.

*Cleanup act: Destroy the handle to the worksheets collection
DESTROY hExcelWkss.

*Cleanup act: Close the workbook, don't prompt for Save.
MODIFY hExcelWkb @Close(0).

*Cleanup act: Destroy the handle to the work book.
DESTROY hExcelWkb.

*Cleanup act: Shutdown Excel
MODIFY hExcelApp @Quit().

*Cleanup act: Destroy the handle to the Excel Application
DESTROY hExcelApp.

GOBACK.

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

Originally posted by gforseth
Because COM application live on their own, you also have to explicitely shut them down before destroying the handle to them, because just destroying the handle is "just closing the door, it doesn't stop the car".

Hi Gysle,

I my situation I don't want to close Excel, because the user must have the abbility to change the data. When I just destroy the handle and 'leave the car running', does this cause a memory leak? Usually the users closes Excel himself, so I guess this would not be a problem.

I have not experimented with COM or DCOM yet, because I thought it would not work with Thin Client. But I'll give it a try.

Thanks for the sample program.

Andr

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

When trying to mail member "Micr4517" the mail address had fatal errors so I will upload a zip file for all to see instead.
This is a little application that let the user browse to a certain xls file and open it by pushing a button. The application is using the COMM Object of Excel as well as the one that Gisele sent but is not using thin client. Have fun!

It seems that it isn't possible to upload zip files but if you want an example don't hesitate to ask.

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

Mathlener, you can certainly leave excel open for the user by setting the visbile property of the app object to 1, but it's important that you destroy all the object handles, ie, app, workbook, worksheet, range, etc.. As Gisle said, office applications can run as COM Automation servers which run in their own process space, much like an applocation server, so you can make excel visible, destroy the handles, and it will remain open for the user, unless you officially shutdown the automation server with the application quit method. Once the user exits excel themselves, it will also be officially shutdown. Note, that you can run multiple instances of the excel COM server, so it's important that you know when to shutdown excel programmatically or to let the user do it. If you want to see the proof, then run through a test yourself, and check your task manager in windows nt, 2000 or xp. You will see that if you make excel visibile and then destroy all excel handles, that excel will remain open, but once you manually close it, the excel process will be gone.

[Migrated content. Thread originally posted on 05 March 2003]

I am creating comma-delimited files in several programs. After creating a file, I would like to launch Excel, open and import the file. I think I can do this with the Excel ActiveX control. Can anyone provide an example or some guidance.

As a more general question, where can I find help/documentation for all the Microsoft ActiveX controls? Their purpose, function, use, variables, etc. This is really a new area for me and would like to make greater use of them but lack basic reference material and experience.

Well said DanM.

Mathlener, as DanM says, just make it visible, don't call the Quit method, and destroy all other references and there should be no memory leak.

Additionally, to all of you. Do look around in the other threads here as well. This Excel listing here has been posted in other threads, with other comments. And if you are looking to use MS Word, there has been posted an extensive mailmerge demo as well.