New – One Click install

/0 Comments/in /by

We have changed the download process for PersisTables. You can now download directly from the website here. The installation package will be downloaded by your chosen browser and opening it will cause the auto-extract and install process to start. There is a chance that your anti-virus software will object, you should enable the download process and a CMD window will open. If Excel is already running it will ask you to close down Excel so the install process can proceed.

The package will prompt you for permission to install. You will require Admin privileges currently (a Non Admin variant is available on request). Once you have agreed to proceed with the installation, the standard windows msi installer runs asking you some questions and then proceeds to set up the software. Uninstalling is the same process except the installer will give you the option to Repair or Uninstall.

Once installed you will be able to open Excel and access the PersisTable toolkit directly from a worksheet, further details on installation can be found here.

Note that the software is developed by Kulakor Limited and distributed by Persis Solutions Limited. You will see that the package has been verified as being published by Kulakor Limited

SQL Server Storage

/0 Comments/in /by
Read more

My Table Handles are not working

/0 Comments/in /by

When you open a spreadsheet which contains Table Handles, they need to be recalculated before they can be used. The Table Handles represent data captured from the spreadsheet or loaded from the PersisTables store. To load the data into the Handle, the formula they contain needs to be recalculated.

In this trivial example the yellow range is the original data which is captured inthe ‘Data’ Handle and then displayed in the Green area. G10 shows the current time using Now() while The Handle contains the time it was last calculated.

Closing and reopening the spreadsheet shows the same data and Handle time but Now() has been updated

If we try and recalculate the data in the green area it all goes wrong

The Handle is stale because it has not been recalculated since it was loaded.
The answer is to regenerate the Handle either by pressing [F2] followed by [Enter] or recalculating the whole workbook using the key combination [CTRL][SHIFT][ALT][Enter] together.

We can see that the timestamp on the Handle now matches the current time showing it is up to date. We can now manipulate the Table data as before.

Using Array Formulae

/1 Comment/in /by

Excel functions are great when they have to return a single value, SUM(), MAX(), LEFT() all return the results of their processing into the cell the formula is entered in. What happens though when the formula needs to return multiple results?

The Array Formula is designed to handle just such a situation. It allows the results to be spread over a range of cells, so each cell contains one of the results.

The PersisTables Toolkit makes use of Array formulae to unpack the information held in a Table. The Table can hold many values and can be accessed through the Table Handle returned to the cell the formula generating the Table is entered in.

In this simple example the Range of cells B4:C9 is captured in the “powers” Table

The data can be extracted using the TKTable_Get() function.

However on its own we just see the top left value. To extract the full set of data we create an Array Formula by selecting the range we want to extract the data to and then pressing the [F2] key on the top left cell. This displays the TKTable_Get() formula

Now pressing the [CTRL][Shift] and [Enter] keys together creates the Array Formula

The braces in the formula bar indicate the cell highlighted (E6) is part of an Array formula.

 

CSV in one formula

/0 Comments/in /by

In an earlier blog post I discussed how PersisTables simplifies the creation of a CSV file. In the post I created a Table and then used the TKTable_Save() function to save it away.

One of the powerful features of PersisTables is that a Table and an Excel Range are interchangeable. So any function that accepts a Table Handle as input can also accept a Range of cells. This means that a CSV file can be created in one function.

The Range highlighted is saved directly as a csv file named “powers” in the FastSave Genre.

Similarly a file can be loaded and displayed in a single formula. Normally we enter the TKTable_Load() formula in a single cell and get a Table Handle back.

However wrapping the function in the TKTable_Get() function and turning the results into an Array formula we can display the whole Table immediately.

Using the Fill argument in TKTable_Get() allows the ugly #N/A to be replaced by an empty string or other suitable characters.

 

What version of PersisTables do I need?

/1 Comment/in /by

There are two versions of the PersisTables installer package, designed to work with 32 bit or 64 bit versions of Excel. This should not be confused with using 32 bit or 64 bit Windows versions (all versions of Windows beyond Windows 7 are 64 bit).

To check which version is required open Excel, click on the File->Account menu which will open the Account window (in Windows 2010 and beyond)

Click on the ? About Excel button which displays the EULA, in the top left you will see the installation type

 

When will my license expire?

/0 Comments/in /by

The simplest way to find the expiry date is to open the Persistence Viewer by right clicking on an empty cell and selecting it.

The TKTable_Version() function also provides details on the installation including the expiry date for the current license.

Output from TKTable_version() function

Here the contents of the version table has been expanded using TKTable_Get(). The License Expiry column holds the date the license expires. This is an Excel representation of a date which can be formatted by selecting the cell containing the expiry value and pressing [Ctrl]#