Excel VBA (Visual Basic for Applications) is a powerful tool used for automating tasks in Microsoft Excel. One such task is executing EPM Automate commands using VBA macros. In this article, we will cover the basics of writing VBA macros to run EPM Automate commands and log in to Planning Analytics Workspace.
Prerequisites
Before we dive into writing macros, ensure you have the following:
- A working installation of Planning Analytics Workspace
- Excel 2010 or later
- EPM Automate installed and configured
- Knowledge of Excel VBA
Writing a Macro to Execute EPM Automate Commands
To write a VBA macro to execute EPM Automate commands, follow these steps:
Step 1: Create a new VBA project
Open your Excel file, press Alt + F11 to open the Visual Basic for Applications editor.
Step 2: Write the code
Add the following code to the newly created module:
Option Explicit
Sub RunEPMAutomateCommand()
Dim objShell As Object
Dim objFSO As Object
Dim objFile As Object
Dim strPath As String
' Set the path to the EPM Automate executable
strPath = "C:\Program Files\IBM\Enterprise Performance Management\EPM Automate\epmautomate.exe"
' Create an instance of the Shell object
Set objShell = CreateObject("WScript.Shell")
' Create an instance of the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Create a new temporary file to store the command
Set objFile = objFSO.CreateTextFile("C:\temp\command.txt", True)
' Write the command to the temporary file
objFile.Write "login -r -u -p "
objFile.Close
' Execute the EPM Automate command using the Shell object
objShell.Run ChDir:="C:\temp", _
Command:="epmautomate.exe @C:\temp\command.txt", _
Hidden:=True, WaitOnReturn:=True
' Delete the temporary file
objFSO.DeleteFile "C:\temp\command.txt", True
' Release objects from memory
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing
End Sub
Replace
Step 3: Run the macro
To run the macro, press F5 or click the "Run" button in the toolbar.
Automating the Login Process
To automate the login process, modify the code as follows:
Option Explicit
Sub RunEPMAutomateCommand()
Dim objShell As Object
Dim objFSO As Object
Dim objFile As Object
Dim strPath As String
Dim strLoginCommand As String
' Set the path to the EPM Automate executable
strPath = "C:\Program Files\IBM\Enterprise Performance Management\EPM Automate\epmautomate.exe"
' Create an instance of the Shell object
Set objShell = CreateObject("WScript.Shell")
' Create an instance of the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Create a new temporary folder to store the command and login files
Set objFolder = objFSO.CreateFolder("C:\temp\EPMAutomate")
' Create a new temporary file to store the command
Set objFile = objFSO.CreateTextFile("C:\temp\EPMAutomate\command.txt", True)
' Write the command to the temporary file
strLoginCommand = "login -r -u -p "
objFile.Write strLoginCommand
objFile.Write " runplan -p -r " ' Add your plan and report paths here
objFile.Close
' Create a new temporary file to store the login command
Set objLoginFile = objFSO.CreateTextFile("C:\temp\EPMAutomate\login.cmd", True)
objLoginFile.Write "epmautomate.exe @C:\temp\EPMAutomate\command.txt"
objLoginFile.Close
' Execute the login command using the Shell object
objShell.Run ChDir:="C:\temp\EPMAutomate", _
Command:="cmd /c C:\temp\EPMAutomate\login.cmd", _
Hidden:=True, WaitOnReturn:=True
' Delete the temporary files and folder
objFSO.DeleteFolder "C:\temp\EPMAutomate", True
Set objLoginFile = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing
End Sub
This code creates a new temporary folder to store the command and login files, writes the command and login command to their respective files, and executes the login command using the Shell object.
In this article, we learned how to write a VBA macro to execute EPM Automate commands and automate the login process to Planning Analytics Workspace. By following the steps outlined above, you can create your own custom macros to automate various tasks in your Planning Analytics environment.