You can set up FinJinni to either refresh existing queries in a workbook or to run new queries automatically. Several features are available for this purpose:
- The Query Builder program:
The query builder can be run as a separate program. It is called “FinJinniSqlBuilder.exe” and you will find it in the install folder, usually “C:\Program Files (x86)\GypsyBI\FinJinni Professional\Bin”. If you have the 64-bit version of Excel, use the version of this program in the Bin\X64 subfolder.
Run this program with one or more of the following arguments:
/ex extract-name
The FinJinni extract name used to identify the database. This is required.
/in input-file
An input workbook to be modified.
/out output-file
An output workbook or text file to be created. For text files, the extension must be .txt, .csv, or .xml. For Excel workbooks, if you omit the “/in” option and the “/out” file already exists, it will be used as the input file and modified then saved. Or you can omit this option and supply the “/in” option, in which case that workbook will be modified and saved.
/ref
Refreshes all queries in the input workbook.
/q “query name”
The name of a new query to be executed and either added to the workbook or written as a text file.
/date “date choice”
The date choice to be used for the query. This is a name from the date drop-down, such as “year to date”. It can also be a custom date range such as “01/01/2021:03/31/2021” (use colon as the separator).
/company “id”
The company selection to be used for the query if multiple companies are loaded.
/tabname “name”
An Excel tab name to be assigned.
/unlink
Unlink all queries in the workbook.
Note: Either the “/ref” or the ‘/q’ option must be provided for the program to have any effect.
Examples:
- To read an input workbook, refresh all queries, and save as an output workbook with queries unlinked from their data source, use:
/ex MyExtract /in c:\temp\Input.xlsx /out c:\temp\Output.xlsx /ref
/unlink
- To create an output workbook with a balance sheet, use:
/ex MyExtract /out c:\temp\newbook.xlsx /q “balance sheet as of date”
/date “today”
- To create a CSV file with all sales transactions for the current month, use:
/ex MyExtract /out c:\temp\newfile.csv /q “sales detail for date range”
/date “current month”
- Build your own Excel macros:
Use the macro function “FJ.REFRESH” to refresh queries on a workbook. From VBA, the code to do this is:
RC = Application.Run(“=FJ.REFRESH”, TRUE)
The parameter “TRUE” will refresh all worksheets in the workbook. If FALSE is supplied instead, only the active worksheet is refreshed.
A second function “FJ.UNLINK” can be used to unlink queries from their data source, usually prior to distribution. From VBA, the code to do this is:
RC = Application.Run(“=FJ.UNLINK”, TRUE)
The parameter “TRUE” will unlink all worksheets in the workbook. If FALSE is supplied instead, only the active worksheet is unlinked.