Live integration between APL ARRAY and EXCEL Sheet

[Morten|Dyalog]

Hi Charles! Could you be falling prey to the same problem that Woodley had - of a non-default value for ⎕WX? The value should be 3. Without this, you need to treat Range as a function rather than an indexable property, and write:

Code: Select all

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

The ⎕NC-vs-⎕NL behaviour is related to the fact that Dyalog APL wraps all external objects in a "regular" APL namespace. You can inject your own names into this wrapper, and then ⎕NC will report them - but it will only see the "normal" APL names:

Code: Select all

      'XL' ⎕WC 'OLECLient' 'Excel.Application' ⍝ Create a an OLE Client
      XL.XYZ←1234
      XL.⎕NL 2
XYZ
      XL.⎕NC 'XYZ'
2


The negative argument to ⎕NL alters the behaviour in two ways: You get a vector of vectors back, AND it also includes names exposed by the underlying external object (not you ALSO see XYZ in the result):

Code: Select all

      ¯6↑XL.⎕NL -2
 WindowsForPens  Workbooks  WorksheetFunction  Worksheets  XYZ  _Default


If you provide ⎕NC with a nested right argument, you will see the names exposed by the underlying object, and also get a more granular result with sub-classification of the names:

Code: Select all

      XL.⎕NC 'CentimetersToPoints' 'ActiveSheet'
¯3.6 ¯2.6


More about this in the help for ⎕NC and ⎕NL.

[Charles Brenner]

Hi Morton,
Thanks. I thought I'd checked ⎕WX←3 but since I'm now successful with SaveXL I must've thought wrong.

But the Excel object isn't visible to me. I see a slew of names & functions in Workbook but nothing tempting hits my eye. Can you tell me where to look?

Thanks,
Charles

[Morten|Dyalog]

Try (XL.Visible←1) to make Excel show itself?

[woody]

I'm doing some amazing things with the Dyalog integration to EXCEL.

I need one more "trick" ...
I need to establish a connection with a specific EXCEL (C: path and file.xlsx)

I see the example in the documentation, but I can not seem to get that to work.
(I'm sure the TESTDATA.xlsx exists under TEMP folder)

Code: Select all

       'EX'⎕WC'OLEClient' 'Excel.Application'
      EX.Workbooks.Open'C:\TEMP\TESTDATA.xlsx'
DOMAIN ERROR: Open method of Workbooks class failed
      EX.Workbooks.Open'C:\TEMP\TESTDATA.xlsx'


I have been manually opening the EXCEL workbook FIRST ...
THEN running some APL functions that make the connection to the open workbook and read/write the EXCEL sheets and cell data. (slick)

But, now, I'd like to have multiple workbooks open at the same time ... and establish several connections between APL and the several specific EXCEL workbooks.

Thoughts?

Sincere thanks!

//W

[Morten|Dyalog]

There are a couple of things you need to be aware of:

1) With the default setting for ⎕WX (3), you need to enclose a single character argument to an OLE or .NET method, so your example should be:

Code: Select all

 EX.Workbooks.Open⊂'C:\TEMP\TESTDATA.xlsx'

However, I suspect you have a non-default setting, since you seem to be getting an error message from Excel itself.

2) Excel has states where COM method calls, or attempts to connect an OleClient will fail. For example, they will fail if you are currently editing the contents of a cell. You mentioned that you manually open the sheet first; you need to make sure that Excel is completely "at rest" in order for it to accept calls.

I hope this helps!

[woody]

Bingo.
Perfect.

I created and tested a small APL program that connected 4 EXCEL files in parallel ... using different variations for the "WB" object names (WB1, WB2, WB3, WB4).

I launched 4 parallel threads ... each doing read/write to their 4 respective EXCEL files.
Worked like a charm.

Note that when I READ or WRITE back to EXCEL object variables, I use a little APL function.

My PutExcelData and GetExcelData functions trap any event that seems to happen where I'm using EXCEL on the front-end ... while APL is trying to write or read on the back-end.

After each error, my utility does a quick delay, and then loops to try again. I have the loop-out at 10 tries.

This works well ... giving me 10 seconds using EXCEL Front-end to make entries into cells, and release the interaction with EXCEL so APL can perform READS/WRITES.

I ran into some problems having APL perform the first OPEN ... prior to my opening the XLSX file with the EXCEL client. EXCEL reported that "Woody" had the excel file locked. The solution for me was to open the TASK MANAGER, and KILL the Windows EXCEL process to let it "release" the XLSX file.

Also, I noticed that if the XLSX file is opened ONLY by APL ... the calculated fields do not execute automatically ... I'm reading up on this ... and how to generate LINKED cells and CALCULATED cells .. .and how to SAVE the file via program control.

But, if I open the XLSX file first, and then START the background APL programs ... everything is fine. (Of course ... I must STOP the APL threads first .... prior to closing the EXCEL).

Sincere thanks for the help !

Cheers,

//W

[Morten|Dyalog]

Good to hear it is working! However, note that Microsoft recommends not using this approach for any kind of seriuos automation. If all you are trying to do with this is to create Excel Spreadsheets, I would recommend that you look at tools like https://github.com/the-carlisle-group/APL2XL. With that, you don't even need to run under Windows to create Excel spreadsheets.

[woody]

Thanks for the tip !

This is a working prototype as a proof of concept.

The next version will be 100% APL and APL component files.

//W