Macro for TestCase Design (TCD) Export to Excel
This article is about an Excel Macro which is used to let your TCD Export look fancy.
Business users want to know which business data they actually use within their test cases.
Since business users probably do not have access to Tosca, you need to export the TestSheets to several Excel sheets.
To be able to run the Macro, simply follow those steps:
- Copy Macro to Directory %appdata%\Microsoft\Excel\XLSTART
- Open an empty Excel
- Copy and paste each TestSheet to a separate Excel sheet using "Copy table to clipboard"
Be sure no instances and additional columns (e.g. Filter) are shown!
- Run Macro
- Save Excel
- Hand Excel out to business users
Macro functionalities are in specified order:
- Reset all worksheet names
- Activate worksheet if sProofCell is not empty
- <if activated | default FALSE> color whole Excel sheet if invalid text exists
- Align everything to left
- Clear row 1
- Set Font to Bold for first 2 columns
- Set Font to Bold for testcase row (row 2)
- Resize structure columns to width 1.3
- Resize last structure column to width 55
- Resize data columns to AutoFit (max width 80)
- Change row color of Precondition
- Change row color of Process
- Change row color of Verification
- Change cells including sTextSearchString (Default: TODO)
- Freeze pane for structure columns and testcase row
- Rename worksheet
- Go to next worksheet
If necessary you can modify the Macro according to your needs.
All relevant Information are stored as global variables:
Const sProofCell As String = "A2" | If this Cell is empty, the Macro is not executed on this Excel sheet
Const lColorVerification As Long = 5296274 | 5296274 = Green
Const lColorProcess As Long = 15773696 | 15773696 = Blue
Const lColorPrecondition As Long = 14277081 | 14277081 = Grey
Const lColorSearchString As Long = 65535 | 65535 = Yellow
Const lColorInvalidName As Long = 16764159 | 16764159 = Pink
Const sTextVerification As String = "Verifikation" | Row with this exact text will be colored as defined in lColorVerification
Const sTextProcess As String = "Prozessschritt" | Row with this exact text will be colored as defined in lColorProcess
Const sTextPrecondition As String = "Vorbedingungen" | Row with this exact text will be colored as defined in lColorPrecondition
Const sTextSearchString As String = "*TODO*" | Cell with this text will be colored as defined in lColorSearchString
Const sTextInvalidName As String = "<Text>" | Excel sheet with this exact text will be colored as defined in lColorInvalidName