Live integration between APL ARRAY and EXCEL Sheet
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 !
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 !
18 posts
• Page 2 of 2 • 1, 2
Re: Live integration between APL ARRAY and EXCEL Sheet
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:
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:
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):
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:
More about this in the help for ⎕NC and ⎕NL.
- 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.
-
Morten|Dyalog - Posts: 454
- Joined: Tue Sep 09, 2008 3:52 pm
Re: Live integration between APL ARRAY and EXCEL Sheet
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
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
Try (XL.Visible←1) to make Excel show itself?
-
Morten|Dyalog - Posts: 454
- Joined: Tue Sep 09, 2008 3:52 pm
Re: Live integration between APL ARRAY and EXCEL Sheet
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)
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
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
-
woody - Posts: 144
- Joined: Tue Dec 28, 2010 12:54 am
- Location: Atlanta, Georgia USA
Re: Live integration between APL ARRAY and EXCEL Sheet
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:
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!
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!
-
Morten|Dyalog - Posts: 454
- Joined: Tue Sep 09, 2008 3:52 pm
Re: Live integration between APL ARRAY and EXCEL Sheet
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
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
-
woody - Posts: 144
- Joined: Tue Dec 28, 2010 12:54 am
- Location: Atlanta, Georgia USA
Re: Live integration between APL ARRAY and EXCEL Sheet
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.
-
Morten|Dyalog - Posts: 454
- Joined: Tue Sep 09, 2008 3:52 pm
Re: Live integration between APL ARRAY and EXCEL Sheet
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
This is a working prototype as a proof of concept.
The next version will be 100% APL and APL component files.
//W
-
woody - Posts: 144
- Joined: Tue Dec 28, 2010 12:54 am
- Location: Atlanta, Georgia USA
18 posts
• Page 2 of 2 • 1, 2
Who is online
Users browsing this forum: ray and 1 guest
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group