Internship VBA Macros

This article is expected to be about a 7 minute read.

Published: Jan 16, 2021
Timeline: Mar 2, 2015-Nov 7, 2016

Table of Contents

Internship_VBA_Macros_Logo

Background

Since 1987, Microsoft Excel has been and remains to this day an extremely useful spreadsheet application for the organization, analysis, storage, and visualization of data in tabular format. Automation of data became a key addition to Excel in 1993 with the introduction of the (V)isual (B)asic for (A)pplications (VBA) macro programming language, an implementation of Microsoft’s earlier 1991 event-driven Visual Basic (VB6).

Today, Excel proficiency is a common baseline requirement for many employment opportunities in various fields; in the modern digital age, the automation of data is paramount for companies and other organizations to remain aloft and perform routine business functions - especially if these functions require arithmetic calculation for statistical analysis of data.

In 2015, I was hired as a software engineer intern to automate workflow productivity for the design and planning phases of utility installation project timelines across multiple customer support teams at the company. I was also tasked to design a method of forecasting future employee workload based on statistical analysis of predictable project metrics made available from prior fiscal year data - this would help the customer support teams preemptively mitigate imbalances while moving forward into the current fiscal year.

Unsurprisingly, this data was automatically generated and stored in various Excel documents by SAP, the company’s backend data processing software. Upon inspection of this raw data, I formulated multiple ideas for automation and efficiency improvement after a few meetings with my internship supervisor, and I got to work.

Implementation

Save for a few select projects I encountered throughout my internship, the majority of them were written in VBA macros. I did have one or two “one-off” objectives which involved some web development and direct integration with SAP - nevertheless, all of them had me writing VBA code at one point or another due to the company’s reliance on Excel.

Employee Task Completion Productivity Report Tool

The first project I was assigned involved sorting, analyzing and categorizing anywhere from 18,000 to 35,000 rows of raw input data to produce generated charts and tables as a visualization aid to employee productivity. Based on a few reference tabs provided by my management, my original design included a linear search lookup table - this approach worked well, and despite meeting expectation, did not satisfy my need for speed and efficiency. Due to the scale of the input, the implementation of the linear search (i.e. O(n)), and the generation of the charts, the program execution time took anywhere from 18 to 25 minutes to complete.

After a meeting with my supervisor, I mentioned I had already thought of several enhancements I could incorporate into a second iteration of the macro - including dismissal of the linear search for a hashmap (i.e. O(1), key -> value) structure (these are referred to as “Dictionaries” in VBA) and caching the reference data into memory. After a brief explanation of these plans, I was actually able to make the changes during the meeting and demonstrate the improvements. As it turned out, the dictionary structures combined with the reference caches trimmed the execution time down to 3 minutes, equating to an approximate 86% improvement in efficiency.

An example of caching reference data into memory:

'Date Reference for getting due dates
Set datesMap = New Dictionary
With PROGRAM.Sheets("Date Reference")
  For Each row In .Rows
    If row.row <= .UsedRange.Rows.Count Then
      If row.row > 1 Then
        Dim k As String: k = Trim(.Cells(row.row, 1)) & "^"
        datesMap(k & "apr") = Trim(.Cells(row.row, 3))
        datesMap(k & "may") = Trim(.Cells(row.row, 4))
        datesMap(k & "jun") = Trim(.Cells(row.row, 5))
        datesMap(k & "jul") = Trim(.Cells(row.row, 6))
        datesMap(k & "aug") = Trim(.Cells(row.row, 7))
        datesMap(k & "sep") = Trim(.Cells(row.row, 8))
      End If
    Else
      Exit For
    End If
  Next row
End With

An example of iterating through each row of raw input data:

For Each row In sheet.Rows
  'If row contains data
  If row.row <= sheet.UsedRange.Rows.Count Then
    'Logic here
  Else
    Exit For
  End If
Next row

An example of copying and pasting rows into separate sheets to handle sorting, based solely on a single column provided in the input data:

'Copy row
sheet.Range(sheet.Cells(row.row, 1), sheet.Cells(row.row, 31)).Copy

'Sort
Select Case Right(Trim(sheet.Cells(row.row, 16).Text), 4)
  Case "DATA1"
    For i = 1 To REPORT.Sheets.Count
      If Left(REPORT.Sheets(i).Name, 2) = "D-" Then
        With REPORT.Sheets(i)
          .Range(.Cells(.UsedRange.Rows.Count + 1, 2), .Cells(.UsedRange.Rows.Count + 1, 32)).PasteSpecial
          .Cells(.UsedRange.Rows.Count, 1) = Left(Trim(refMap(refNum)), 3)
        End With
      End If
    Next i
  Case "DATA2"
    With D2
      .Range(.Cells(.UsedRange.Rows.Count + 1, 2), .Cells(.UsedRange.Rows.Count + 1, 32)).PasteSpecial
      .Cells(.UsedRange.Rows.Count, 1) = Left(Trim(refMap(refNum)), 3)
    End With
  ...
