Dynamische gebruikersnamen toevoegen aan Excel-voetteksten vanuit SharePoint-sjablonen

Gebruikersactiviteit bijhouden in aan SharePoint gekoppelde Excel-sjablonen

Stel je een druk kantoor voor waar meerdere gebruikers toegang hebben tot dezelfde SharePoint-sjabloon om hun formulieren in te dienen. 🖥️ De uitdaging ontstaat wanneer een auditor moet vaststellen wie een specifiek formulier heeft ingevuld en ingediend. Hoewel SharePoint deze informatie registreert onder de kolom 'maker', blijft de noodzaak om een ​​papieren versie af te drukken met de gebruikersnaam in de voettekst van het Excel-werkblad onvervuld.

Deze taak wordt lastiger omdat de standaard VBA als volgt functioneert Applicatie.Gebruikersnaam En Omgeving("Gebruikersnaam") verwijzen vaak naar de oorspronkelijke sjabloonmaker of de lokale computergebruiker, in plaats van naar de daadwerkelijke gebruiker die het formulier bewerkt. Daarom wordt het vinden van een betrouwbare methode om dynamisch de juiste gebruikersnaam in te voeren cruciaal.

In praktijkscenario's kan deze discrepantie leiden tot onnauwkeurigheden in auditing en tracking. In mijn vorige rol hadden we bijvoorbeeld een probleem waarbij door externe contractanten ingevulde formulieren altijd de gebruikersnaam van de beheerder op de afdruk vertoonden, wat tijdens audits aanzienlijke verwarring veroorzaakte.

Dit artikel gaat in op hoe u deze obstakels kunt omzeilen met behulp van VBA, SharePoint-integratie en enkele slimme aanpassingen. Uiteindelijk beschikt u over een praktische oplossing die ervoor zorgt dat elk afgedrukt formulier de juiste weergave is van de individuele gebruiker die het heeft ingediend. Laten we erin duiken! 🔍

Excel-voetteksten aanpassen met dynamische SharePoint-gebruikersnamen

De gepresenteerde oplossingen zijn bedoeld om de SharePoint "maker" gebruikersnaam in de voettekst van een Excel-werkblad. Deze vereiste doet zich voor in scenario's waarin meerdere gebruikers formulieren indienen op basis van een gedeelde sjabloon die is opgeslagen in SharePoint, en auditors een duidelijke attributie nodig hebben. Het eerste script maakt gebruik van de native versie van Excel Pagina-instelling functionaliteit om de voettekst dynamisch aan te passen. Door VBA-methoden te combineren met toegang tot SharePoint-metagegevens, zorgt dit script ervoor dat de voettekst de gebruikersnaam weerspiegelt van de gebruiker die het formulier heeft ingevuld, en niet de oorspronkelijke maker.

De eerste oplossing maakt bijvoorbeeld gebruik van LijstObjecten.Toevoegen om een ​​live verbinding tot stand te brengen met de documentbibliotheek van SharePoint. Deze opdracht haalt metagegevens naar de werkmap, waardoor het mogelijk wordt om door rijen te lopen en het veld 'maker' te extraheren. Stel je een afdeling voor die nalevingsformulieren indient. In de voettekst van elke indiening wordt duidelijk de verantwoordelijke medewerker geïdentificeerd, waardoor dubbelzinnigheden bij de audit worden geëlimineerd. Deze methode zorgt voor flexibiliteit en voorkomt handmatige tussenkomst bij het identificeren van formulierbijdragers. 🚀

De tweede benadering maakt gebruik van de REST API van SharePoint. Door gebruik te maken van de CreateObject("MSXML2.XMLHTTP") commando, initieert het script een HTTP-verzoek om metagegevens rechtstreeks op te halen. Deze methode is vooral handig in omgevingen waar SharePoint-bibliotheken complex zijn of veel velden bevatten. Het JSON-antwoord parseren met functies zoals InStr En Midden maakt nauwkeurige extractie van het veld "schepper" mogelijk. In mijn vorige rol stroomlijnde een soortgelijk script het bijhouden van formulieren, waardoor elke maand uren aan handmatige afstemming werd bespaard. 🖋️

Het uiteindelijke script integreert Office 365-eigenschappen, met behulp van de Ingebouwde documenteigenschappen opdracht om rechtstreeks toegang te krijgen tot de metagegevens van de werkmap. Dit script is het meest geschikt voor organisaties die veelvuldig gebruik maken van Office 365 en een lichtgewicht oplossing nodig hebben zonder REST API-complexiteit. Elk script heeft modulaire functies, waardoor ze herbruikbaar zijn voor andere in SharePoint geïntegreerde workflows. U kunt ze bijvoorbeeld aanpassen zodat ze tijdstempels voor indiening of zelfs afdelingsnamen bevatten, waardoor hun audithulpprogramma nog verder wordt uitgebreid.

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

Integratie van SharePoint-gegevens met Excel VBA voor verbeterde auditing

Een vaak over het hoofd gezien aspect van de integratie van Excel met SharePoint is de naadloze stroom van metadata tussen de twee platforms. Met VBA kunt u verder gaan dan de basisautomatisering en cruciale metagegevensvelden extraheren, zoals de gebruikersnaam van de persoon die een sjabloon invult, en gebruik deze in aangepaste Excel-voetteksten of -kopteksten. Deze functionaliteit is essentieel in scenario's zoals compliance, waarbij elk ingediend formulier een duidelijke toewijzing moet hebben aan de persoon die verantwoordelijk is voor de voltooiing ervan.

Een andere nuttige aanpak is het benutten van de uitgebreide metadatamogelijkheden van SharePoint. Kolommen zoals 'Gewijzigd door' of 'Laatst gewijzigd' kunnen bijvoorbeeld extra context bieden voor tracking en verificatie. Door deze gegevens dynamisch via VBA op te halen, weerspiegelen uw Excel-sjablonen niet alleen nauwkeurige gebruikersinformatie, maar verkleinen ze ook de kans op handmatige invoerfouten. Deze functie is vooral waardevol in teamprojecten, waarbij meerdere gebruikers samenwerken aan gedeelde sjablonen. 🖇️

Ten slotte is het belangrijk om rekening te houden met de mogelijke verschillen in de manier waarop organisaties SharePoint gebruiken. Sommige hebben mogelijk aangepaste kolommen of metagegevensvelden, waarvoor aanpasbare VBA-scripts nodig zijn. Modulaire coderingspraktijken, zoals het scheiden van API-aanroepen en gegevensopmaak, zorgen ervoor dat uw oplossing kan worden geschaald of kan worden aangepast aan dergelijke variaties. In een vorig project hebben we deze aanpak bijvoorbeeld gebruikt om automatisch samenvattende rapporten te genereren waarin gebruikersactiviteiten rechtstreeks vanuit SharePoint naar Excel-werkmappen werden geaggregeerd. 🚀