Legge til dynamiske brukernavn i Excel-bunntekst fra SharePoint-maler

Spore brukeraktivitet i SharePoint-koblede Excel-maler

Se for deg et travelt kontor hvor flere brukere får tilgang til den samme SharePoint-malen for å sende inn skjemaene sine. 🖥️ Utfordringen oppstår når en revisor skal identifisere hvem som har fylt ut og sendt inn et spesifikt skjema. Mens SharePoint logger denne informasjonen under «skaper»-kolonnen, forblir behovet for å skrive ut en papirkopi med brukerens navn i bunnteksten av Excel-arket ikke oppfylt.

Denne oppgaven blir vanskeligere fordi standard VBA fungerer som Application.UserName og Environ("Brukernavn") peker ofte på den opprinnelige malskaperen eller den lokale maskinbrukeren, i stedet for at den faktiske brukeren redigerer skjemaet. Som sådan blir det avgjørende å finne en pålitelig metode for dynamisk å sette inn riktig brukernavn.

I virkelige scenarier kan dette avviket føre til unøyaktigheter i revisjon og sporing. For eksempel, i min forrige rolle, hadde vi et problem der skjemaer utfylt av eksterne kontraktører alltid viste administratorens brukernavn på utskriften, noe som forårsaket betydelig forvirring under revisjoner.

Denne artikkelen fordyper deg i hvordan du kan omgå disse hindringene ved å bruke VBA, SharePoint-integrasjon og noen smarte justeringer. Til slutt vil du ha en praktisk løsning som sikrer at hvert trykt skjema reflekterer den individuelle brukeren som sendte det inn. La oss dykke inn! 🔍

Tilpasse Excel-bunntekst med dynamiske SharePoint-brukernavn

Løsningene som presenteres tar sikte på å dynamisk hente og vise SharePoint "skaper" brukernavn i bunnteksten i et Excel-regneark. Dette kravet oppstår i scenarier der flere brukere sender inn skjemaer basert på en delt mal lagret i SharePoint, og revisorer trenger tydelig attribusjon. Det første skriptet bruker Excels opprinnelige Sideoppsett funksjonalitet for å tilpasse bunnteksten dynamisk. Ved å kombinere VBA-metoder med SharePoint-metadatatilgang, sikrer dette skriptet at bunnteksten gjenspeiler brukernavnet til brukeren som fylte ut skjemaet, ikke den opprinnelige skaperen.

For eksempel utnytter den første løsningen ListObjects.Add for å etablere en direkte forbindelse til SharePoints dokumentbibliotek. Denne kommandoen trekker metadata inn i arbeidsboken, noe som gjør det mulig å iterere gjennom rader og trekke ut "skaper"-feltet. Se for deg en avdeling som sender inn samsvarsskjemaer – bunnteksten til hver innsending vil tydelig identifisere den ansvarlige medarbeideren, og eliminere revisjonsuklarheter. Denne metoden sikrer fleksibilitet og forhindrer manuell intervensjon for å identifisere skjemabidragsytere. 🚀

Den andre tilnærmingen drar fordel av SharePoints REST API. Ved å bruke CreateObject("MSXML2.XMLHTTP") kommandoen, starter skriptet en HTTP-forespørsel for å hente metadata direkte. Denne metoden er spesielt nyttig i miljøer der SharePoint-biblioteker er komplekse eller inneholder mange felt. Parser JSON-responsen med funksjoner som InStr og Midt tillater presis utvinning av "skaper"-feltet. I min tidligere rolle strømlinjeformet et lignende skript skjemasporing, og sparte timer med manuell avstemming hver måned. 🖋️

Det endelige skriptet integrerer Office 365-egenskaper ved å bruke BuiltinDocumentProperties kommando for å få direkte tilgang til arbeidsbokens metadata. Dette skriptet er best egnet for organisasjoner som bruker Office 365 i stor utstrekning og trenger en lett løsning uten REST API-kompleksitet. Hvert skript har modulære funksjoner, noe som gjør dem gjenbrukbare for andre SharePoint-integrerte arbeidsflyter. Du kan for eksempel tilpasse dem til å inkludere tidsstempler for innsending eller til og med avdelingsnavn, og forbedre revisjonsverktøyet ytterligere.

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

Integrering av SharePoint-data med Excel VBA for forbedret revisjon

Et ofte oversett aspekt ved å integrere Excel med SharePoint er den sømløse flyten av metadata mellom de to plattformene. Ved å bruke VBA kan du gå utover grunnleggende automatisering for å trekke ut viktige metadatafelt, for eksempel brukernavn av personen som fullfører en mal, og bruk dem i egendefinerte Excel-bunntekster eller topptekster. Denne funksjonaliteten er viktig i scenarier som overholdelse, der hvert innsendte skjema trenger en tydelig attribusjon til personen som er ansvarlig for utfyllingen.

En annen nyttig tilnærming innebærer å utnytte SharePoints omfattende metadatafunksjoner. For eksempel kan kolonner som "Endret av" eller "Sist endret" gi ekstra kontekst for sporing og verifisering. Ved å trekke disse dataene dynamisk gjennom VBA, gjenspeiler Excel-malene ikke bare nøyaktig brukerinformasjon, men reduserer også sannsynligheten for manuelle inntastingsfeil. Denne funksjonen er spesielt verdifull i teamprosjekter, der flere brukere samarbeider om delte maler. 🖇️

Til slutt er det viktig å ta hensyn til potensielle variasjoner i hvordan organisasjoner bruker SharePoint. Noen kan ha egendefinerte kolonner eller metadatafelt, som krever tilpasningsdyktige VBA-skript. Modulær kodingspraksis, som å skille API-kall fra dataformatering, sikrer at løsningen din kan skaleres eller justeres til slike variasjoner. I et tidligere prosjekt brukte vi for eksempel denne tilnærmingen til automatisk å generere sammendragsrapporter som samlet brukeraktivitet direkte fra SharePoint til Excel-arbeidsbøker. 🚀