working with WinCom

[Stefan S.]

Hello List,
I try to work with WinCom. Has anybody a documentation about WinCom and Excel. The Syntax looks a little bit like VB. But I found no good reference to put Data to Excel. There is an example from 4JS
(https://4js.com//online_documentation/fjs-gdc-2.10.01-manual-html/User/WinCOM.html), but I am searching for a method to put more then one cell to excel (an array record f.e.)

Thanks in advance

Stefan Serwe

[Stefan S.]

WinCom seems to be very slowly.
When I put Data to Excel I have to work with each Cell. Is there no better way to put a wohle row to Excel ?
Attached you find a little test-program to see what I mean.

Thanks in advance


Stefan Serwe

[Lionel F.]

Hello Stefan,

For what I know, I'm afraid that is not possible.

- Visual Basic works like this:

Range("A1").Select                                  'Select the cell A1
ActiveCell.FormulaR1C1 = "value1"            'Fill the cell A1 with "value1"

Range("A2").Select                                  'Select the cell A2
ActiveCell.FormulaR1C1 = "value2"           'Fill the cell A2 with "value2"
.
.
and so on...

- Wincom works in the same way :

CALL ui.interface.frontcall("WinCOM", "CallMethod", [xlapp, 'Range("A1").Select'], [xlwb])                #Select the cell A1
CALL ui.interface.frontcall("WinCOM", "SetProperty", [xlapp, 'Range("A1").Value', "val1"],[result])    #Fill the cell A1 with "val1"

CALL ui.interface.frontcall("WinCOM", "CallMethod", [xlapp, 'Range("A2").Select'], [xlwb])                #Select the cell A2
CALL ui.interface.frontcall("WinCOM", "SetProperty", [xlapp, 'Range("A2").Value', "val2"],[result])    #Fill the cell A2 with "val2"
.
.
and so on...

Since the COM API is a Microsoft API, you could maybe have more precision from the Microsoft Support.

Nevertheless, as mentionned in the GDC documentation, Genero WinCOM API is slightly more strict than the COM language syntaxe :

• := notation is only allowed in version 2.00.1e (or later) ; for instance:  myFunction(SourceType:=3)
• "no parenthesis" notation is not allowed ; for instance: myFunction 3 must be written myFunction(3)
• numeric constants are only allowed in version 2.00.1e (or later).

The constant list depends on the application used via WinCOM ; therefore, the list is configurable: a file named etc/WinCOM.cst gathers all the constants provided today by Microsoft for Office XP. It can be modified to add user defined constants.

Does that answer your question?

Best regards,
Lionel

[Stefan S.]

Thanks Lionel,
but I am still searching for a good documentation for WinCom/Excel. With many try I was able to set the column-wide to autofit.
  call ui.Interface.frontCall("WinCOM", "SetProperty", [gl_xlwb, "activesheet.Columns(\"AA\").AutoFit", "0"],[lo_fehler])

But I am not able to set some Boarders, or Font-Size

Why can I set with DDE more then one Cell in an Excel-Sheet ??

I hope you have some further tips for me


Thanks Stefan

[Lionel F.]

Hello Stefan,

This is doable : you have to achieve this in two times.

1) Doing the selection (e.g : several columns or several cells)

###From cell C4 to E15
CALL ui.interface.frontcall("WinCOM", "CallMethod", [xlapp, 'Range("C4:E15").Select'], [xlwb])

###From columns C to H
CALL ui.interface.frontcall("WinCOM", "CallMethod", [xlapp, 'Columns("E:G").Select'], [xlwb])


2) Applying what you're expecting to do on the selection (e.g.:set the font to bold):

###Set the font to bold
CALL ui.Interface.frontCall("WinCOM", "SetProperty", [xlapp, 'Selection.Font.Bold', "true"],[result])


On my side, in order to find the correct syntax, I firstly recorded a macro of what I'm expecting to do. I analyse the vba code and I attempt to adapt it to the COM api. It doesn't work for everything but this the best method I found.

Don't hesitate to get back to me if you need further information.

Best regards,
Lionel

[Stefan S.]

Hello Lionel,
so far so good. I have to use the try and error method to work with win-com. But what about the rate of win com. A table with 1000 rows and 10 columns will take about 2 minutes to transfer to excel. That is not very practicalbe for our customers. To avoid this I will better use the old way to create a csv file, copy that to the client workstation and try to open excel with that file. This is not the method I really like to do it, becuse I think that win com offers more possibilities.

