Hi Dennis,   as I mentioned in the other topic: RANGE is an object now.   Think you still use it like a property

Dennis Vorst said Excellent this all works . But when i want to add a formula in a cell as is done in VBA like so:

ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" I use the following uniface code:

p_activecell->SET_FORMULAR1C1(p_value) where p_activecell is a handle to the activecell (a reference to a RANGE signature)

and p_value is either "=SOM(E7:G7)" as found in the cell of the excel file or "=SUM(RC[-3]:RC[-1])" as the value found in the VBA editor. in both cases i donot get the result of the cells E7 through G7, but get the exact string result in the underlying cell. I have tried passing the value several different ways. As a string OR as a string within a string (using %%") OR using single quotes ('). Any suggestions?

Hi Dennis, Good to hear. I did a couple of tests and the notation "=SUM(E7:G7)" will only work when you use the SET_VALUE operation; e.g. p_value = "=SUM(E7:G7)" p_activecell->SET_FORMULAR1C1(p_value) If you would like to use the SET_FORMULAR1C1 operation then you have to specify the range relative to the current range (or cell) - this is done by specifying the row as R[n] and column as C[n], whereby n is a number. The following will give (e.g.) the sum of the range A1:A3 and assign it to A4: vWorkSheet->GET_RANGE(vActiveCell, "A4", -) vActiveCell->SET_FORMULAR1C1("=SUM(R[-3]C[0]:R[-1]C[0])") * R[-3]C[0] = A1; three rows up in the same column * R[-1]C[0] = A3; one rows up in the same column And your example should look like this: "=SUM(R[0]C[-3]:R[0]C[-1])" I hope this helps. Regards, Daniel Senior Customer TSA, Uniface

Hi Guys, Thanks for all the input. It seems that when I use the record macro function the result is in English. Which is probably the default:

ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" Which is not recognized when i parse it via the RANGE object.

v_activecell->SET_FORMULAR1C1("=SUM(RC[-3]:RC[-1])") ; results in an error However when i parse it in the language that my Excel version is in. In this case Dutch. it works fine

v_activecell->SET_FORMULAR1C1("=SOM(RK[-3]:RK[-1])") Notice the subtle differences ? SUM vs SOM and RK vc RC?

I put what i have sofar in the github repository: https://github.com/dennisvorst/uniface/tree/master/u9.6.04 Just get the C_MIS_EXCEL.exp from the components folder and get the MATERIAL.IP_EXCEL2010.exp from the libraries/include_procs folder. It is a library named MATERIAL with an include proc IP_EXCEL2010 containing several entries used in the component.   It is basically a simple component that shows you some of the steps (top to bottom) to manipulate an Excel document. Prerequisites: - you need to have the Excel 2010 signatures generated (see the EXEC trigger for details) - the VBA version i tested it with is 14.0.   Please feel free to fork the project (using Github) and expand on it. Or just let me know if you have any suggestions.

Dennis Vorst said Thanx for the input. I have made some progress. apparently there is a difference between a "" and a - (the optional character). I have reached the point where i can change the content of a cell. But... there is always a but isnt there. In my code i have 3 lines where i select a cell in a worksheet and then put a value in. activate v_activecell->"GET_RANGE"(v_activecell, "A1", -) activate v_activecell->"SET_FORMULAR1C1"("sometext") activate v_activecell->"GET_RANGE"(v_activecell, "A2", -) activate v_activecell->"SET_FORMULAR1C1"("some other text") activate v_activecell->"GET_RANGE"(v_activecell, "B1", -) activate v_activecell->"SET_FORMULAR1C1"("a third text") But when my cursor is on cell B2 the first to lines result in the value being printed in the current cell (B2). The second set of lines donot select A2 but move the cursor 1 down to cel B3 where the text "some other text is displayed". Then the last set of lines move the cursor 1 position to the right C3 before printing "a third text" So it appears that the paths given are relative from the starting cell. My question is how can i move my cursor to cell A1. Which operation in which object do i need to use. Any help is greatly appreciated.

Hello Dennis, It indeed makes a huge difference if you pass an empty string ("") or an optional parameter (-) - in the first case you pass an empty string as value to the specific parameter and in the second case no value is passed. One remark regarding your code notation: it is best not to combine the activate statement with a handle. In case the handle will return an error then this will be overwritten by the activate statement. This is the recommended notation:

And I think I know why the cells are selected in a "relative" way: you use as input the handle of a cell for the GET_RANGE operation and this means that the specified range will be relative to the "current" cell (input handle). So instead of the handle of a cell you should use the handle of a worksheet. For example:

And if you only want to add a text to a cell (and not a formula) then you also could use SET_VALUE instead of SET_FORMULAR1C1. For example: v_activecell->SET_VALUE("UT_OPTIONAL", "sometext") I hope this helps - and makes sense. Regards, Daniel Senior Customer TSA, Uniface   P.S. Excuse the formatting, but the Editor is not very cooperative...

The code should look like this: v_activesheet->GET_RANGE(v_activecell, "A1", -) v_activecell->SET_FORMULAR1C1("sometext") v_activesheet->GET_RANGE(v_activecell, "A2", -) v_activecell->SET_FORMULAR1C1("sometext") v_activesheet->GET_RANGE(v_activecell, "B1", -) v_activecell->SET_FORMULAR1C1("sometext")

Thanx for the input. I have made some progress. apparently there is a difference between a "" and a - (the optional character). I have reached the point where i can change the content of a cell. But... there is always a but isnt there. In my code i have 3 lines where i select a cell in a worksheet and then put a value in. activate v_activecell->"GET_RANGE"(v_activecell, "A1", -) activate v_activecell->"SET_FORMULAR1C1"("sometext") activate v_activecell->"GET_RANGE"(v_activecell, "A2", -) activate v_activecell->"SET_FORMULAR1C1"("some other text") activate v_activecell->"GET_RANGE"(v_activecell, "B1", -) activate v_activecell->"SET_FORMULAR1C1"("a third text") But when my cursor is on cell B2 the first to lines result in the value being printed in the current cell (B2). The second set of lines donot select A2 but move the cursor 1 down to cel B3 where the text "some other text is displayed". Then the last set of lines move the cursor 1 position to the right C3 before printing "a third text" So it appears that the paths given are relative from the starting cell. My question is how can i move my cursor to cell A1. Which operation in which object do i need to use. Any help is greatly appreciated.

even easier and working in most of the cases: just SPAWN the filename. Under Windows, the application and action assoziated with the file extension will be started as Zdeněk stated. The default action gives you some additional flexibility what to do with the file (you can even print it etc.)

mpservices said Hello I'm not familiar at all with this, but interested in lauching excel with opening a file (currently I'm using a bat file)... [cut] I want to launch it outside uniface and the user will close it . Regards Dominique

