Automate Excel files with native integration using Excel formula in TagUI - done

[kensoh]

First path is trying out embedding sheet.js directly (and credit) to see if that's doable -

https://github.com/sheetjs/sheetjs

[kensoh]

It looks like SheetJS does not ensure preservation of formatting and features like pivot tables. And that improved consistency is only for the paid pro version. In that case SheetJS cannot be an option. More details in below posts - https://github.com/SheetJS/sheetjs/issues/347 https://gist.github.com/SheetJSDev/24b8acd317d01999d721b38de7c53021

It also looks like PHPSpreadsheet does not preserve formatting - https://stackoverflow.com/questions/53787637/will-phpspreadsheet-convert-xls-to-xlsx-and-preserve-formatting

Looking further, Python packages such as openpyxl also do not preserve formatting - https://stackoverflow.com/questions/52878615/how-to-keep-style-format-unchanged-after-writing-data-using-openpyxl-package-in

Looking even further, from Microsoft, they only provide an API for web-based MS Office, which has a limited feature set and not the primary mode of consumption of MS Office documents - https://docs.microsoft.com/en-us/javascript/api/overview?view=excel-js-preview

[kensoh]

In light of above, will explore doing from UI with keyboard shortcuts + visual automation, but with minimal effort from users. For example, explore the following syntax (this is work-in-progress, should be done using familiar concepts to Excel users)

Taking a step back, CRUD (create / read / update / delete) operations can be simplified and unified as moving data from some source to destination. Below examples are a verbose and explicit way of assigning values, but another possible query language for the same operations can be done using 'Excel formula styles' such as total = G20, row_data = 10:10, column_data = F:F, A1:G20 = data_array, A2 = A1. This would be unambiguous provided that variables aren't Excel cells or ranges.

Further exploration notes

--> check numeric labels for Excel, range object, cell object (row_number, column_number), offset object(row, col) --> https://docs.microsoft.com/en-us/office/vba/api/excel.range(object) --> target range can accept not just range but top left corner

1-liners - reading

excel file Sales Report.xlsx
excel sheet June Report
excel cell G20 to total
excel row 10 to row_data
excel column F to column_data
excel range A1:G20 to data_array
excel sheet June Report to sheet_data_array

1-liners - writing

excel 99999 to cell H20
excel cell A1 to cell A2
excel data_array to range A1:G20
excel range A1:B2 to range E2:F3
excel save Sales Report.xlsx

excel block - reading

excel begin
file Sales Report.xlsx
sheet June Report
cell G20 to total
range A1:G20 to data_array
excel finish

excel block - writing

excel begin
99999 to cell H20
cell A1 to cell A2
data_array to range A1:G20
range A1:B2 to range E2:F3
save Sales Report.xlsx
excel finish

[kensoh]

Iterating to VBScript for Windows + AppleScript for Mac - https://www.faqforge.com/windows/run-vbscript-windows/ https://www.idautomation.com/font-encoders/applescript/

Also consider

excel open datatable.xlsx as wb1
excel wb1.sheet as sheet1
excel sheet1.A1 = total_price
excel wb1.sheet1.A1 = wb2.sheet2.A2

excel begin
open file1
goto sheet1
price_table = A1:D5

open file2
goto sheet2
A1:D5 = price_table

goto file1
goto sheet1
price_table2 = E1:H5

goto file2
goto sheet1
E1:H5 = price_table2

wb1.sheet1.E1:H5 = price_table2

alias summary_report = wb1.sheet1
summary_report.A1 = value

excel finish

[kensoh]

excel begin
file --> sheet --> cell/range = variable
variable = file --> sheet --> cell/range
file --> sheet --> cell/range = file --> sheet --> cell/range
excel finish

possible connectors

.
|
-->
=>

no connector is possible too

excel begin
file.xlsx sheet1 A1:B5 = variable
variable = file.xlsx sheet1 A1:B5
file.xlsx sheet1 A1:B5 = file2.xlsx sheet1 A1:B5
excel finish

no connector is possible too

excel begin
file.xlsx sheet1 A1:B5 to variable
variable to file.xlsx sheet1 A1:B5
file.xlsx sheet1 A1:B5 to file2.xlsx sheet1 A1:B5
excel finish

using standard tagui steps

