A set of VBA, TypeScript and Python scripts useful for daily Excel automation projects, and Excel template files to demonstrate specific use cases of excel formulas and features.
Each file type correspond to a specific type of script or template :
- VBA scripts which are only compatible with desktop versions of Excel. These scripts are written in Visual Basic language and stored in ".bas" text files.
- Office scripts which are only compatible with web versions of Excel and some desktop versions (require consistent internet connection and that file is located on OneDrive). These scripts are written in TypeScript language and stored in ".ts" text files.
- Template or example excel files are compatible (with some limitations) with both Excel desktop and Excel for the web. These files have a ".xlsx" (for examples) or ".xltx" (for templates) extension and are a zip archive of XML files, so make sure to download file to preview it.
- Python scripts to easily automate files from outside. These scripts are stored in ".py" text files. Python is a free, easy and widespread programming language, with built in file management librairies.
- VBA scripts : ".bas" files must be imported from the VBA IDE (from Excel desktop : alt + F11 > File > Import file).
- Office scripts : text contained in ".ts" files must be copy-pasted in the Office Scripts IDE. Please note that, the Office scripts are stored here in ".ts" format and would require to be restructured to comply to ".osts" format (not only renamed).
- Template or example Excel files : open the file with Excel.
- Python scripts : ".py" files must be run from a command line interface where the python programming language is installed.
The scripts available on this repository are listed and explained below.
related file : GatherSheetsSummary.bas
For each sheet in a workbook, gather a range located always in the same cell on each sheet. This range contains e.g. a summary of data contained in the active sheet. So that the juxtaposition of ranges makes a summary of the whole workbook.
related file : ArrayForm2Database.ts
Build and update a database based on an array form. More precisely, users fill the array form, and a script organize the data in a database (Excel table), which enables Pivot Tables or Power Platform usage of this data.
related file : PivotTableDataDisplay.xlsx
Excel pivot table feature obliges user to agregate data (using a sum or other functions), which is something impossible with text values or other specific types.
related file : osts2ts.py
Converts all non easy to read .osts files to readable .ts files located in the same folder as the python script, and save them in a target folder of your choice. An OSTS file is created when saving an Office script to Onedrive, but it is saved as a JSON structure. This script helps converting this file to a "code editor friendly" file.
related folder : ExcelFormulaParser
Enables versioning of Excel formulas, writing a formula on multiple lines and adding comments. The parsing process does the following :
- python-like comments management : comment is recognized as a line which begin with "# " (hastag followed by space)
- line breaks and spaces remove
- create one line per formula : formula is detected as a line break followed by "=" (equal) sign
How to use :
- add your formula to excel_formula_in.txt
- run parser.py
- get result in excel_formula_out.txt
Known limitations :
- variable name containing space not supported
- unable to make difference between formula begin and "=" sign in formula body
related file : litterature-review.xltx
An Excel template with some simple useful formulas, to stay organized when doing a litterature review.