XLGet Active Workbook

Hints, tips and tricks for newbies

Moderators: Dorian (MJT support), JRL

Post Reply
drunkenmonkey
Pro Scripter
Posts: 90
Joined: Fri Jan 31, 2020 10:52 am

XLGet Active Workbook

Post by drunkenmonkey » Mon Apr 05, 2021 11:44 am

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! :D :D :D

User avatar
Grovkillen
Automation Wizard
Posts: 1128
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: XLGet Active Workbook

Post by Grovkillen » Mon Apr 05, 2021 12:01 pm

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.
Let>ME=%Script%

Running: 15.0.27
version history

drunkenmonkey
Pro Scripter
Posts: 90
Joined: Fri Jan 31, 2020 10:52 am

Re: XLGet Active Workbook

Post by drunkenmonkey » Mon Apr 05, 2021 12:54 pm

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

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLGet Active Workbook

Post by Dorian (MJT support) » Mon Apr 05, 2021 12:56 pm

Just to add to what Grovkillen said, another alternative if the file is already open is XLGet.
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 90
Joined: Fri Jan 31, 2020 10:52 am

Re: XLGet Active Workbook

Post by drunkenmonkey » Mon Apr 05, 2021 4:04 pm

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! :D :D :D

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLGet Active Workbook

Post by Dorian (MJT support) » Mon Apr 05, 2021 6:33 pm

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
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 90
Joined: Fri Jan 31, 2020 10:52 am

Re: XLGet Active Workbook

Post by drunkenmonkey » Mon Apr 05, 2021 7:54 pm

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

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLGet Active Workbook

Post by Dorian (MJT support) » Mon Apr 05, 2021 9:35 pm

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.
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 90
Joined: Fri Jan 31, 2020 10:52 am

Re: XLGet Active Workbook

Post by drunkenmonkey » Tue Apr 06, 2021 10:45 am

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!

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLGet Active Workbook

Post by Dorian (MJT support) » Tue Apr 06, 2021 11:06 am

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?
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 90
Joined: Fri Jan 31, 2020 10:52 am

Re: XLGet Active Workbook

Post by drunkenmonkey » Tue Apr 06, 2021 2:18 pm

Thank you Dorian for your answer. I have managed. :D
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. :) :) :)

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: XLGet Active Workbook

Post by Dorian (MJT support) » Tue Apr 06, 2021 2:54 pm

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%
Yes, we have a Custom Scripting Service. Message me or go here

drunkenmonkey
Pro Scripter
Posts: 90
Joined: Fri Jan 31, 2020 10:52 am

Re: XLGet Active Workbook

Post by drunkenmonkey » Tue Apr 06, 2021 5:05 pm

Thank you Dorian for your comments. Very helpful! :D :D :D

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts