HOW TO CREATE AND EXECUTE MACROS

Introduction to Macros in D4E

A macro (also called script) is a sequence of instructions or actions that can be recorded and executed automatically in Excel to save time and enhance efficiency. D4E provides users with the ability to record, execute, and manage macros within Excel, integrating SQL commands, parameterized operations, and more. This guide explains how to create and execute macros within the D4E framework, providing clear examples and details on how to manage macro execution. 


How to Record, Stop, and Manage Macros in Data Mart for Excel (D4E)

In Data Mart for Excel (D4E), macro recording is seamlessly integrated into the D4E Ribbon, Task Pane, and Command Bar. Users can record, stop, and execute macros easily through buttons and menus, all without needing to interact with code directly. Below is a guide to help you understand how to start, stop, and run recorded macros using the available D4E functionality.

1. Starting the Macro Recording

There are two options for starting macro recording in D4E, depending on whether you want the actions to be executed immediately or just recorded for later use.


1.1 Record Macro with Execution
Button Name: Record Macro while Executing
Where to Find It:
D4E Ribbon: Under the D4E Macros group.
D4E Task Pane: In the D4E Macros section.
Action: Clicking this button will start recording your D4E menu actions in Excel and immediately execute them. For example, if you extract SQL data  and enter placeholder parameter,  those actions will be recorded and executed in real time as part of the macro.


1.2 Record Macro Only (No Execution)
Button Name: Record Macro Only (No Exec)
Where to Find It:

Command Bar: Add-ins Tool Bar
D4E Ribbon: Under the D4E Macros group.
D4E Task Pane: In the D4E Macros section.
Action: Clicking this button will start recording your actions but will not execute them immediately. This is useful if you want to record a series of actions for later use without having them run as you record them.


2. Stopping the Macro Recording


To stop the macro recording session and save the recorded actions, you can use the Stop Macro Recording button.


2.1 Stop Macro Recording
Button Name: Stop Macro Recording
Where to Find It:

Command Bar: Add-ins Tool Bar
D4E Ribbon: Under the D4E Macros group.
D4E Task Pane: In the D4E Macros section.
Action: Clicking this button will stop the recording session and save all recorded actions as a macro. The macro will be saved within the workbook, and you can choose to run it later at any time.


3. Running a Recorded Macro
Once the macro is recorded and saved, you can run it using the Run This Macro button.


3.1 Run This Macro
Button Name: Run This Macro
Where to Find It:

Command Bar: Add-ins Tool Bar
D4E Ribbon: Under the D4E Macros group.
D4E Task Pane: In the D4E Macros section.
Action: Clicking this button will execute the recorded macro that was previously saved in the workbook. The macro will carry out all the actions you recorded, such as entering data, formatting, and any other operations you performed.


4. Key Buttons and Their Functions
Here’s an overview of the buttons you’ll interact with in the D4E Ribbon and D4E Task Pane for managing macro recordings:
Record Macro while Executing: Starts recording your actions and immediately executes them.
Record Macro Only (No Exec): Starts recording your actions without executing them right away.
Stop Macro Recording: Ends the recording session and saves the recorded actions as a macro.
Run This Macro: Executes the macro that was previously recorded and saved.
These buttons can be accessed from both the D4E Ribbon and the D4E Task Pane, providing an easy and intuitive way to record, stop, and run macros in Excel.


5. Summary of Button Locations
D4E Ribbon:
Record Macro while Executing
Record Macro Only (No Exec)
Stop Macro Recording
Run This Macro
D4E Task Pane:
Record Macro while Executing
Record Macro Only (No Exec)
Stop Macro Recording
Run This Macro
These buttons offer a seamless experience for recording, saving, and executing macros in Excel without requiring any code interaction.
This method of controlling macros through the D4E Ribbon, D4E Task Pane, and Command Bar ensures that users can efficiently automate tasks and manage macro recordings within Excel, all through intuitive buttons and simple interactions.


6. Running Macro Commands in the Task Pane

In the Excel Task Pane, there is a macro box where the user can type or paste macro commands. The user can click on Submit button to execute the macros.  The log window will show immediately any log messages, after execution of the macros.





LIST OF MACRO COMMANDS AVAILABLE


Macro commands are presented below grouped by function:


1. SQL Execution Commands


These commands handle SQL execution and manipulation.
add1sqlfile: Adds an SQL file to the D4E system.
          Description: Adds the specified SQL file to the tool's store, which can be used for future executions.
