Live integration between APL ARRAY and EXCEL Sheet

[woody]

Greetings,

I've seen several posts that come close to what I need.
(I reviewed the LoadData WS and LoadXL function .. but it seems to read FROM EXCEL into APL)

I would like to display LIVE APL DATA ARRAY in an EXCEL Worksheet.

This is a simple 2D nested array table.

As changes are made inside APL to the APL Array, I'd like to see those same EXCEL CELL values also change.

My current interest is only one-way ... from APL ... into EXCEL.

Any tips or tricks to accomplish this ?

Sincere thanks,

//W

[Morten|Dyalog]

Hi Woodley!

I think all the ingredients you need can be found below:

Code: Select all

      mat←3 4⍴⍳12 ⍝ Some data you want to view in Excel
      'XL' ⎕WC 'OLECLient' 'Excel.Application' ⍝ Create a an OLE Client
      range←XL.ActiveSheet.Range[⊂'A1:D3'] ⍝ Get hold of a suitable range
      range.Value2←mat ⍝ Set the value


If you want changes to mat to cause the range to be updated, you can do this with a trigger function, which will be called whenever mat changes.

Code: Select all

     ∇ UpdateRange args                                                             
[1]    :Implements Trigger mat                                         
[2]    range.Value2←mat                                                 
     ∇                                                                 

      mat←⌽mat ⍝ Watch it change

The right argument to the trigger function is a namespace containing three variables, Name NewValue and OldValue, so you can use the same trigger function for several variables.

[woody]

Perfect!

This got me going!

Is there some documentation on this?
I searched and looked ... but could not find anything.

//W

[Morten|Dyalog]

The general documentation for the OLE Client interface (which does include some simple Excel examples) can be found in
http://docs.dyalog.com/17.1/Dyalog%20for%20Microsoft%20Windows%20Interface%20Guide.pdf.

Some good examples are in the loaddata workspace, which is distributed as part of Dyalog APL. You found the LoadXL function, there is also a SaveXL function which performs updates.

