VBA APIs to open/close/update forms
TGK_OpenTemplate
| Element | Description |
|---|---|
| Syntax | dim wb as workbook set wb = Application.run("Tgkrmacro.xlam!TGK_OpenTemplate", "REPORT_CODE") |
| Description | Opens the form in design mode. |
| Mandatory parameters | REPORT_CODE: Code identifying the form to be opened |
| Value returned | Workbook associated with the open form. If the form has not been opened correctly, it returns nothing (e.g. if the code is non-existent or the user does not have permission to open the form in Design mode). |
TGK_Navigate
| Element | Description |
|---|---|
| Syntax | dim wb as workbook set wb = Application.run("Tgkrmacro.xlam!TGK_Navigate", "REPORT_CODE", ["PROMPT_FILTERS"], ["PARAMETERS"]) |
| Description | Opens the form in navigation mode. |
| Mandatory parameters | - REPORT_CODE: Code identifying the form to be opened |
| Optional parameters | - PROMPT_FILTERS: Only present for compatibility with forms created using the VBA client - PARAMETERS: Parameters to be used when the form is run. Must be defined according to human-readable syntax. If omitted, the parameters are requested when the form is run. |
| Value returned | Workbook associated with the open form. If the form is not opened correctly, it returns nothing (e.g. if the code is non-existent or the user does not have permission to open the form in navigation mode). |
Example
Example of syntax for running the form with code "PROSP01" in navigation mode, based on the following parameters:
- entity A01
- node N1 of hierarchy 01 of custom dimension 1
- parametric scenario (SP) 2004C
- parametric period (PP) 12.
dim wb as workbook
set wb = Application.run("Tgkrmacro.xlam!TGK_Navigate", "PROSP01", "Entity=A01;
TGK_DataEntry
| Element | Description |
|---|---|
| Syntax | dim wb as workbook set wb = Application.run("Tgkrmacro.xlam!TGK_DataEntry", "REPORT_CODE", "PROCESS_CODE", ["PROMPT_FILTERS"], ["PARAMETERS"]) |
| Description | Opens the form in data entry mode. |
| Mandatory parameters | - REPORT_CODE: Code identifying the form to be opened - PROCESS_CODE: Code identifying the process on which data collection is performed |
| Optional parameters | - PROMPT_FILTERS: Only present for compatibility for forms created using the VBA client - PARAMETERS: Parameters to be used when the form is run. Must be defined according to human-readable syntax. If omitted, the parameters are requested when the form is run. |
| Value returned | Workbook associated with the open form. If the form is not opened correctly, for example the code is non-existent or the user does not have permission to open the form in Data Entry mode, it returns nothing. |
TGK_OpenReportFile
| Element | Description |
|---|---|
| Syntax | dim wb as workbook set wb = Application.run("Tgkrmacro.xlam!TGK_OpenReportFile", "FILENAME.xls") |
| Description | Opens an ad hoc form or a form opened in navigation mode and then saved as an Excel sheet. |
| Parameters | FILENAME: full XLS Path of the file to be used e.g. "C:\Documents and Settings....\myReport.xls" |
| Value returned | Workbook associated with the open form. In the following cases, it returns nothing: - the file does not exist or is locked, or does not have read permission - user does not have permission to open ad hoc forms - the specified Excel file is not a valid form |
TGK_CloseMySelf
| Element | Description |
|---|---|
| Syntax | Application.run("Tgkrmacro.xlam!TGK_CloseMySelf", WORKBOOK, [ASK_SAVE], [JUST_CLOSE]) |
| Description | Closes the current form (or the form containing the macro that calls up this function). |
| Mandatory parameters | - WORKBOOK: Workbook object corresponding to the form to be closed. |
| Optional parameters | - ASK_SAVE: the system only considers this parameter if the JUST_CLOSE parameter is FALSE. If it is TRUE, the system checks whether any changes have been made and asks the user if they want to save. If it is FALSE or omitted, the system saves and closes the form without displaying any messages. - JUST_CLOSE: if TRUE, it closes the form without saving or asking for confirmation. If FALSE or omitted, the parameter ASK_SAVE is taken into consideration. |
| Value returned | None |
Example
Example of syntax for closing the form corresponding to the Excel document containing the macro, which asks for confirmation before saving data.
Application.run("Tgkrmacro.xlam!TGK_CloseMySelf", ThisWorkbook, true)
TGK_CloseOtherDocuments
| Element | Description |
|---|---|
| Syntax | Application.run("Tgkrmacro.xlam!TGK_CloseOtherDocuments", WORKBOOK, [ASK_SAVE]) |
| Description | Closes a different document from the one in which the macro is written. |
| Mandatory parameters | WORKBOOK: Workbook object corresponding to the form to be closed. |
| Optional parameters | ASK_SAVE: If TRUE, the system checks whether any changes have been made and asks the user if they want to save. If it is FALSE or omitted, the system saves and closes the form without displaying any messages. |
| Value returned | TRUE if the form was closed correctly; FALSE otherwise. |
TGK_RunUnprotected
| Element | Description |
|---|---|
| Syntax | dim bResult as |
| Description | In all cases in which the form was opened in data entry mode, and is therefore protected, this allows you to run the specified macro by removing the protection from the sheet before running it and potentially protecting again at the end. |
| Mandatory parameters | - WORKBOOK: Workbook object corresponding to the form containing the macro to be run - MACRO_NAME: Name of the macro to be run. Must be a function present on a public module and belonging to the specified workbook. |
| Optional parameters | MACRO_PARAM_1,...,MACRO_PARAM_10: Any input parameters of the macro to be run. |
| Value returned | The macro name, as indicated in "MACRO_NAME" |
Example
This example shows how to hid a specific row of a form, for example row 25, in a form containing a macro that hides the past row processed as an argument.
The details of the macro are provided below:
Public Function nascondiRiga(numRiga As Integer)
ActiveSheet.Rows(numRiga).Hidden = True
nascondiRiga = True
End Function
To hide row 25, the following syntax must be used:
dim bResult
bResult = nascondiRiga(25)
When the system runs this macro on a protected sheet, it returns the following error: "Impossible to set the 'Hidden' property for the 'Range' class". It must therefore be run with the sheets not protected, using the following syntax.
dim bResult
bResult = Application.run("Tgkrmacro.xlam!TGK_RunUnprotected", "nascondiRiga", 25)
The system closes the form corresponding to the Excel document containing the macro and asks for confirmation before saving data.
TGK_RunUnprotectedObj
| Element | Description |
|---|---|
| Syntax | dim objResult as |
| Description | The same as TGK_RunUnprotected, but to be used when the called macro returns an object rather than a primitive type. |
| Mandatory parameters | - WORKBOOK: Workbook object corresponding to the form containing the macro to be run - MACRO_NAME: Name of the macro to be run. Must be a function present on a public module and belonging to the specified workbook. |
| Optional parameters | MACRO_PARAM_1,...,MACRO_PARAM_10: Any input parameters of the macro to be run. |
| Value returned | The macro name, as indicated in "MACRO_NAME" |
TGK_Save
| Element | Description |
|---|---|
| Syntax | dim bEsito as boolean bEsito = Application.run("Tgkrmacro.xlam!TGK_Save", WORKBOOK, [FILE_NAME]) |
| Description | Saves the file related to the specified form. Equivalent to Save in the button bar. |
| Mandatory parameters | WORKBOOK: Workbook object corresponding to the form to be saved. |
| Optional parameters | FILE_NAME: If specified, the system saves the form with the name indicated without asking for further confirmation. Otherwise, it follows the normal save Excel file request, i.e. it saves the changes to the current file or, if the document is a new document, asks the user for the name to be assigned to it. |
| Value returned | TRUE if the form was saved correctly; FALSE otherwise. Do not use the ThisWorkbook.save command within a macro. Replace with TGK_Save. TGK_Save with automatic refresh linked to saving may not function correctly. Disable this option and check that there is no linked data processing that requires it. |
TGK_Refresh
| Description | |
|---|---|
| Syntax | dim wb as workbook set wb = Application.run("Tgkrmacro.xlam!TGK_Refresh") |
| Description | Refresh the current form, already opened and run. |
| Value returned | Workbook updated. Note: Dynamic forms will be closed and reopened in the refresh phase. Therefore, it is likely that any rows of codes subsequent to the TGK_Refresh function call will never be run, as the file is closed during the refresh. The report is closed and reopened in the refresh phase. It is therefore necessary to check that the workbook returned by TGK_Refresh is not Nothing even if any rows subsequent to the call will not be run. |
TGK_CountAttachments
| Element | Description |
|---|---|
| Syntax | Application.run("Tgkrmacro.xlam!TGK_CountAttachments", cell) =TGK_CountAttachments({cella}) |
| Description | Returns the number of attachments present in a cell or range of cells. Function supported in both Excel and Web. This function can also be used as a spreadsheet formula. |
| Mandatory parameters | CELL. - Single cell or range whose number of attachments is to be retrieved (if used within the form) - Column/row whose number of attachments is to be retrieved for each of its cells (if inserted in a formula within the Matrix Wizard) |
| Value returned | Number of attachments inserted in a cell or range of cells. Note: To display the number of comments entered on a cell using this function, the option Automatically display comments in reports data entry must be enabled (Admin Home Page > Settings > form tab > Comments section). See Settings window. |