End Select

A subroutine used to sort all columns between two specified columns based on a third column provided in a spreadsheet (this sub is used throughout all my macros):

'
' Sub: sortBasedOnColumn
' Params: sheet (Worksheet),
'         firstColumn (String),
'         secondColumn (String),
'         sortColumn (String),
'         startRow (String)
' This will sort all columns between 'firstColumn' and 'secondColumn' with respect to
' specified 'sortColumn' starting at row 'startRow' on worksheet 'sheet'.
'
Public Sub sortBasedOnColumn(ByVal sheet As Worksheet, firstColumn As String, secondColumn As String, sortColumn As String, startRow As String)

Dim lastRow As String
lastRow = sheet.Range("$" & secondColumn & "$" & sheet.Range("$" & secondColumn & "$" & sheet.Rows.Count).End(xlUp).row).row & ""
Dim lastColumn As String
lastColumn = sheet.Range("$" & secondColumn & "$" & sheet.Range("$" & secondColumn & "$" & sheet.Rows.Count).End(xlUp).row).column & ""

With sheet
  .Activate
  Range("$" & firstColumn & "$" & startRow, Range(secondColumn & lastRow)).Select
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=Range("$" & sortColumn & "$" & startRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With .Sort
    .SetRange Range("$" & firstColumn & "$" & startRow, Range(secondColumn & lastRow))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  .Cells(1, 1).Select
End With

End Sub

An example of saving output:

'Save PROGRAM file
PROGRAM.Save

'Save output
outputLocation = Application.GetSaveAsFilename(PROGRAM.Path & "\" & reportName, filefilter:="Excel Files (*.xlsx), *.xlsx", title:="Save output")
REPORT.SaveAs fileName:=outputLocation, FileFormat:=xlOpenXMLWorkbook

Besides sorting the data into individual tabs, color-coded charts were produced as output and may have looked like the following:

Internship_VBA_Macros_Employee_Task_Completion_Productivity_Report_Tool_Output_Graphs

These color-coded visual representations of data and related metrics helped the company’s customer support teams hone in on imbalances to meet project goal deadlines and improve customer experience by extension.

Employee Workload Forecasting Tool

Similar to the Employee Task Completion Productivity Report Tool, I was tasked with developing a preemptive workload calculator to forecast predictable workload prior to the beginning of the project season. This program would not rely on raw data as input, but rather a few numerical values which would be fed into several equations and plotted as a multi-bar chart across a future date range - in this way, the project manager would be able to mitigate imbalances before they could occur throughout the entire year.

One of these functions may have looked like this:

'
' Sub: doTIndex
' Params: worksheet (Worksheet), value (Integer), main (Integer), mon (String)
' This will run through the calculations for each T-milestone.
'
Private Sub doTIndex(ByVal worksheet As Worksheet, value As Integer, main As Integer, mon As String)

For Each k In totalsMap.Keys
  Dim valueTX As Double
  If CDbl(totalsMap(k)) > 0 Then
    valueTX = CDbl(totalsMap(k)) * value
    valueTX = valueTX / (CDate(dueDatesMap(k & "^" & LCase(mon))) - CDate(startDatesMap(k & "^" & LCase(mon))) + 1)
  End If
  Dim mainTX As Double
  If CInt(mainMap(k)) > 0 Then
    mainTX = CInt(mainMap(k)) * main
    mainTX = mainTX / (CDate(dueDatesMap(k & "^" & LCase(mon))) - CDate(startDatesMap(k & "^" & LCase(mon))) + 1)
  End If
  Dim dateIndex As Date
  For dateIndex = CDate(startDatesMap(k & "^" & LCase(mon))) To CDate(dueDatesMap(k & "^" & LCase(mon)))
    With CES
      .Cells(.UsedRange.Rows.Count + 1, 1) = dateIndex
      .Cells(.UsedRange.Rows.Count, 2) = valueTX
      .Cells(.UsedRange.Rows.Count, 3) = mainTX
    End With
  Next dateIndex
Next k

End Sub

An example of generating a graph:

'
' Sub: createGraph
' Params: book (Workbook), sheet (Worksheet), t (String), sourceRow1 (Integer), sourceCol1 (Integer), sourceRow2 (Integer), sourceCol2 (Integer)
' This will create a graph.
'
Private Sub createGraph(book As Workbook, ByVal sheet As Worksheet, t As String, sourceRow1 As Integer, sourceCol1 As Integer, sourceRow2 As Integer, sourceCol2 As Integer)

Dim g As Chart: Set g = book.Charts.Add
Set g = g.Location(Where:=xlLocationAsObject, name:=t)
With g
  .SetSourceData sheet.Range(sheet.Cells(sourceRow1, sourceCol1), sheet.Cells(sourceRow2, sourceCol2)), xlRows
  .ChartType = xlBarStacked
  .HasTitle = True
  .SetElement (msoElementChartTitleAboveChart)
  .Parent.Top = 0
  .Parent.Left = 0
  .Parent.Height = 645
  .Parent.Width = 925
  .ChartTitle.Text = t
  .SeriesCollection(1).Interior.ColorIndex = 44
  .SeriesCollection(2).Interior.ColorIndex = 3
End With
addVerticalLineToGraph g, "Limit", CInt(limit), 102, 102, 153, True

End Sub

'
' Sub: addVerticalLineToGraph
' Params: c (Chart), title (String), n (Integer), red (Integer), blue (Integer), green (Integer), dashed (Boolean)
' This adds a vertical line to a chart 'c' at 'n' named 'title' with RGB color based on
' 'red', 'blue', and 'green', with boolean property 'dashed'.
'
Public Sub addVerticalLineToGraph(c As Chart, title As String, n As Integer, red As Integer, blue As Integer, green As Integer, dashed As Boolean)
Dim l As Series: Set l = c.SeriesCollection.NewSeries
With l
  .ChartType = xlXYScatterLinesNoMarkers
  .XValues = Array(n, n)
  .Values = Array(0, 1)
  .AxisGroup = xlSecondary
  .name = title
  .Format.Line.ForeColor.RGB = RGB(red, blue, green)
  If dashed Then
    .Format.Line.DashStyle = msoLineSysDash
  End If
End With
c.HasAxis(XlAxisType.xlCategory, XlAxisGroup.xlSecondary) = True
Dim xAxis2 As Axis: Set xAxis2 = c.Axes(xlValue, xlSecondary)
With xAxis2
  .MajorTickMark = xlNone
  .TickLabelPosition = xlNone
  .Format.Line.Visible = msoFalse
  .MinimumScale = 0
  .MaximumScale = 1
End With
c.HasAxis(XlAxisType.xlCategory, XlAxisGroup.xlSecondary) = False
End Sub

An example of a sub used to calculate start dates:

'
' Sub: calculateStartDates
' Params: N/A
' This calculates all start dates based on other milestone due dates.
'
Private Sub calculateStartDates()

Set startDatesMap = New Dictionary
For i = 0 To dueDatesMap.Count - 1
  Dim k As String: k = dueDatesMap.Keys(i)
  If i < 6 Then
    '27 days representing 4 weeks + 1 day, which theoretically is (T-34 + 1) in this case
    startDatesMap(k) = Trim(CDate(dueDatesMap(k)) - 27)
  Else
    'Handle special cases
    If dueDatesMap(k) = dueDatesMap.Items(i - 6) Then
      'If same due dates as previous, same start dates as previous
      startDatesMap(k) = startDatesMap.Items(i - 6)
    Else
      'Look back 6 entries for previous milestone due date, add 1 to it
      startDatesMap(k) = Trim(CDate(dueDatesMap.Items(i - 6)) + 1)
    End If
  End If
Next i

End Sub

An example of the simple calculator interface:

Internship_VBA_Macros_Employee_Workload_Forecasting_Tool_Interface

An example of graph output (36 hour horizontal line representing 1 work week):

Internship_VBA_Macros_Employee_Workload_Forecasting_Tool_Output_Graph

Customer Project Phase Timeline

In an effort to provide visualization to the planning phases of customer project timelines, I was tasked in 2016 to create an interface capable of interacting directly with SAP. The original idea was to build this interface in Excel and hook into SAP and automate interaction with VBA macros - essentially, this project would be a sort of “frontend” for the company’s backend data processor. After meeting with the project manager to discuss options, I mentioned the possibility of creating a web-based frontend rather than one contained inside Excel - at the time, I was uncertain if this would even be feasible. I did not know if there was any way to hook into SAP directly nor if it would be possible to execute VBA macros from the browser.

Regardless, I accepted the challenge and told my project manager I would give it a shot (he really liked the idea of a web interface for ease of usability). I designed a simple web UI around the vis.js dynamic visualization library (now deprecated to visjs) by almende to add an adjustable timeline (see example here). Specifically, my createTimeline() function may have looked like the following:

/* Creates timeline when page loads */
function createTimeline() {
  var container = document.getElementById('timeline');
  var today = new Date(getToday() + ' 00:00:00');
  items = new vis.DataSet([
    { id: 1, content: 'INITIATION', start: (new Date().setDate(today.getDate() - 3)), style: "background-color: #a589d9; border-color: #8c68cb;" },
    { id: 2, content: 'SCOPING', start: (new Date().setDate(today.getDate() - 2)), style: "background-color: #f16d64; border-color: #ec4339;" },
    { id: 3, content: 'DESIGN', start: (new Date().setDate(today.getDate() - 1)), style: "background-color: #f59640; border-color: #f47b16;" },
    { id: 4, content: 'PREREQUISITES', start: (new Date().setDate(today.getDate())), style: "background-color: #35bec1; border-color: #00aeb3;" },
    { id: 5, content: 'SCHEDULING', start: (new Date().setDate(today.getDate() + 1)), style: "background-color: #f371af; border-color: #ed4795;" },
    { id: 6, content: 'EXECUTION', start: (new Date().setDate(today.getDate() + 2)), style: "background-color: #95c753; border-color: #7cb82f;" }
  ]);
  options = { stack: false, height: '200px', selectable: false, hiddenDates: {}, align: 'center' };
  timeline = new vis.Timeline(container, items, options);
}

Each dataset ID in the items parameter passed to new vis.Timeline() corresponds to the initial value given to each project phase when the page loads in the browser. I added sliders to adjust these values, which are also controlled in JavaScript:

<div id="sliders">
  <form>
    <div id="scoping_wrapper">
      <label for="scoping">SCOPING:<label>
      <div class="scoping_popup" style="display: none;">
        Initial site meeting or review, obtaining service characteristics and customer’s load information and all other information pertinent to design.
      </div>
    </div>
    <span id="scoping_lock" style="color: green;">&#x1f513;</span>
    <input type="range" name="scoping" id="scoping" data-highlight="true" data-show-value="true" value="0" min="0" max="50">
  </form>
  ...
</div>

/* Scoping */
function getScoping() {
  scoping = $('#scoping').val();
  return scoping;
}
function setScoping(i) {
  scoping = i;
  $('#scoping').val(i).slider('refresh');
}

/* Updates timeline */
function updateTimeline() {
  // Items
  var initiationItem = items.get(1);
  var scopingItem = items.get(2);
  var designItem = items.get(3);
  var prerequisitesItem = items.get(4);
  var schedulingItem = items.get(5);
  var executionItem = items.get(6);
  // Base date
  var base = new Date($('#date_picker').val() + ' 00:00:00');
  // Backward
  prerequisitesItem.end = dateOffset(base, 0);
  prerequisitesItem.start = dateOffset(base, -getPrerequisites());
  designItem.end = (new Date(prerequisitesItem.start));
  designItem.start = dateOffset(designItem.end, -getDesign());
  scopingItem.end = (new Date(designItem.start));
  scopingItem.start = dateOffset(scopingItem.end, -getScoping());
  initiationItem.end = (new Date(scopingItem.start));
  initiationItem.start = dateOffset(initiationItem.end, -getInitiation());
  // Forward
  schedulingItem.start = (new Date(prerequisitesItem.end));
  schedulingItem.end = dateOffset(schedulingItem.start, getScheduling());
  executionItem.start = (new Date(schedulingItem.end));
  executionItem.end = dateOffset(executionItem.start, getExecution());
  // Update
  items.update(initiationItem);
  items.update(scopingItem);
  items.update(designItem);
  items.update(prerequisitesItem);
  items.update(schedulingItem);
  items.update(executionItem);
  // Options
  timeline.setOptions(options);
  // Window
  if(!(isVisibleWeekends())) {
    timeline.setWindow(dateOffset(initiationItem.start, -1), dateOffset(executionItem.end, 1));
  } else {
    var ini = new Date().setTime(initiationItem.start.getTime() + (-1) * 86400000);
    var exe = new Date().setTime(executionItem.end.getTime() + 1 * 86400000);
    timeline.setWindow(ini, exe);
  }
}

Internship_VBA_Macros_Customer_Project_Phase_Timeline_Interface

Internship_VBA_Macros_Customer_Project_Phase_Timeline_Interface_Updating

Internship_VBA_Macros_Customer_Project_Phase_Timeline_Interface_Updated

This interface allows the employee to adjust the various phases of a project based on an individual customer’s needs and deadlines. However, without some way to integrate with SAP, the user is only able to view the date deadlines for each phase but not automatically submit them into the data processor (i.e. this would still need to be completed manually). I figured the best idea would be to write the SAP form submission macros in VBA and then finally find a way to call them from the web UI (from the outside looking in, this sounds impossible).

Nevertheless I started writing the macros used to submit updated deadline information based on the “Notification” selector in the web UI. To do so, I added a common VBA module shared across the Internet for integrating with SAP and modified it to fit my needs. On top of this integration, I also wanted to implement multithreading, a practice normally non-existent in VBA but made possible by automatically cloning the workbook and calling the macros from the original as many times as needed to run tasks in parallel. If this sounds “hacky” that’s because it certainly is - but it works, and it worked for this project.

'
' Function: setup
' Params: ses (Variant)
' This sub will act as the driver for opening and preparing SAP for automation.
' It will return the session for interaction / automation.
'
Public Function setup(Optional ses As Variant) As SAPFEWSELib.GuiSession: On Error GoTo ErrHandler

'Open IE / SAP if needed
openSAP

'New session
Dim Session As SAPFEWSELib.GuiSession

'Initial setup and connection
If App Is Nothing Then
  Set SapGuiAuto = GetObject("SAPGUI")
  Set App = SapGuiAuto.GetScriptingEngine
End If
If Connection Is Nothing Then Set Connection = App.Children(0)
If Session Is Nothing Then
  If Not IsMissing(ses) Then
    Set Session = Connection.Children(CInt(ses))
  Else
    Set Session = Connection.Children(CInt(getSessionCount))
  End If
End If
If IsObject(WScript) Then
  WScript.ConnectObject Session, "on"
  WScript.ConnectObject Application, "on"
End If

Set setup = Session

Exit Function

'Error handling
ErrHandler: errHandle Session, Err.Number, Err.Description

End Function

'
' Sub: openSAP
' Params: N/A
' This sub will open Internet Explorer and open SAP by visiting the link for SAP in the portal.
' It will then close Internet Explorer and wait 5 seconds before continuing automation.
'
Public Sub openSAP()

'Check if SAP is already open
If isExeRunning("saplogon.exe") Then Exit Sub

'Automate IE to open SAP
Dim IE As Object: Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}") 'Set IE = CreateObject("InternetExplorer.Application")
With IE
  ShowWindow .hwnd, SW_MAXIMIZE
  .Visible = True
  .Navigate "http://some.link.here/", 0
  .Navigate "irj/servlet/prt/portal/prteventname/Navigate/prtroot/pcd!3aportal_content!2fevery_user!2fgeneral!2fcom.Z_Desktop_0!2fframeworkPages!2fcom.frameworkpage!2fcom.sap.portal.innerpage!2fcom.sap.portal.contentarea?NavigationTarget=navurl%3A%2F%2Fa280b87640b394a02c0f69de77efa77d&ExecuteLocally=true&CurrentWindowId=WID1370809905537&NavMode=1", 2048&
End With

'Wait until SAP has opened
While Not isExeRunning("saplogon.exe"): DoEvents: Wend
  rest 40
  IE.Quit

End Sub

'
' Function: isExeRunning
' Params: sExeName (String), sComputer (String)
' This will check to see if a certain process 'sExeName' is running in Windows.
' It is used in this project for instance to check whether or not SAP has loaded.
'
Public Function isExeRunning(sExeName As String, Optional sComputer As String = ".") As Boolean

On Error GoTo Error_Handler
  Dim obj As Object: Set obj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sComputer & "\root\cimv2").ExecQuery("SELECT * FROM Win32_Process WHERE Name = '" & sExeName & "'")
  If obj.Count <> 0 Then isExeRunning = True