In VBA you can use   the   "Application.ScreenUpdating = False"  method to speed up the macros. Can I use this with win com to ?


Kind regards
Stefan Serwe

[.]

Stefan,

Just a word about WinCOM extension: we simply provide a way to access or manipulate a COM server from a 4GL program.

Unfortunately, we're not specialists concerning all the COM servers and all the things you could do.
We've some experience with Excel and Word, as these are the most used servers, but Excel and Word APIs are so huge it's impossible for us to know everything (Our goal is to focus on 4GL, right ?)

Where we can help you: if the syntax does not work - there are some known limitations - or if there is crash somewhere. For anything else, we will not do much more than you would do: just try if it works ;).

But don't hesitate to post feedback here, we're gathering all the topics concerning WinCOM.

Best regards,
Pierre-Nicolas

[.]

Stefan,

A technique you could use to populate the Excel array quickly is via the clipboard.

Use the ui.Interface.FrontCall standard clipboard methods cbclear and cbadd to populate the clipboard, and then use the WinCom method to select a range of cells and paste into those cells.  Something like ...

Code (genero) Select Expand

CALL ui.Interface.FrontCall("standard","cbclear","",result)
LET sb = base.StringBuffer.create()
FOR i = 1 TO number_of_rows
   FOR j = 1 TO number_of_columns
      CALL sb.append(value(i,j))   # append the cell value
      CALL sb.append(ASCII(9))   # append the Tab column delimiter
   END FOR
   CALL sb.append(ASCII(10))   # append the LF row delimiter
END FOR
CALL ui.Interface.FrontCall("standard","cbadd",sb.toString(),result)

#and then call the WinCom method to select where you want to paste
CALL ui.interface.frontcall("WinCOM", "CallMethod", [xlapp, 'Range("?:?").Select'], [xlwb])

# and then call the WinCom method to paste

I have used this technique of populating the clipboard in this manner to create a Copy Entire Table function for use with arrays.


Reuben

[Ullrich M.]

Hi Reuben,
the copy/paste way to populate the Excel array works perfectly.
I tried some more or less useless gimmicks.

Regards

[Neil M.]