Documentation for the :Trigger functionality can be found by searching the online help (go to google, search for "Dyalog :Trigger" and it should take you to https://help.dyalog.com/17.1/Content/Language/Defined%20Functions%20and%20Operators/Triggers/Triggers.htm.

[woody]

Great!

I'll do some reading and learning ...

My new laptop worked great with your provided code ... right out of the box 1 2 3 !
I have Dyalog installed under C:\DyalogAPL (version 17.0.xxx 64bit)

On my 2nd older computer (also Windows 10)
I have DyalogAPL V17.0 64bit installed under C:\Program Files\Dyalog ....

QUESTION 1:
Is it a "best practice" to install Dyalog under it's own dedicated C:\DyalogAPL directory ... rather than using the default Windows program files/Dyalog path ?

EXCEL TRUST CENTER?
Also, using the latest version of Office EXCEL v2019 from O365...
I see that EXCEL has OPTIONS and settings for a "Trust Center".
I added Program Files\Dyalog (but not sure if it is needed or not)

QUESTION 2:
Is it necessary to configure the EXCEL Options Trust Center for this integration to work?

So, on my older PC with Dyalog installed under Program Files, I just keep getting Syntax error here:

Code: Select all

'XL'⎕WC'OLECLient' 'Excel.Application' ⍝ Create a an OLE Client
 range←XL.ActiveSheet.Range[⊂'A1:B2']
Syntax Error  .............^


I can see the XL object in APL Explorer ...
with all of its methods and properties.

I have tried to erase the 2 namespaces XL and range
And tried to start EXCEL first, then APL..
and start APL first and then EXCEL
But just keep getting Syntax Error.

I'm not sure what the issue is.

One last clue ...
When I start APL (on either computer) I see this POPUP

QUESTION #3:
Is there some house-cleaning needed to prevent this popup ?
(e.g. should I erase objects XL and range from the WS before SAVING and shutting down APL?

APL_XL_Load_Warning.png (7.84 KiB) Viewed 22302 times

NEXT STEPS:
I plan to re-install Dyalog on my 2nd computer under C:\DyalogAPL folder ...
and not under the Windows Program Files built-in folders.

And see if this makes a difference.

Also, I'll try a EXCEL without any Dyalog Trust Center settings to see if that really matters.

Sincere thanks for the continued support!

//W

[Morten|Dyalog]

Hi Woodley, it sounds as if you need to do some spring cleaning on your old laptop!

I don't think this is related to the Excel trust centre (I have never used it) or the folder that you have installed APL in.

My guess is that the SYNTAX ERRORS are probably the result of a non-default value for ⎕WX. The default was changed to 3 back in Version 11.0 (October 2006), when we introduced object oriented language features and were able to improve the syntax for certain types of .NET and COM properties, so they became index-able rather than being implemented as function calls.

⎕WX allows people to manage the migration to the new behaviour (14 years should be enough time to convert or dump your old code, I think ;-)); my guess is you still have the old compatibility mode enabled and have forgotten about it. If my hunch is right, you will find that this works:

Code: Select all

range←XL.ActiveSheet.Range 'A1:B2'

The error message about not being able to rebuild objects suggests that you have loaded a workspace (do you have a CONTINUE workspace that is loaded when APL starts?) or a session file which was saved by an APL interpreter that has a different architecture (bit size or endian-ness). In your case my guess would be that you saved it with a 32-bit version of Dyalog APL and are now using 64-bit. We try to patch up old stuff but if there is a reference to something external, there are architectural barriers that we cannot cross.

[woody]

Good info !

I made some progress today on my old computer that was having problems connecting APL to EXCEL. (I deinstalled APL, and then re-installed on special folder C:\DyalogAPL)

1. This PC with Windows10 had several problems when I first tried to install Dyalog 17.0 (64bit) over 18 months ago.

NOTE: I had FIRST installed the latest version of Office EXCEL v2019 from O365 which includes ONEDRIVE connection to the O365 cloud storage.

Then, installed Dyalog APL using default path under C:\Program Files\Dyalog ...

2. At that time, I hit a major CONFLICT between the WIN10 built-in ONEDRIVE configuration that is used in O365 Office ONEDRIVE cloud services. During the install of APL, I hit several problems with permissions, access, and normal activities. Again, the installation was on the normal default path: C:\Program Files\Dyalog

3. After a multi-day battle (and with your help, and help from the Dyalog forum)... I finally found a way to DISABLE ONEDRIVE on that PC ... and got DyalogAPL to work.

4. However, the deep rooted mapping of C folders that also had virtual paths that were OWNED by the ONEDRIVE tools left behind some "random segments of code". That is, I had to HACK several things just to get Dyalog APL to run normally with the session log, fonts, etc. But... I finally got APL to run normally.

18 months have gone by...

5. Yesterday, I tried to open an APL workspace (also saved as V17.0) and make the simple connection to EXCEL: range←XL.ActiveSheet.Range 'A1:B2'
... but continually got a SYNTAX ERROR

6. I remembered the mess 18 months earlier with a conflict between Office 365 ONEDRIVE, Windows ONE DRIVE configurations ... and a simple install of DyalogAPL 17.0. (blaming O365, WIN10 and ONEDRIVE)

7. So, today I uninstalled APL ... and did a fresh reinstall... but this time set the path to C:\DyalogAPL

8. I also removed any added TRUST PATHS that I had setup before under EXCEL TRUST CENTER.
As you said... there is no need to setup TRUST ENTRIES.

Now, everything works perfectly.

Interesting.

The business driver behind this technical debugging is our plan to package and resell a DyalogAPL application... and our desire to have a smooth install of Dyalog APL for our clients who will also have Office O365 client products and cloud services.

I'm all set for now.

Thanks again !!!

//W

[Morten|Dyalog]

Yup, a complete re-install would also have cleared up the issues with ⎕WX and old objects stored in a CONTINUE workspace or session file.

Good to hear all is working well again!

[woody]

Another cool feature when connecting EXCEL to APL that I just discovered ...

Each EXCEL "CELL" can be "NAMED" inside EXCEL.

So, for example, rather than defining a cell range of "B7:D23" ...
I can NAME the cells in EXCEL where "B7" can be named "MATstart" and D23 can be named "MATend".

Now, when I write my APL code to connect to the RANGE, I can reference "MATstart:MATend".

This makes it a little easier to keep track of the purpose of these defined cell ranges.
AND ... allows the freedom of moving them around in EXCEL ... and not breaking any of the APL code that references EXCEL "NAMES".

I'm really having a good time with this EXCEL to APL integration.

NEXT STEP:
I am learning more about Triggers (per the document you referenced)

:Implements Trigger mat


I tried to write a background APL function that LOOPS using ⎕DL←5.
And each 5 seconds, the program READS a special EXCEL CELL that I use to start the APL program processing.

If the cell contains 'GO' then I run through some APL code ... and update the other cells. Then, I return that cell back to 'READY'.

This works well ... however, anytime the EXCEL user CLICKS into a CELL and starts updating anything within EXCEL ... APL will receive a warning / error from the connection (which is not available at that moment).

As you have already suggested, I can use APL Triggers to work around this.

I'd like to setup triggers in both directions... running in APL.
Trigger 1: If an APL Variable changes ... fire the trigger and update EXCEL
Trigger 2: If the user changes a specific cell value in EXCEL ... fire the trigger and run some APL function which will update an APL variable that will fire Trigger 1.

UPDATE EDIT:
I tried several ways .. .but could not get an APL Trigger to fire from a change that is made in EXCEL. So, I will continue to use my 5 second APL LOOP technique... with :Trap that handles the case when the EXCEL user is editing an EXCEL Cell and blocking access from APL.


Looking good !

//W

[Charles Brenner]

This discussion is very interesting to me. Unfortunately I get stuck at an early stage but even negative information is good information.
'XL' ⎕WC 'OLECLient' 'Excel.Application' ⍝ Create a an OLE Client
works fine. But
range←XL.ActiveSheet.Range[⊂'A1:D3'] ⍝ Get hold of a suitable range
SYNTAX ERROR
range←XL.ActiveSheet.Range[⊂'A1:D3'] ⍝ Get hold of a suitable range
∧
XL.(⎕NC'ActiveSheet')
0
XL.⎕nl-2 however produces a lot list of variables including ActiveSheet. That's consistent with the syntax error.

My Excel is 2007. Is that the problem? Hope not - I accidentally got near to Office365 and enabled OneDrive while setting up my new computer recently, which generated problems that wasted a fair amount of effort to back out of.