Hello Dominique, if you need just opening an .xls file (be it with Excel or OpenOffice or any other registered application), it's much easier just to launch the file via OS (Windows) ShellExecute, isn't it? I mean something like activate/stateless "WINAPI".SHELLEXECUTE(0, "open", "C:\\mydir\\myfile.xlsx", "", "", 1). This will open user defined default application outside the Uniface (be it Excel, OpenOffice, web browser, mail client, ...) We use this for opening files, URL's etc... Kind regards, Zdeněk Socha

Hello Dominique, That's just a silly typo - sorry.

Regards, Daniel

Hello I'm not familiar at all with this, but interested in lauching excel with opening a file (currently I'm using a bat file) , I tried just to copy this and ran, but got $procerror -57 on the "vExcelHandle->GET_ACTIVEWORKBOOK(vActiveWorkBook)" vWorkBooksv is not declared in you example so that's why I got this error what do I need to do to make it work or what did I miss, I want to launch it outside uniface and the user will close it . (importing signature is ok) Regards Dominique

And here's some simple code that shows how to open a WorkBook file (and close it). Note: It is possible to mark a parameter of a COM Call-Out operation as optional by specifying a hyphen (-) or the string "UT_OPTIONAL" (see example below):

Hello Dennis, First of all you should import the signatures for Microsoft Office Excel 2010. You can do this using the following idf /sti command line switch. For example:

Next it might be a good idea to have a look at the Uniface Sample "Call-Out to Microsoft Word Using COM" that is part of the Uniface distribution (at least) since version 9.4 (see \\uniface\\samples\\IntegrateComWord.zip). This sample shows you how to: * Find the Word interfaces using Visual Basic’s Object Browser * Use the macro recording technique to find which components are used in a Word application * Use Uniface’s COM import facility to get Word signatures * Modify the imported signatures so that they can be used in a Uniface application * Activate the operations in the Word signatures to create and edit Word documents from Uniface The same principals shown in this sample can also be used for calling out to Microsoft Office Excel. I hope the provided information is helpful. Kind regards, Daniel

Excellent this all works . But when i want to add a formula in a cell as is done in VBA like so:

ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" I use the following uniface code:

p_activecell->SET_FORMULAR1C1(p_value) where p_activecell is a handle to the activecell (a reference to a RANGE signature)

and p_value is either "=SOM(E7:G7)" as found in the cell of the excel file or "=SUM(RC[-3]:RC[-1])" as the value found in the VBA editor. in both cases i donot get the result of the cells E7 through G7, but get the exact string result in the underlying cell. I have tried passing the value several different ways. As a string OR as a string within a string (using %%") OR using single quotes ('). Any suggestions?