I hit the same problem of winCom being slower than winDDE for populating multiple cells.
As DDE stopped working on some machines, even when using the same version of the GDC and running the same program from the same server but different client machine,so I abandoned DDE. ( Assume the problem was in Excel as everything else was the same ).
In the end I replaced my DDE/COM functions with a function to produce a csv file, output using channels;
used winSaveFile (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/UtilityFunctions.html#UF_WINSAVEFILE ) to ask where to save the file on the client machine;
copied the file to the client machine using fgl_putfile (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/BuiltInFunctions.html#BF_FGL_PUTFILE);
used winShellExec (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/UtilityFunctions.html#UF_WINSHELLEXEC ) to open the file with Excel.
This proved to be much quicker than DDE or COM and worked on all the machines I tested.

[Reuben B.]

I hit the same problem of winCom being slower than winDDE for populating multiple cells.
As DDE stopped working on some machines, even when using the same version of the GDC and running the same program from the same server but different client machine,so I abandoned DDE. ( Assume the problem was in Excel as everything else was the same ).
In the end I replaced my DDE/COM functions with a function to produce a csv file, output using channels;
used winSaveFile (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/UtilityFunctions.html#UF_WINSAVEFILE ) to ask where to save the file on the client machine;
copied the file to the client machine using fgl_putfile (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/BuiltInFunctions.html#BF_FGL_PUTFILE);
used winShellExec (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/UtilityFunctions.html#UF_WINSHELLEXEC ) to open the file with Excel.
This proved to be much quicker than DDE or COM and worked on all the machines I tested.

Neil,

Just be aware that with that technique if you have any CHAR fields where the data is purely numeric e.g. account_code maybe a CHAR(5) but contain values such as 00100,10100,51200 then the Excel import will treat the column as numeric and in Excel it will be right-justified and any leading zeros will be dropped e.g. 00100 becomes 100

Reuben

[Stefan S.]

I hit the same problem of winCom being slower than winDDE for populating multiple cells.
As DDE stopped working on some machines, even when using the same version of the GDC and running the same program from the same server but different client machine,so I abandoned DDE. ( Assume the problem was in Excel as everything else was the same ).
In the end I replaced my DDE/COM functions with a function to produce a csv file, output using channels;
used winSaveFile (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/UtilityFunctions.html#UF_WINSAVEFILE ) to ask where to save the file on the client machine;
copied the file to the client machine using fgl_putfile (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/BuiltInFunctions.html#BF_FGL_PUTFILE);
used winShellExec (https://4js.com//online_documentation/fjs-fgl-2.10.01-manual-html/User/UtilityFunctions.html#UF_WINSHELLEXEC ) to open the file with Excel.
This proved to be much quicker than DDE or COM and worked on all the machines I tested.

Neil,

Just be aware that with that technique if you have any CHAR fields where the data is purely numeric e.g. account_code maybe a CHAR(5) but contain values such as 00100,10100,51200 then the Excel import will treat the column as numeric and in Excel it will be right-justified and any leading zeros will be dropped e.g. 00100 becomes 100

Reuben

Hello Nell,
Have a look at Reply #8. There Is an attached file "wincom.zip" there we are using the clipboard and Win-Com Functions(thanks to Reuben) to put multiple Cells to Excel. That method works very fast and we have no problems with it.


kind regards
Stefan Serwe

[.]

Hi Reuben,

I am trying to use this technique on a Ms Word table.

My problem is that all the data is being pasted in the first cell. (ASCII(9)) is not tabing to the next cell but behaves like  CTRL + TAB and tabs to next tab space in the first cell.

I want to create labels using data from my record array where each cell in Word is a record of my array.

has anyone worked with WinCom and Word tables ?

Regards
Bothwell

[Reuben B.]

...
I want to create labels using data from my record array where each cell in Word is a record of my array.

...

First question would be why not use Genero Report Writer to create your labels? A 5-15 minute job rather than mucking around with Word and WinCom.

With WinCom my rule of thumb is to record what I want to do in a Macro and then use that code as a basis for the WInCom calls.  Interestingly when I had a quick go at this, when attempting to record a Macro in Word it didn't allow me to select the table as I wanted.

Selecting the table outside the macro recording if I selected the whole table I got the result you described.  If I selected only the columns in the table I was able to get the desired result.  A little googling and trial and error led me to this ....

ActiveDocument.Tables(1).Select
Selection.EndOf Unit:=wdColumn, Extend:=wdExtend
Selection.Paste

... it did the desired job.  That might get you pointed in the right direction but as I said if you are generating labels from records in your database, use Genero Report Writer (look at the OrderLabels.4rp) in the examples shipped with Studio

[.]

Just jumping in the topic to explain a bit why we've some limitations in wincom extension.

Our extension is based on Microsoft C++ COM API, which is plain C++, i.e. create objects, call functions, pass these objects in parameters and so on.
So our job is basically to transform the string written in the front call in C++ code ; and this is pretty difficult to cover everything.

issue #1: syntax.

you can write in vbScript something like:

myObject.myFunction( a )
myObject.myFunction a
myObject.myFunction( myParam := a)

and I'm sure there are others (I remember you could write "call XXX" like in 4GL to call a function).


issue #2: typing.
while vbScript has a very weak typing policy, the C++ API needs strong typing. When we're calling the COM function, we need to indicate the type of the parameters we're passing.

which means that:
a = "foo"
myObject.myFunction(a)

is internally not the same as

a = 123
myObject.myFunction(a)

We've to guess the type, but sometimes it's impossible. For instance, we've an open case at the support today for excel and page break insertion.
The function is the following: ActiveWindow.SelectedSheets.HPageBreaks.Add
and it takes as parameter a cell. Not an int, not a string, but a cell. But GDC (and the C++ com api) has absolutely no idea what is a cell.

vbScript call is:

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

but ActiveCell could be: a constant, a string, an object... and while parsing the command we've absolutely no way to know what is the type, and moreover what is "ActiveCell" as it is a global object in excel.

So to solve these issues we've 2 solutions.
- long term: there is an API which allows to pass directly vbscript to the com server. Mixing this with the C++ COM api should remove a lot of limitations.
- short term: whenever it's possible, you can write macros, and call these macros from 4GL. It's not always possible and it needs you to use a document including the macros, but it can save a lot of time.