Error_Handler_Exit:
  On Error Resume Next
  Set obj = Nothing
  Exit Function

Error_Handler:
  Resume Error_Handler_Exit

End Function

'
' Function: getSessionCount
' Params: N/A
' Returns the number of sessions.
'
Public Function getSessionCount() As Integer

getSessionCount = Connection.Children.Count - 1

End Function

'
' Function: elementExists
' Params: Session (SAPFEWSELib.GuiSession), id (String)
' Returns true if element with ID 'id' exists in Session 'Session', false otherwise.
'
Public Function elementExists(Session As SAPFEWSELib.GuiSession, id As String) As Boolean: On Error Resume Next

Session.FindById(id).SetFocus
If Err.Number = 0 Then
  elementExists = True
Else
  elementExists = False
End If

End Function

'
' Sub: errHandle
' Params: Session (SAPFEWSELib.GuiSession), (Optional) num (Integer), (Optional) message (String)
' Handle errors in SAP session.
'
Private Sub errHandle(Session As SAPFEWSELib.GuiSession, Optional num As Integer, Optional message As String)

If Not Session Is Nothing Then
  Session.FindById("wnd[0]").Close
  Set Session = Nothing
End If

'Kill SAP
killSAP

'Failure
writeReturn "3", message

End Sub

To handle killing processes in the multi-threaded environment, I wrote two subs named killSAP (generates and executes a .bat script using taskkill /F /IM sapgui.exe /T to terminate the SAP process) and killExcel (same logic as killSAP but targeting excel.exe):

