Adding Dynamic Usernames to Excel Footers from SharePoint Templates

Tracking User Activity in SharePoint-Linked Excel Templates

Imagine a bustling office where multiple users access the same SharePoint template to submit their forms. 🖥️ The challenge arises when an auditor needs to identify who filled out and submitted a specific form. While SharePoint logs this information under the "creator" column, the need to print a hard copy with the user’s name in the footer of the Excel sheet remains unfulfilled.

This task becomes trickier because the default VBA functions like Application.UserName and Environ("Username") often point to the original template creator or the local machine user, instead of the actual user editing the form. As such, finding a reliable method to dynamically insert the correct username becomes crucial.

In real-world scenarios, this discrepancy can lead to inaccuracies in auditing and tracking. For example, in my previous role, we had an issue where forms completed by external contractors always displayed the admin’s username in the printout, causing significant confusion during audits.

This article delves into how you can bypass these obstacles using VBA, SharePoint integration, and some smart tweaks. By the end, you’ll have a practical solution that ensures each printed form correctly reflects the individual user who submitted it. Let’s dive in! 🔍

Customizing Excel Footers with Dynamic SharePoint Usernames

The solutions presented aim to dynamically fetch and display the SharePoint "creator" username in the footer of an Excel worksheet. This requirement arises in scenarios where multiple users submit forms based on a shared template stored in SharePoint, and auditors need clear attribution. The first script utilizes Excel's native PageSetup functionality to customize the footer dynamically. By combining VBA methods with SharePoint metadata access, this script ensures that the footer reflects the username of the user who completed the form, not the original creator.

For instance, the first solution leverages ListObjects.Add to establish a live connection to SharePoint's document library. This command pulls metadata into the workbook, making it possible to iterate through rows and extract the "creator" field. Imagine a department submitting compliance forms—each submission’s footer would clearly identify the responsible employee, eliminating audit ambiguities. This method ensures flexibility and prevents manual intervention in identifying form contributors. 🚀

The second approach takes advantage of SharePoint's REST API. By using the CreateObject("MSXML2.XMLHTTP") command, the script initiates an HTTP request to fetch metadata directly. This method is especially useful in environments where SharePoint libraries are complex or contain numerous fields. Parsing the JSON response with functions like InStr and Mid allows precise extraction of the "creator" field. In my past role, a similar script streamlined form tracking, saving hours of manual reconciliation every month. 🖋️

The final script integrates Office 365 properties, utilizing the BuiltinDocumentProperties command to access the workbook's metadata directly. This script is best suited for organizations that extensively use Office 365 and need a lightweight solution without REST API complexities. Each script has modular features, making them reusable for other SharePoint-integrated workflows. For instance, you can adapt them to include submission timestamps or even department names, enhancing their audit utility further.

Sub AddUsernameFromSharePoint()
    Dim ws As Worksheet
    Dim sharePointUsername As String
    Dim listObj As Object
    Dim spURL As String
    Dim row As Object
    On Error Resume Next
    ' Set your SharePoint site and library path here
    spURL = "https://your-sharepoint-site/documents/"
    Set ws = ActiveSheet
    ' Access metadata of the current workbook in SharePoint
    Set listObj = ws.ListObjects.Add(
        SourceType:=xlSrcExternal,
        Source:=spURL,
        Destination:=Range("A1")
    )
    ' Loop through rows to find "creator"
    For Each row In listObj.ListRows
        If row.Range(1, 1).Value = "creator" Then
            sharePointUsername = row.Range(1, 2).Value
            Exit For
        End If
    Next row
    ' Update footer with username
    ws.PageSetup.LeftFooter = "SUBMITTED BY: " & sharePointUsername & " on " & Date
    On Error GoTo 0
End Sub

Sub FetchUsernameWithAPI()
    Dim http As Object
    Dim jsonResponse As String
    Dim username As String
    Dim ws As Worksheet
    Set http = CreateObject("MSXML2.XMLHTTP")
    Set ws = ActiveSheet
    ' API endpoint to fetch metadata
    apiURL = "https://your-sharepoint-site/_api/web/lists/getbytitle('Documents')/items"
    ' Make GET request
    http.Open "GET", apiURL, False
    http.setRequestHeader "Accept", "application/json;odata=verbose"
    http.Send
    ' Parse response for "creator" field
    jsonResponse = http.responseText
    username = ParseJSONForCreator(jsonResponse)
    ' Add username to footer
    ws.PageSetup.LeftFooter = "SUBMITTED BY: " & username & " on " & Date
End Sub

Function ParseJSONForCreator(jsonResponse As String) As String
    ' Basic parsing logic to extract "creator" value
    Dim pos As Integer
    Dim creatorValue As String
    pos = InStr(jsonResponse, """creator"":")
    creatorValue = Mid(jsonResponse, pos + 10, InStr(pos + 10, jsonResponse, ",") - pos - 10)
    ParseJSONForCreator = creatorValue
End Function

Sub AddFooterFromO365()
    Dim ws As Worksheet
    Dim o365User As String
    Set ws = ActiveSheet
    ' Assume user is logged in to Office 365
    o365User = Application.UserName
    ' Fetch creator data from workbook properties
    If ActiveWorkbook.BuiltinDocumentProperties("Creator") <> "" Then
        o365User = ActiveWorkbook.BuiltinDocumentProperties("Creator")
    End If
    ' Add to footer
    ws.PageSetup.LeftFooter = "SUBMITTED BY: " & o365User & " on " & Date
End Sub

Integrating SharePoint Data with Excel VBA for Enhanced Auditing

One often-overlooked aspect of integrating Excel with SharePoint is the seamless flow of metadata between the two platforms. Using VBA, you can go beyond basic automation to extract crucial metadata fields, such as the username of the individual completing a template, and use them in custom Excel footers or headers. This functionality is essential in scenarios like compliance, where every submitted form needs a clear attribution to the person responsible for its completion.

Another useful approach involves leveraging SharePoint's extensive metadata capabilities. For instance, columns such as "Modified By" or "Last Modified" can provide additional context for tracking and verification. By pulling this data dynamically through VBA, your Excel templates not only reflect accurate user information but also reduce the likelihood of manual entry errors. This feature is particularly valuable in team projects, where multiple users collaborate on shared templates. 🖇️

Finally, it's important to account for the potential variations in how organizations use SharePoint. Some may have custom columns or metadata fields, requiring adaptable VBA scripts. Modular coding practices, such as separating API calls from data formatting, ensure that your solution can scale or adjust to such variations. For instance, in a past project, we used this approach to automatically generate summary reports that aggregated user activity directly from SharePoint into Excel workbooks. 🚀