XLGet Active Workbook

[drunkenmonkey]

Hi!
I've got an open xlsx book. How to use the XLGet function.
The file was generated by an app so it is not yet saved.
I want to save it as an xls file to use it has an array.
Thank you!

[Grovkillen]

You need to use the XLopen command to have a session id. So I suggest you first use a keyboard command to save the open Excel file and then close the application. After that you can use the Excel commands.

[drunkenmonkey]

Thank you Grovkillen,
Managed to do it.
Have a good day!

[Dorian (MJT support)]

Just to add to what Grovkillen said, another alternative if the file is already open is XLGet.

[drunkenmonkey]

thank you for your help!
2 issues now. Maybe different problem.
If I do

Code: Select all

            Let>fichier=C:\Users\%USER_NAME%\AppData\Local\Temp\GWPR.XLSX
            XLGet>%fichier%,xlBook
            XLSave>%xlBook%,%USERDOCUMENTS_DIR%\mybook.xls,16

The file in xls format wont't be saved. But if I save as with the app it is working.

I have this

Code: Select all

            XLSheetToArray>%USERDOCUMENTS_DIR%\mybook.xls,Sheet1,xlData
            
            Let>nombre=%xlData_count%
            
            Let>duree=xlData_%nombre=%_1
            Let>age=xlData_%nombre=%_2

The row count var is ok. But let say that i want to retrive the value of col 1 an 2 with the last row.
I guess I't not properly coded beause duree and age var are not giving any values.

thank you again for your precious help!

[Dorian (MJT support)]

I had a play with it and it doesn't seem to like 16, or a few of the others. It seems to work with 39, or nothing.

Code: Select all

XLSave>xlBook,d:\mybook.xls,39
XLSave>xlBook,d:\mybook.xls

However, only the 39 option produced a file readable by XLSheettoArray.

My test sheet simply contained 3 row and 2 columns :
A1:qq
B1:ww
A2:ee
B2:rr
A3:tt
B3:yy

Code: Select all

XLSheetToArray>d:\mybook.xls,Sheet1,array

Here are the results :

Code: Select all

0: ARRAY_1_1=qq
0: ARRAY_1_2=ww
0: ARRAY_1_COUNT=3
0: ARRAY_2_1=ee
0: ARRAY_2_2=rr
0: ARRAY_2_3=
0: ARRAY_2_COUNT=3
0: ARRAY_3_1=tt
0: ARRAY_3_2=yy
0: ARRAY_3_COUNT=3
0: ARRAY_4_1=
0: ARRAY_4_3=
0: ARRAY_COUNT=4

[drunkenmonkey]

Thank you Dorian.
I have tried both with 39 ans nothing and negative on my side.
I would have prefer not use keystroke but without a working format do I have another choice.
I managed this anyway.

Code: Select all

XLOpen>C:\Users\%USER_NAME%\AppData\Local\Temp\GWPR.XLSX,1,xlBook
Let>WIN_USEHANDLE=1
GetActiveWindow>xlBook,X,Y[,Width,Height]
Let>WIN_USEHANDLE=1
UIClick>{%xlBook%},{"File Tab"}
Wait>1
UIFocus>{%xlBook%},{"Save As"}
Wait>1
UIFocus>{%xlBook%},{"Save as type"}
Wait>1
Press Down
Wait>1
Press Down*3
Wait>.5
Press Enter
Press TAB
Wait>.5
Press Enter
Wait>1
Press Enter
Wait>1
Press LALT
Press F4

[Dorian (MJT support)]

Eurgh. Why don't you send the xlsx file to us over at Support and we'll have a play with it. I'm guessing the reason you're using XLSheetToArray is because you don't have Excel installed? Otherwise you'd have all the other XL options to play with and not need the file to be an xls as opposed to an xlsx.

[drunkenmonkey]

Hi Dorian! Again thank you for your help.
Here's my reason for using it.
There is a grid in a window app. I want to get values at specific rows and col. Simple you might think. Here's my problem. The headers titles can have more than one line. When I use the FindObject Wizard and insert this code. The $ symbol in the header title is on second line, that is why the code is separated on 2 lines by the wizard tool.

Code: Select all

UIGetValue>{%window_CanVie_3%},{"Prime annualisée requise
($) Ligne 9"},curVals,Positions,nHeight

As you see the suggested code can not be interpreted by MS because the command is broken on 2 lines. If I removed the spaces and put it on one line it is not working.
Because I can not use properly the command I tried another way of getting the values and It by exporting the the grid to an xl book. From the exported data I scroll it in an array.
I would prefer use the UIGetValue instead of going with the export data grid method.
Is there a way to fix the suggested code of the wizard tool?
Thank you!

[Dorian (MJT support)]

That is a strange one, and without being able to experiment with it I can't think of an obvious way.

Have you tried the OCR wizard? Tbh if this was me and I was having reliability issues extracting text I'd probably go the download Excel route too.

Do you have that method working now? Either with XLOpen/Get/XLGetCell or XLSheetToArray?

[drunkenmonkey]

Thank you Dorian for your answer. I have managed.
Just one question. If I want to loop the array and show the result in the MDL, what modification should I apply to the loop. The array can have multiple rows. As you can see I just want each possible 10th row.

Code: Select all

XLSheetToArray>%USERDOCUMENTS_DIR%\mybook.xls,Sheet1,xlData

//la matrice a un header et donc le compte affiche le nombre de lignes plus 1
Let>compte=%xlData_count%
Let>nombre=%xlData_count% - 1

//MDL>nb de lignes du xls -> %nombre%

//Avec Canada Vie le tableau va jusqu'à 105
//Le tableau montre l'âge initial de l'assuré + 1
//Âge réel AgeR est donc %age% + 1
Let>age=105 - %nombre%
Let>ageR=%age% + 1
//MDL>Àge de l'assuré -> %ageR%

//Boucle la matrice
Let>v=1
repeat>v
Let>v=v+1
//Récupère la valeur de la col 1 à chaque multiple de 10 durées
Let>x={%v% mod 10}
Let>valeur=xlData_%v%_1
IF>x=0
MDL>%valeur%
Endif

Until>v=%compte%

Thank you again for your precious help.

[Dorian (MJT support)]

If you wanted every 10th row wouldn't you just simply use Let>v=v+10?

There would probably be an overlap so you need to stop when v > compte.

Until>v>%compte%

[drunkenmonkey]

Thank you Dorian for your comments. Very helpful!