'
' Sub: killSAP
' Params: N/A
' This sub kills any SAP processes.
'
Public Sub killSAP()

Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
Dim killBat As Object: Set killBat = fso.CreateTextFile(ThisWorkbook.Path + "\kill_sap.bat")
killBat.WriteLine "taskkill /F /IM saplogon.exe /T"
killBat.WriteLine "taskkill /F /IM sapgui.exe /T"
killBat.Close: Set killBat = Nothing
Shell (ThisWorkbook.Path & "\kill_sap.bat"), vbHide
SetAttr (ThisWorkbook.Path & "\kill_sap.bat"), vbNormal: rest 3: Kill (ThisWorkbook.Path & "\kill_sap.bat")

End Sub

'
' Sub: killExcel
' Params: N/A
' This sub kills any Excel processes other than the one the sub is running from.
'
Public Sub killExcel()

Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
Dim killBat As Object: Set killBat = fso.CreateTextFile(ThisWorkbook.Path + "\kill_excel.bat")
killBat.WriteLine "for /f ""skip=3 tokens=2 delims= "" %%a in ('tasklist /fi ""imagename eq excel.exe""') do ( if ""%%a"" neq """ & GetCurrentProcessId & """ ( TASKKILL /PID %%a /f >nul 2>nul ) )"
killBat.Close: Set killBat = Nothing
Shell (ThisWorkbook.Path & "\kill_excel.bat"), vbHide
SetAttr (ThisWorkbook.Path & "\kill_excel.bat"), vbNormal: rest 3: Kill (ThisWorkbook.Path & "\kill_excel.bat")

End Sub

For automating the actual specific interaction with SAP forms, SAP elements are identified using very long string paths, and are easiest to deduce using the “Script Recording and Playback…” functionality made available in Excel.

Internship_VBA_Macros_Customer_Project_Phase_Timeline_Recording_Macros

An example of automating a specific form in SAP:

'
' Sub: auto
' Params: N/A
' This is used for the customized / desired automation in SAP.
'
Private Sub auto(): On Error GoTo ErrHandler

With Session

  'Enter IW52
  .FindById("wnd[0]/tbar[0]/okcd").Text = "iw52"
  .FindById("wnd[0]").SendVKey 0

    '"Notification selection"

      'Notification
      .FindById("wnd[0]/usr/ctxtRIWO00-QMNUM").Text = priv_notif

    'Enter
    .FindById("wnd[0]").SendVKey 0

    'If it fails here, notification was locked or no data was returned from SAP
    On Error GoTo ErrHandlerLocked

    'Switch to "Tasks" tab
    .FindById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB11").Select

    'Return to normal error checking
    On Error GoTo ErrHandler

    'Iterate through the task list and update finish dates
    Dim scroll As Integer: scroll = 0
    For scroll = 0 To 100
      .FindById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB11/ssubSUB_GROUP_10:SAPLIQS0:7120/tblSAPLIQS0MASSNAHMEN_VIEWER").VerticalScrollbar.Position = scroll
      If .FindById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB11/ssubSUB_GROUP_10:SAPLIQS0:7120/tblSAPLIQS0MASSNAHMEN_VIEWER").VerticalScrollbar.Position <> scroll Then Exit For
      Dim task As String: task = Trim(.FindById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB11/ssubSUB_GROUP_10:SAPLIQS0:7120/tblSAPLIQS0MASSNAHMEN_VIEWER/ctxtVIQMSM-MNCOD[2,0]").Text)
      Select Case task
        'Scoping
        Case "SCPE", "CSMC"
          writeNewDate Session, getDateFormat(priv_scoping_date)
          writeTask task
          On Error GoTo ErrHandler
        'Design
        Case "REVW"
          writeNewDate Session, getDateFormat(priv_design_date)
          writeTask task
          On Error GoTo ErrHandler
        'Prerequisites
        Case "ORFC", "PCST", "RCOM"
          writeNewDate Session, getDateFormat(priv_prerequisites_date)
          writeTask task
          On Error GoTo ErrHandler
      End Select
      If elementExists(Session, "wnd[2]") Then
        Do While elementExists(Session, "wnd[2]")
          .FindById("wnd[2]/tbar[0]/btn[0]").press
        Loop
      End If
    Next scroll

    'Save
    '.FindById("wnd[0]/tbar[0]/btn[11]").press

End With

'Close session
Session.FindById("wnd[0]").Close

At this point, I had automated SAP to update project phase deadline information in VBA but had not found a way to call the macro from the web UI. After a long day or two of research, I found a way to accomplish this task with PHP and AJAX (“Asynchronous JavaScript and XML”). This topology would appear as follows:

Internship_VBA_Macros_Customer_Project_Phase_Timeline_Topology

  • HTML, CSS, JavaScript - frontend interface which the user sees and interacts with in their web browser; HTML is the layout of the page, CSS is the styling of the page, and JavaScript provides logic handling within the browser and sends AJAX requests to the server and waits for responses
  • PHP - used on the server to handle incoming AJAX requests from clients and processes them; used in combination with Windows processes to automate Excel on the server
  • VBA - used on the server to handle the automation and interaction with SAP; controlled via PHP, a macro runIW52() is called and is given the arguments passed in by the AJAX request from the browser

An example JavaScript function used to submit an AJAX request to the server:

/* Runs the VBA macro via PHP COM to update a notification in IW52 */
function runIW52(notif) {
  $('#overlay').show();
  $('.loader').show();
  $('.checkmark').hide();
  $('.errormark').hide();
  $('#overlay_text').text('Updating task end dates for ' + notif + '...');
  $('#overlay_text').css('color', 'rgba(0, 121, 193, 1)');
  $('#overlay_text').show();
  var scopingDate, designDate, prerequisitesDate;
  for(var i = 1; i <= items.length; i++) {
    var item = items.get(i);
    switch(item.content) {
      case 'SCOPING': scopingDate = getDateString(item.end); break;
      case 'DESIGN': designDate = getDateString(item.end); break;
      case 'PREREQUISITES': prerequisitesDate = getDateString(item.end); break;
    }
  }
  // Send the information for the long running PHP script with COM
  var id = 0;
  $.ajax({
    type: 'POST',
    url: 'php/timeline.php',
    data: {
      notification: notif,
      scoping_date: scopingDate,
      design_date: designDate,
      prerequisites_date: prerequisitesDate
    },
    success: function(returnJson) { id = JSON.parse(returnJson); }
  })
  // Check for when it finishes
  var iteration = setInterval(function() {
    $.ajax({
      type: 'POST',
      url: 'php/check.php',
      data: { identifier: id },
      success: function(returnJson) {
        var returnData = JSON.parse(returnJson);
        var result = returnData[0];
        if(result) {
          var returnCode = returnData[1];
          var returnDesc = returnData[2];
          var returnTask = returnData[3];
          if(returnTask !== null && returnTask !== '') { returnTask = returnTask.split(';'); }
          if(returnCode == 0) {
            var tasks = '';
            for(var i = 0; i < returnTask.length; i++) {
              if(returnTask[i] !== '') {
                tasks = tasks + returnTask[i];
                if(i !== returnTask.length - 1) { tasks = tasks + ', '; }
              }
            }
            $('#overlay_text').text('Success! Notification ' + notif + ' was updated!\nTasks: ' + tasks);
            $('#overlay_text').css('color', 'lime');
            $('.checkmark').show();
          } else {
            $('#overlay_text').text('Failure! ' + returnDesc);
            $('#overlay_text').css('color', 'rgb(255, 25, 25)');
            $('.errormark').show();
          }
          $('.loader').hide();
          $('#overlay_text').show();
          $('#overlay').one('click', function() {
            $('#overlay').hide();
            $('#overlay_text').hide();
          });
          clearInterval(iteration);
        }
      }
    })
  }, 3000);
}

The PHP functionality itself was split into 3 separate modules:

  1. timeline.php - receives AJAX requests from client web browsers; each parameter is checked for presence (notification number and phase end dates), and execution sleeps until it can process the long running PHP script long.php which actually handles the Excel manipulation in the background; this structure allows clients to load the page without freezing up given another request is being processed (timeline.php should almost be considered to be a queueing system)
  2. long.php - runs the Excel manipulation (and later, the macro) in the background; this script is backgrounded to allow other clients to queue in AJAX requests without page lockups and grabs the arguments saved in a temporary text file created by timeline.php, opens Excel silently, and runs the runIW52() macro through Excel manipulation; the text file is then deleted to allow the next AJAX request through while another text file is temporarily created and assigned a name based on a randomly generated number using PHP’s mt_rand() function so that requests may be matched based on uniquely random temporary numbers to correctly return back the results to the correct client
  3. check.php - continuously sent AJAX requests after the first AJAX request is sent to timeline.php, returns result information to a client if a text file matching their generated request ID exists; once this text file does exist, it is deleted, the continuous requests cease, and the result is displayed on the client screen

timeline.php

<?php
  try {
    # Check for the presence of args from AJAX
    if(isset($_POST["notification"]) && isset($_POST["scoping_date"]) && isset($_POST["design_date"]) && isset($_POST["prerequisites_date"])) {
      # Change script max execution time
      ini_set("max_execution_time", 600);
      # Sleep while processing other request
      while(file_exists("args_lock.txt")) { sleep(5); }
      # Get random number to identify this request
      $id = mt_rand();
      # Create file for *very* inconvenient method of arg passing
      $args = fopen("args_lock.txt", "w") or die("Unable to create new args file");
      fwrite($args, $id."\n");
      fwrite($args, $_POST["notification"]."\n");
      fwrite($args, $_POST["scoping_date"]."\n");
      fwrite($args, $_POST["design_date"]."\n");
      fwrite($args, $_POST["prerequisites_date"]."\n");
      fclose($args);
      # Backgrounding for long-running PHP script (COM stuff)
      shell_exec("background.vbs");
      # Return the session ID so client knows which session results to wait for
      echo json_encode($id);
    }
  } catch(Exception $e) {
    # Print error information if caught
    error_log($e);
  }
?>

long.php

<?php
  try {
    # Change script max execution time
    ini_set("max_execution_time", 600);
    # Get passed args
    $args = fopen("args_lock.txt", "r");
    $id = intval(fgets($args));
    $notification = fgets($args);
    $scoping_date = fgets($args);
    $design_date = fgets($args);
    $prerequisites_date = fgets($args);
    # Variable for workbook
    $file = "../vba/IW52.xlsm";
    # Starts the COM and opens workbook
    $excel = new COM("Excel.Application") or die("Unable to start COM");
    # Set some Excel flags
    $excel->DisplayAlerts = false;
    $excel->Application->Visible = 0;
    # Get the workbook
    $workbook = $excel->Application->Workbooks->Open($file) or die("Unable to open Excel");
    # Run macro from workbook
    $excel->Run("runIW52", $notification, $scoping_date, $design_date, $prerequisites_date);
    # Handle all "return codes" as marked in Cells(1, 1) from macro
    $return_code = $excel->ActiveWorkbook->Worksheets(1)->Cells(1, 1)->Value;
    $return_desc = $excel->ActiveWorkbook->Worksheets(1)->Cells(1, 2)->Value;
    $return_task = $excel->ActiveWorkbook->Worksheets(1)->Cells(1, 3)->Value;
    # Close workbook and quit
    $excel->ActiveWorkbook->Close(false);
    $excel->Quit();
    unset($excel);
    # Close and delete args lock file
    fclose($args);
    chmod("args_lock.txt", 0777);
    unlink("args_lock.txt");
    # Create ID file with output
    $result = fopen($id.".txt", "w") or die("Unable to create new ".$id.".txt file");
    fwrite($result, $return_code."\n");
    fwrite($result, $return_desc."\n");
    fwrite($result, $return_task."\n");
    fclose($result);
  } catch(Exception $e) {
    # Print error information if caught
    error_log($e);
  }
?>

check.php

<?php
  try {
    # Check for the presence of args from AJAX
    if(isset($_POST["identifier"])) {
      # Get the ID
      $id = intval($_POST["identifier"]);
      if(file_exists($id.".txt")) {
        $result = fopen($id.".txt", "r");
        $return_code = fgets($result);
        $return_desc = fgets($result);
        $return_task = fgets($result);
        fclose($result);
        chmod($id.".txt", 0777);
        unlink($id.".txt");
        echo json_encode(array(true, $return_code, $return_desc, $return_task));
      } else {
        echo json_encode(array(false));
      }
    }
  } catch(Exception $e) {
    # Print error information if caught
    error_log($e);
  }
?>

Results

While there are several other projects I could write about from my internship, I figured these three projects did well to provide examples of the power of Excel and automation with VBA. An understanding of data structures and efficiency programming provided me with additional resources in the design of the programs I produced for my team members - many of these programs and tools are still in use today. It is always rewarding and fulfilling to implement transformational change into any organization, and it remains a goal of mine going forward in all future roles and challenges I take on throughout my career.