get: Executes an SQL query from a specified sheet and column.
          Description: Runs the SQL query from a particular sheet using predefined column parameters.
getdatatable: Executes an SQL query to retrieve data and populate a table.
          Description: Executes SQL and imports the result into an Excel sheet as a table.
getdatatablev: Executes a versioned SQL query to retrieve data.
          Description: Retrieves SQL query results from a specified version of the query.
runsql: Executes an SQL query from the active sheet based on the version title.
          Description: Runs a query directly from the active sheet and logs its execution.
runsqlkeep: Executes an SQL query and keeps the results.
          Description: Similar to runsql, but this keeps the result in the sheet for future reference.
runsqlv: Executes a versioned SQL query.
          Description: Runs a versioned SQL query, allowing users to track and execute specific query versions.
runsqlvkeep: Executes a versioned SQL query and keeps the results.
          Description: Like runsqlv, but retains the query results.


2. File and Directory Commands

These commands manage file operations and directories.

addsqlfile: Adds an SQL file to the system for future execution.
          Description: Allows users to add SQL files into the system to be executed later.
opensqlfile: Opens an SQL file by its full path.
          Description: Opens a specific SQL file, if it exists.
setdir: Sets the directory for file operations.
          Description: Assigns a directory for file-related operations.
setfolder: Sets the folder for storing or retrieving files.
          Description: Assigns a folder path for file management tasks.
settempdir: Sets the temporary directory for files.
          Description: Defines a temporary directory path for file operations.
settempfolder: Sets a folder for temporary files.
          Description: Assigns a specific folder to handle temporary files.


3. Macro Execution Commands


These commands handle running macros and macro sheets.
RunMacroSheet: Executes macros from a specified sheet.
          Description: Runs the macro commands stored in a macro sheet.
runmacro: Executes a macro command.
          Description: Runs a specific macro command defined in the worksheet.


4. Parameter and Logging Commands


These commands manage parameters and logging of activities.
clearparamlist: Clears the list of parameters.
          Description: Resets or clears any stored parameters from the global list.
logmsg: Logs a message with multiple tokens.
          Description: Concatenates a message using multiple tokens and logs it in the system.
logrows: Sets the number of rows in the log.
          Description: Defines how many rows of logs should be kept.
setparam: Defines parameters for SQL queries or macros.
          Description: This command is used to assign parameters to SQL queries or macros dynamically.
setprompt: Processes parameter prompts.
          Description: Handles the prompting for parameters before running SQL or macros.


5. Task and Scheduler Commands


These commands manage the scheduling and execution of tasks.
stopscheduler: Stops the task scheduler.
          Description: Pauses or stops any scheduled tasks.
startscheduler: Starts the task scheduler.
          Description: Initializes and starts the scheduler for task automation.
validatetasks: Validates scheduled tasks.
          Description: Checks if the scheduled tasks are correctly set up.


6. Utility and Miscellaneous Commands


These are general utility commands and others that don’t fall into the above categories.
about: Displays information about the tool.
          Description: Shows a brief summary or "About" information about the tool.
aboutinfo: Displays detailed "About" information.
          Description: Provides detailed information on the version, developer, and features.
clearaddin: Clears the add-in configuration.
          Description: Resets or clears the add-in data.
clearstore: Clears stored data in the tool.
          Description: Resets stored settings or data related to the tool.
cleanup: Cleans up the add-in environment.
          Description: Resets the tool to its default state and removes any temporary data.
help: Provides help information for macro commands.
          Description: Displays information about available commands and their usage.
initmenuflow: Initializes the menu flow for the tool.
          Description: Sets up the navigation and menu structure within the tool.
rem: Comment placeholder.
          Description: No action is taken; used to comment out lines or provide inline documentation.
runmacro: Executes a macro.
          Description: Runs a specific macro based on user-defined input.


7. Shell and Command Execution Commands


These commands execute external commands.
shell: Executes a system shell command.
          Description: Runs a specified shell command.
shellcmd: Executes a system command line command.
          Description: Uses the cmd command line to run a specified shell command.


8. Silent Mode and Quiet Mode Commands


These commands control the behavior of parameter prompting during execution.
quietmode: Toggles the quiet mode for silent execution.
          Description: Disables the prompt for parameters, running commands silently.
silent_mode: Enables or disables silent mode for running commands without prompts.
          Description: Runs commands without asking for any user input, ideal for automated execution.