excel begin
open file.xlsx
read sheet1 A1:B5 to variable
type variable to sheet1 A1:B5
type sheet1 A1:B5 to sheet2 A1:B5
save file.xlsx
close file.xlsx
excel finish

moving file operations outside of the step

excel begin
read sheet1 A1:B5 to variable
type variable to sheet1 A1:B5
type sheet1 A1:B5 to sheet2 A1:B5
excel finish

for single sheet workbook or primary sheet

excel begin
read A1:B5 to variable
type variable to A1:B5
excel finish

open c:\desktop\report.pdf
open c:\reports\result.xls

type sheet1 B5 as `total_price`
read sheet1 1:1 to header_array

[kensoh]

References for macOS applescript -

https://macscripter.net/viewtopic.php?id=33220 https://discussions.apple.com/thread/1507812 https://applescriptlibrary.files.wordpress.com/2013/11/excel-2004-applescript-reference.pdf https://developer.apple.com/library/archive/documentation/LanguagesUtilities/Conceptual/MacAutomationScriptingGuide/ManipulateListsofItems.html http://downloads.techbarrack.com/books/programming/AppleScript/website/index.html https://www.idautomation.com/font-encoders/applescript/ https://discussions.apple.com/thread/5298745 http://preserve.mactech.com/articles/mactech/Vol.23/23.02/2302AppleScript/index.html https://discussions.apple.com/thread/8345854 https://apple.stackexchange.com/questions/315658/how-to-open-an-excel-workbook-that-ends-with-a-specific-string-using-applescript https://stackoverflow.com/questions/24345166/applescript-open-excel-file-and-save-in-a-different-format-same-name-and-path

working example that can be invoked with osascript demo.scpt

tell application "Microsoft Excel"
    select worksheet "Sheet1"
--        set value of range "A1" to "testing"
--        get value of range "A1"
end tell

[kensoh]

How the syntax will look like if using Excel standard definition of workbook + sheet + range -

[kensoh]

I think I found the ultimate syntax for this feature. This requires learning 0 additional knowledge to use and super powerful.

variable = [workbook]sheet!range
[workbook]sheet!range = variable
[workbook]sheet!range = [workbook]sheet!range

This lets users move data around as if they are in Excel. 0% TagUI syntax and 100% Excel syntax.

And also, aligned with TagUI's way for assigning variables.

[kensoh]

New ideas and users' suggestions

• [x] run in background so user can continue using PC (crowd poll concluded)
• [x] Excel 2019 version reuse existing Excel session, enable background mode
• [x] fixed 1 of 2 known limitations (accessing col, row with A:A, B:B, 3:3, 1:1)
• [x] extending accessing col, row feature to multiple cols, rows (eg B:D, 3:5)
• [x] added excel_visible option to run Excel invisibly for Windows and Mac
• [x] support for password-protected Excel file with excel_password variable
• [x] use env variables like %USERPROFILE%, added get_env() helper function
• [x] regression issue on mac reading string data (introed with row-col access)
• [x] support reading data from workbooks with cells having commas ', ' in them
• [ ] pending feedback for selection of used range (no such formula in Excel)

Passed below tests for initial release

