Scripting

PowerTSM also allows access to time series data using VBA macros.

In order to use these functions, make sure to reference HAKOM.TSM.App.COM.tlb from the applications installation directory in your macro.

Getting Started

Registering the Library

In order to get started, the COM library that exposes methods to VBA must be registered. When installing the PowerTSM App using the provided installer, this will be done automatically.

When using the portable installation, this must be done manually:

Locate regasm.exe.
This file can usually be found under %SystemDrive%\Windows\Microsoft.NET\Framework64\<latest version>\
Execute regasm.exe with the following parameters:
regasm.exe /codebase /tlb <bin folder of downloaded files>\HAKOM.TSM.App.COM.dll
The previous step generates a HAKOM.TSM.App.COM.tlb file which can then be referenced in your macros.

Referencing the library

In Excel, open the Visual Basic editor on the Developer Tools ribbon.
In the toolbar, under Extras → References, select Browse to add the HAKOM.TSM.App.COM.tlb file.
The list of references should now include an entry for HAKOM PowerTSM

You may now create an instance of the TSMConnector class in your macro and use any of the available functions.

Available Functions

Loading Data

The following code-snippet demonstrates how to use the methods to read from the database and load the data into the active sheet.

VB

Sub Read()
 Dim connector = New TSMConnector      'Time Series Manager object
 Dim rc As Variant                     'Holds data returned from the function
 
 'read values between 1.1.2008-2.1.2008 in hourly resolution(3600 seconds)
 rc = connector.LoadData(Application.ActiveWorkbook, #1/1/2008#, #2/1/2008#, 3600)
End Sub

This function has the following parameters

Parameter

Default

Description

Workbook

N/A

The workbook to use. Usually either Application.ActiveWorkbook or ThisWorkbook

From

N/A

The beginning of the period for which data should be loaded.

To

N/A

The end of the period for which data should be loaded

Raster

N/A

The interval size to use. May be given as either

a positive integer, denoting the time (in seconds) between individual data points
a negative integer representing one of the follwowing intervals:

Value	Interval
-1	quarter hour
-2	half hour
-3	hour
-4	day
-5	week
-6	month
-7	quarter
-8	year
-9	none
-10	half year
-11	total

MissingEmpty

False

When True, causes values that are flagged as “Missing” to not be loaded into the sheets cells at all. By default, missing values are instead loaded as 0.0 values and marked in red, indicating their missing status.

Units

None (empty string)

When a unit is given, an attempt will be made to convert the underlying data into this unit.

AllSheets

False

Wether or not to process all sheets of the workbook, or just the active one (default)

Descending

False

By default, data is read in ascending order (i.e. the earliest data within the period on top). By setting this parameter to True this order is reversed.

Saving Data

VB

Sub Write()
 Dim connector = New TSMConnector      'Time Series Manager object
 Dim rc As Variant                     'Holds data returned from the function
 'save the current workbook
 rc = connector.SaveData(Application.ActiveWorkbook)
End Sub

Parameter	Default	Description
Workbook	N/A	The workbook to use. Usually either `Application.ActiveWorkbook` or `ThisWorkbook`
AllValuesValid	`True`	When `True`, causes all values to be written with the Valid flag, regardless of how they are marked in the worksheet.
Rollout	`False`	Whether or not to continue writing data beyond the sheets ranges Note that the Rollout feature is not available when accessing time series via an HTTP connection (i.e. the PowerTSM cloud service).
RolloutDays	0	When `Rollout` is set to `True`, this parameter controls how many days into the future valus will be continued
AllSheets	`False`	Wether or not to process all sheets of the workbook, or just the active one (default)

Important Notes Regarding Authentication

When accessing time series via an HTTP connection such as the PowerTSM cloud service, the connection must use the authentication type: ApiKeys. Interactive authentication methods are not available in VBA macros.