silentmode: Enables silent execution without prompts.
          Description: Similar to silent_mode, it runs macros or SQL commands silently.


9. Miscellaneous File Handling Commands


Commands related to document management and file operations.
showdocs: Displays document sheets in the workbook.
          Description: Shows or hides sheets that are used for documentation purposes.
unloadaddin: Unloads the add-in and optionally saves the workbook.
          Description: Exits the add-in, optionally prompting the user to save the workbook.


Summary


SQL Execution: Handles SQL query execution, with options for keeping results or executing versioned queries.
File and Directory: Manages files, directories, and folders for storing and opening SQL files and related assets.
Macro Execution: Executes stored macros from specific sheets or commands, offering a flexible way to trigger actions.
Parameter and Logging: Allows for dynamic parameter setting and logs the execution of commands for future reference.
Scheduler and Task Management: Provides scheduling functionality to automate tasks, including validation and stopping tasks.
Utility Commands: Offers general utility for displaying information, cleaning up, and resetting the system.
Shell and Command Execution: Executes system commands and shell instructions for advanced operations.
Silent and Quiet Mode: Enables silent or quiet execution of commands, bypassing prompts for parameter inputs.
Miscellaneous Commands: Includes tasks for managing documents, unloading the add-in, and managing shell commands.
Each group above serves a specific function that contributes to the overall usability of D4E, making it highly customizable and flexible for various SQL, macro, and file management tasks.



SORTED LIST OF COMMANDS

about - Display about information, activates/refreshes Excel Taskpane.
aboutinfo - see about.
add1sqlfile - see addsqlfile.
addsqlfile <fullfilename> - Add an SQL file to the D4E store and activates RUN Mode.
cleanup - Reset status of the add-in.
clearaddin - Clear the data in add-in or store.
clearlog - Clear contents of log (menulog).
clearparamlist - Clear parameters in param_list.
copyto <sheet> - Copy last SQL output to <sheet> which is used by a pivot table/report.
createsendfile <email>,<report_title>,<file> - Prepare output results for sending by email.
createsendsheet <email>,<report_title>,<sheet> - Prepare report sheet for sending by email.
encode <text> - Encode a value using a D4E encoding scheme.
excelvba <file!VBAmacro> - Run VBA code.
formatsql - Paint/format all SQL code in sheets.
get - see runsql.
getdatatable - see runsql.
getdatatablev - see runsqlv.
getinbound <email> - Read macro commands from email u@cmpy.com or @cmpy.com
getmenu - Create menu sheet and menu commandbars.
help <cmd/searchkey> - Display help on <cmd/searchkey>.
initmenuflow - Make menuflow from SQL sheets.
logmsg <msg> - Log a message.
openfile - see opensqlfile.
opensqlfile <file> - Open SQL file for EDIT Mode.
prepareupdate - Prepare activesheet to have Action Code column.
quiet_mode - see silent_mode.
quietmode - see silent_mode.
reademailmacro - see getinbound.
refreshpivots <sheet> - Refresh pivot tables/reports.
rem - This is a comment line.
removesheets - Delete output sheets (Sheets???).
runmacro <sheet> - Run macro command from sheet.
runquiet - see silent_mode.
runsilent - see silent_mode.
runsql <sheet> <colummABC> - Execute an SQL query found in column X of sheet.
runsqlkeep <sheet> <columnABC> - Execute a runsql command but keeps the results.
runsqlv <sheet> <version_title> - Execute SQL in column X, with version_title in row 1.
runsqlvkeep <sheet> <version_title> - Execute a runsqlv command but keeps the results.
sendoutbound - Send output results to email.
setdir - see settempdir.
setfolder - see settempdir.
setlogrows <maxcount> - Set max log entries to display.
setparam <param>=<value> - Set a value for the placeholder and add to param_list.
setprompt - see setparam.
settempdir <fullfoldername> - Set/create the folder name for temporary output files.
settempfolder - see settempdir.
shell <cmd> - Execute a shell command.
showdocs - Show sheet named 'docs.*' .
silent_mode <ON/OFF> - ON: use internal param_list instead of user prompting.
silentmode - see silent_mode.
startscheduler - Start the scheduler.
stopscheduler - Stop the scheduler.
unloadaddin - Remove D4E addin from session.
update - see updatedb.
updatedb <sheet> - Update database table from sheet rows with Action code.
updatedbfile <filename> - Update database table from pathfilename rows with Action code.
updatefile - see updatedbfile.
validatetasks - Validate all tasks.