• [x] write data to sheet range in closed Excel
• [x] write data to sheet range in opened Excel
• [x] read data from sheet range in closed Excel
• [x] read data from sheet range in opened Excel
• [x] [workbook]sheet!range = variable syntax
• [x] variable = [workbook]sheet!range syntax
• [x] [workbook]sheet!range = [workbook]sheet!range
• [x] check dynamic variable in live and normal mode
• [x] repeat with string, number, cell, range as input
• [x] repeat with string, number, cell, range as output
• [x] repeat with assignment of formula, eg "= A1"
• [x] transactions over 30s timeout? to error out
• [x] auto-opening workbook if not already open
• [x] handling multiple excel workbooks in flow
• [x] repeat with absolute & relative workbook path
• [x] repeat with space in workbook and sheet name
• [x] auto-saving Excel workbooks at end of flow
• [x] check assigning 'string' and "string" to excel
• [x] what if file to write does not exist (to create)
• [x] what is file to read does not exist (to error)
• [x] error handling on write invalid sheet (create)
• [x] error handling on read invalid sheet (error)
• [x] error handling 2d != 2d, 1d = 2d, 2d = 1d
• [x] writing column (C1), row (A5), sheet (A1)
• [x] reading columns (A:A, A:B), rows (1:1, 1:2)
• [x] auto-create output range from input array
• [x] can open .xlsx .xls .csv (xl.|xl..|xml|csv)
• [x] error out on wrong OS (only Linux can't)
• [x] repeat all the above in TagUI live mode
• [x] repeat all the above for Windows Excel

Known limitations for initial release

1. reading data from Excel in live mode
   • constrained by live mode not being able to get excel output until live mode is exited
   • due to JavaScript event loop, the reading happens after live mode execution cycle
   • possibly test piping output to a file and then reading from that file to process data

[EmyEtti]

Dear, Sir My name is Imane and I'm a trainee in a Moroccan company named NOVELIS, the company asked me to do some research about your software TagUI and I will be glad if you could answer some of my questions. 1-Does the software contains an orchestrator if yes how it works? 2- How to manage login and the password? 3- Does the Robot's settings exist? 4- Can I add commands and packages by myself? 5- how does the software manage to lag? 6- Does TagUI have interactions with desktop apps and java apps? 7-Any information about the WorkQ

[kensoh]

Thanks Imane for raising these questions, copying @ruthtxh to share her views on these questions.

[ruthtxh]

Hi @EmyEtti00

1-Does the software contains an orchestrator if yes how it works? No, but you can make use of other tools to achieve this For instance:

• Task Scheduler (Windows) to schedule your deployed TagUI workflow
• OpenRPA, OpenFlow or OpenBots, which can orchestrate TagUI

2- How to manage login and the password? You can save to TagUI's Chrome user profile when building your workflow, so that these credentials will be auto populated when you visit the page.

If you want to hide the password you can separate them from your flow file using the concept of object repository in TagUI. See https://tagui.readthedocs.io/en/latest/advanced.html?highlight=object%20repository#object-repositories

You can also use 3rd party password managers.

3- Does the Robot's settings exist? Sorry I don't understand this question, can you elaborate more?

4- Can I add commands and packages by myself? With human language TagUI you can use the following steps - Javascript, Python, R to write code in the respective programming languages. See https://tagui.readthedocs.io/en/latest/reference.html?highlight=custom%20code#custom-code There's also the following "flavours" of TagUI: Python: https://github.com/tebelorg/RPA-Python C#: https://www.nuget.org/packages/tagui Node-RED: https://flows.nodered.org/node/node-red-contrib-tagui

5- how does the software manage to lag? Sorry I don't understand this question, can you elaborate more?

6- Does TagUI have interactions with desktop apps and java apps? Yes, you can do desktop automations using visual automation, which involves taking snapshots of the controls as identifiers. See this example: https://github.com/aimakerspace/TagUI-Bricks/tree/master/MS-Word-Letter

7-Any information about the WorkQ Sorry I don't understand this question, can you elaborate more? If you are referring to work queues for orchestration, then it would depend on how you orchestrate your flows using the suggestions in point 1.

[kensoh]

Thanks @ruthtxh! I have nothing to add other than including OpenFlow and OpenBots into the list of open-source RPA tools that have built-in support to orchestrate TagUI.

[kensoh]

References for porting to Windows -

https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/automate-excel-from-client-side-vbscript https://www.activexperts.com/admin/vbscript-collection/msoffice/excel/ https://www.softwaretestinghelp.com/vbscript-excel-tutorial-11/ https://www.mrexcel.com/board/threads/vbscript-activating-an-excel-window-already-opened.1076542/ https://www.file.net/process/wscript.exe.html https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/cscript https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/wscript https://www.experts-exchange.com/articles/17325/Difference-between-cscript-and-wscript.html https://isvbscriptdead.com

[kensoh]

Excel integration added! See this link for list of cases tested, known limitations to explore, and user suggestions for review.

Usage guide with various examples - https://tagui.readthedocs.io/en/latest/reference.html#excel

---

If you are using TagUI v6.46 and above, you can get this update with tagui update command or MS Word plug-in Update TagUI button. If not, you can get TagUI v6.46 from this installation page before performing the update.

For list of changes since TagUI v.6.46 release in mid-June, see this (link also found at TagUI installation page) - https://github.com/kelaberetiv/TagUI/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+fixed+OR+done+

[kensoh]

Closing since this change has made its way into the latest packaged release.

[Umariqbal6814]

hello sir i am diploma student sir how we find latest data in a particular format