Live integration between APL ARRAY and EXCEL Sheet

APL-related discussions - a stream of APL consciousness.
Not sure where to start a discussion ? Here's the place to be
Forum rules
This forum is for discussing APL-related issues. If you think that the subject is off-topic, then the Chat forum is probably a better place for your thoughts !

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby Morten|Dyalog on Tue Mar 24, 2020 8:04 am

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.
User avatar
Morten|Dyalog
 
Posts: 409
Joined: Tue Sep 09, 2008 3:52 pm

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby Charles Brenner on Mon Mar 30, 2020 4:51 am

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
Charles Brenner
 
Posts: 2
Joined: Mon Mar 23, 2020 4:09 am

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby Morten|Dyalog on Mon Mar 30, 2020 7:54 am

Try (XL.Visible←1) to make Excel show itself?
User avatar
Morten|Dyalog
 
Posts: 409
Joined: Tue Sep 09, 2008 3:52 pm

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby woody on Sat Mar 13, 2021 2:39 am

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
Woodley Butler
Automatonics, Inc.
"Find your head in the APL Cloud"
http://www.APLcloud.com
User avatar
woody
 
Posts: 140
Joined: Tue Dec 28, 2010 12:54 am
Location: Atlanta, Georgia USA

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby Morten|Dyalog on Sat Mar 13, 2021 6:47 am

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!
User avatar
Morten|Dyalog
 
Posts: 409
Joined: Tue Sep 09, 2008 3:52 pm

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby woody on Sun Mar 14, 2021 10:35 pm

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
Woodley Butler
Automatonics, Inc.
"Find your head in the APL Cloud"
http://www.APLcloud.com
User avatar
woody
 
Posts: 140
Joined: Tue Dec 28, 2010 12:54 am
Location: Atlanta, Georgia USA

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby Morten|Dyalog on Mon Mar 15, 2021 7:56 am

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.
User avatar
Morten|Dyalog
 
Posts: 409
Joined: Tue Sep 09, 2008 3:52 pm

Re: Live integration between APL ARRAY and EXCEL Sheet

Postby woody on Mon Mar 15, 2021 2:16 pm

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
Woodley Butler
Automatonics, Inc.
"Find your head in the APL Cloud"
http://www.APLcloud.com
User avatar
woody
 
Posts: 140
Joined: Tue Dec 28, 2010 12:54 am
Location: Atlanta, Georgia USA

Previous

Return to APL Chat

Who is online

Users browsing this forum: No registered users and 0 guests