[archive] Grid - Drag&Drop - Copy to clipboard

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

I don't think this is possible with the Acucorp grid, but there are many activex controls that do it.

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

If you want it in an Excel spreadsheet, I don't understand why you want to involve the clipboard? Use COM and transfer it directly.

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

Hi Gisle

How would you do it with COM, do you have an example ?

Regards
Steen

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

Hi Gisle

How would you do it with COM, do you have an example ?

Regards
Steen

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

There are two ways to transfer data from ACUCOBOL-GT to Excel using COM. You can either transfer by cell:


       IDENTIFICATION               DIVISION.
       PROGRAM-ID.                  ExcelSimple.
      *====================
      *
      * Copyright (c) 1996-2006 by Acucorp, Inc.  Users of ACUCOBOL
      * may freely modify and redistribute this program.
      *
      * The purpose of this application is to show the absolute minimum
      * required to access a cell in a Microsoft Excel spreadsheet.
      *
       ENVIRONMENT DIVISION.
       CONFIGURATION                SECTION.
       SPECIAL-NAMES.
           COPY    "EXCEL.def".
                   .
       DATA        DIVISION.
       WORKING-STORAGE              SECTION.
       77  olExcel                  HANDLE OF APPLICATION.
       77  olWrkBk                  HANDLE OF WORKBOOK.
       77  olWrkSh                  HANDLE OF WORKSHEET.
       77  MY-STRING                PIC X(80).
       PROCEDURE DIVISION.
       Main.
           CREATE  Application      OF Excel
                   HANDLE           IN olExcel.
      *This is for training purposes only. If you don't want to see
      *Excel, remove this line.
           MODIFY  olExcel          @Visible = 1.
           MODIFY  olExcel          Workbooks::Add()
                   GIVING           olWrkBk.
           INQUIRE olWrkBk          Worksheets::Item(1) IN
                   olWrkSh.
      *Set the value of a cell.
           MODIFY  olWrkSh          Range("A1")::Value = "Last Name".
      *Get the value of a cell.
           INQUIRE olWrkSh          Range("A1")::Value IN MY-STRING.
      *Force a recalculation of a sheet
           MODIFY  olWrkSh          Range("A1")::Calculate().
           DESTROY olWrkSh.
      *Enforce a close without save, or Excel will prompt you.
           MODIFY  olWrkBk          @Close(BY NAME SaveChanges 0).
           DESTROY olWrkBk.
           MODIFY  olExcel          Quit().
      *This next line is very important, otherwise Excel will stick in
      *memory.
           DESTROY olExcel.
           GOBACK.


Or you can transfer a table (more efficient by large numbers of data) see next message.

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

This example depends on the presence of a program (rand.cbl) provided in the sample folder of the installation of your ACUCOBOL-GT version. It is not mandatory however, you can just put your own values in there.

This example uses what is known as SafeArray to pass data from ACUCOBOL-GT to Excel using COM.


       IDENTIFICATION DIVISION.
       PROGRAM-ID. ExcelArray.
      *====================
      *
      * Copyright (c) 1996-2006 by Acucorp, Inc.  Users of ACUCOBOL
      * may freely modify and redistribute this program.
      *
      * The purpose of this application is to show how to transfer a
      * whole array at a time to an Excel spreadsheet. This would be
      * very preferrable if large amounts of data are to be transferred
      * as a cell by cell transfer is extremely slow and thus only
      * useful for cases where the number of cells are few.
      *
       ENVIRONMENT DIVISION.
       CONFIGURATION                SECTION.
       SPECIAL-NAMES.
           COPY    "EXCEL.def".
                   .
       DATA        DIVISION.
       WORKING-STORAGE              SECTION.
       77  olExcel                  HANDLE OF APPLICATION.
       77  olWrkBk                  HANDLE OF WORKBOOK.
       77  olWrkSh                  HANDLE OF WORKSHEET.
       01  MyTable.
           03 FILLER                OCCURS 100.
              05 MyItem             USAGE HANDLE OF VARIANT OCCURS 3.
       77  I                        PIC 9(3).
       77  J                        PIC 9(3).
       77  MyData                   PIC X(80).
       01  RND-VALUE                PIC 9(12)V9(5).
       PROCEDURE DIVISION.
       Main.
           PERFORM GEN-TABLE.
           CREATE  Application      OF Excel
                   HANDLE           IN olExcel.
      *This is for training purposes only. If you don't want to see
      *Excel, remove this line.
           MODIFY  olExcel          @Visible = 1.
           MODIFY  olExcel          Workbooks::Add()
                   GIVING           olWrkBk.
           INQUIRE olWrkBk          Worksheets::Item(1) IN
                   olWrkSh.
      *Set the column title, using cells A1, B1 and C2.
           MODIFY  olWrkSh
                   Range("A1")::Value = "Order ID"
                   Range("B1")::Value = "Amount"
                   Range("C1")::Value = "Tax".
      *Now, transfer all those 300 items at once
           MODIFY  olWrkSh
                   Range("A2")::Resize(100 , 3)::Value =
                   MyTable.
           DESTROY olWrkSh.
      *Note that the SaveAs method of the WorkBook object in some versions
      *of Excel has a bug, it will claim that you pass too few parameters.
      *There are two ways to bypass this, either modify the number following
      *OPTIONAL to match the number of parameters minus 1, or preferably
      *provide parameters until the compiler gets happy.
      *Note however, the method SaveAs appears a number of times for various
      *objects, make sure you modify the correct one.
           MODIFY  olWrkBk
                   SaveAs(BY NAME Filename "C:\\Book1.xls").
           MODIFY  olWrkBk          @Close( BY NAME SaveChanges 0 ).
           DESTROY olWrkBk.
           MODIFY  olExcel          Quit().
      *This next line is very important, otherwise Excel will stick in
      *memory.
           DESTROY olExcel.
           GOBACK.
      *This section depends on the presens of the sample program rand.cbl
      *as provided in the sample directory of an ACUCOBOL-GT development
      *installation.
      *We here fill the variant table with data. Notice the mix of text
      *and numeric values, this is a convenient feature of the variant
      *data type.
       GEN-TABLE SECTION.
       GEN-TABLE-001.
           PERFORM VARYING          I FROM 1 BY 1 UNTIL I > 100
                   STRING           "ORD"
                                    I
                                    DELIMITED BY SIZE
                                    INTO MyData
                   CALL             "C$SETVARIANT" USING
                                    MyData
                                    MyItem(I, 1)
                                    END-CALL
                   CALL             "RAND" USING
                                    RND-VALUE
                                    0
                                    1000
                                    END-CALL
                   CALL             "C$SETVARIANT" USING
                                    RND-VALUE
                                    MyItem(I, 2)
                                    END-CALL
                   MULTIPLY         RND-VALUE BY 0.7 GIVING
                                    RND-VALUE
                   CALL             "C$SETVARIANT" USING
                                    RND-VALUE
                                    MyItem(I, 3)
                                    END-CALL
           END-PERFORM.
       GEN-TABLE-900.
       GEN-TABLE-EXIT.
           EXIT.


[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

Hi Gisle

Thanks for the answers.

I have some additional questions.

Second time you drag/drop an area of the grid to excel, how do you know if excel is still alive or if the user has closed the application. If excel is still open, I would try to append the "copy" to the spreadsheet.

I thought that I maybe could make an inquire of the handle, is that possible?

When I try to paste and excel is shut down, the program halts.

Regards
Steen

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

Hi Gisle

Thanks for the answers.

I have some additional questions.

Second time you drag/drop an area of the grid to excel, how do you know if excel is still alive or if the user has closed the application. If excel is still open, I would try to append the "copy" to the spreadsheet.

I thought that I maybe could make an inquire of the handle, is that possible?

When I try to paste and excel is shut down, the program halts.

Regards
Steen

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

Sorry, I am not sure I understand what you are saying. Do you drag from the Acu grid and drop onto Excel, or do you use one of the techniques I showed you?

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

Hi Gisle

Sorry for not making it clear.

I am using your technique with transferring each grid element one by one to excel. And that is working fine. But after the first copying to excel I want to do another one. Of course it would be nice to use the same excel spreadsheet, and when I continues on the same spreadsheet (using the handle I have already created) it also works fine. But maybe the user has closed excel in the meantime, and there it would be nice to be able to decide whether excel is still running or it is closed down. When it is closed my program halts

I hope it makes sense

Regards
Steen

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

There is no mechanism to detect if the user has cancelled your connection. However, as long as you are connected. The only way for the user to close Excel is via the Task Manager, which I would claim is an exception.

Exception handling is also what you should implement to avoid crashing your program. See documentation for this.

At any rate, what you also can do, is to add an event handler, from this you can capture a closing event and if you decide so, you can stop attempts to close Excel and show a message box saying to the user this is a no-no.

At any rate, good programming practise is to close the connection when you no longer use it. Having a connection idle is a burden (small one yes, but still) on the OS, release resources whenever you are done. A COM connection should be like a batch, you run your stuff and disconnect when you are done.

You can also disable user interaction in Excel when you use COM.

Finally, if you use COM just for passing data and producing a spreadsheet, don't show it at all, by not setting the Visible property.

[Migrated content. Thread originally posted on 12 February 2008]

Hi all

In a Grid it is possible to mark an area of the grid by drag and Drop. Is there a way to copy this marked area in the clipboard, and then transfer it to e.g. excel

Regards

Steen, Denmark

There is no mechanism to detect if the user has cancelled your connection. However, as long as you are connected. The only way for the user to close Excel is via the Task Manager, which I would claim is an exception.

Exception handling is also what you should implement to avoid crashing your program. See documentation for this.

At any rate, what you also can do, is to add an event handler, from this you can capture a closing event and if you decide so, you can stop attempts to close Excel and show a message box saying to the user this is a no-no.

At any rate, good programming practise is to close the connection when you no longer use it. Having a connection idle is a burden (small one yes, but still) on the OS, release resources whenever you are done. A COM connection should be like a batch, you run your stuff and disconnect when you are done.

You can also disable user interaction in Excel when you use COM.

Finally, if you use COM just for passing data and producing a spreadsheet, don't show it at all, by not setting the Visible property.