wimgielis.com

Pad en bestandsnaam

• 07/01/2012

	Voorbeeldbestanden bij dit artikel:
	Pad en bestandsnaam

Inleiding

In Excel hebben we regelmatig te maken met teksten die we moeten splitsen. Denk maar een cel die zowel voornaam als achternaam bevat, of alfanumerieke codes die je uit elkaar wil halen, of het zoeken van woorden in een tekstbestand. Maar op Excel forums is één van de steeds weerkerende problemen het werken met bestandsnamen en paden. Bijvoorbeeld: hoe splitsen we een volledig pad en bestandsnaam zoals C:\Website Wim\Testbestanden\Bestandsnaam\Voorbeeld.xlsx op in het pad C:\Website Wim\Testbestanden\Bestandsnaam en de bestandsnaam Voorbeeld.xlsx? In dit artikel bekijken we meerdere methoden om dit voor mekaar te krijgen.

Excel formules

Laten we in cel A1 de volledige naam van bestand en pad zetten: C:\Website Wim\Testbestanden\Bestandsnaam\Voorbeeld.xlsx. We geven die cel een naam: locatie. Dan zal deze formule het pad geven:
=LINKS(locatie;VIND.ALLES(TEKEN(1);SUBSTITUEREN(locatie;"\";TEKEN(1);LENGTE(locatie)- LENGTE(SUBSTITUEREN(locatie;"\";""))))-1)
en dit geeft de bestandsnaam:
=RECHTS(locatie;LENGTE(locatie)-VIND.ALLES(TEKEN(1); SUBSTITUEREN(locatie;"\";TEKEN(1);LENGTE(locatie)-LENGTE(SUBSTITUEREN(locatie;"\";"")))))

Wat in deze formules gebeurt, is dat de laatste \ met de SUBSTITUEREN functie vervangen wordt door een speciaal karakter. Vervolgens zoeken we plaats op van dat nieuwe speciale karakter (VIND.ALLES functie) en tot slot nemen we de meest rechtse of linkse karakters. In de plaats van TEKEN(1) mag je ook een ander karakter nemen, zolang het karakter maar niet voorkomt in de locatie. Je kan ook het resultaat van pad of bestandsnaam gebruiken om het andere af te leiden: als je het ene kent, en dus ook hoe lang dat is, dan kan je afleiden hoe lang het andere is.

VBA formules

Hetzelfde (en meer) kunnen we in VBA code doen. In de praktijk zal het werken met bestandsnamen en paden vaker aan de orde zijn in VBA dan in de gewone Excel omgeving. We bekijken achtereenvolgens 4 verschillende oplossingen voor het gestelde probleem. Eerst is er een (weinig spannende) schermafdruk met de resultaten van de functies:

1. InStrRev:

Function PadEnBestand_1(sLocatie As String) As Variant
' Wim Gielis
' https://www.wimgielis.com
'''''
' Custom functions to split up path and filename
' 07/01/12
'''''
'InStrRev

    If Len(Dir(sLocatie)) Then

        sPad = Left$(sLocatie, InStrRev(sLocatie, "\"))
        sBestandsnaam = Right$(sLocatie, Len(sLocatie) - Len(sPad))

        PadEnBestand_1 = Array(sPad, sBestandsnaam)

    Else

        PadEnBestand_1 = Array(CVErr(xlErrNA), "")

    End If

End Function

InStrRev zoekt een bepaald karakter op in een tekst, van achter naar voor. De functie Dir test op het bestaan van het bestand. Is dit niet het geval dan volgt er een foutmelding (NB of Niet Beschikbaar / NA of Not Available). De cel rechts wordt niet ingevuld. Merk op dat we ook Application.PathSeparator in VBA kunnen gebruiken, aangezien een \ karakter niet steeds het scheidingsteken is voor mappen. Je kan de code m.a.w. generieker maken als je wenst. InStrRev wordt vaak vervangen door een kleine lus aangezien de gebruiker de functie niet kent.


2. De code van functie PadEnBestand_2 volgt een gelijkaardige insteek als de Excel formules van hierboven: verwijder of vervang een aantal \ en splits vervolgens het resultaat. Ook deze code is onderdeel van het te downloaden bestand bovenaan de pagina.



3. Er zijn ook manieren om VBA de tekst te laten splitsen en weer samen te nemen. Gebruik de functies Split en Join:

Function PadEnBestand_3(sLocatie As String) As Variant
'Split & Join

    arr = Split(sLocatie, "\")
    sBestandsnaam = arr(UBound(arr))
    arr(UBound(arr)) = ""
    sPad = Join(arr, "\")

    PadEnBestand_3 = Array(sPad, sBestandsnaam)

End Function

Split zet een tekst om in een array die de componenten bevat, in dit geval mappen en bestandsnaam. Split doet het omgekeerde, nl. de inhoud van een array nemen en de componenten achter mekaar zetten met een bepaald scheidingsteken.


4. Vervolgens kan je ook werken met een scripting.filesystemobject object. Zo verschaf je jezelf toegang tot informatie over bestanden, mappen, drives en dergelijke meer.

Function PadEnBestand_4(sLocatie As String) As Variant
'FSO - http://www.w3schools.com/asp/asp_ref_filesystem.asp

    With CreateObject("scripting.filesystemobject")

        If .FileExists(sLocatie) Then

            sBestandsnaam = .GetFileName(sLocatie)
            sPad = .GetParentFolderName(sLocatie)

            PadEnBestand_4 = Array(sPad, sBestandsnaam)

        Else

            PadEnBestand_4 = Array(CVErr(xlErrNA), "")

        End If

    End With

End Function

FileExists, GetFileName en GetParentFolderName toon ik hierboven.
5. Tot slot (en misschien ben ik nog andere methoden vergeten) is er uiteraard Dir():

Function PadEnBestand_5(sLocatie As String) As Variant
'Gebruik Dir() voor het bestand

    sBestandsnaam = Dir(sLocatie)
    sPad = Left$(sLocatie, Len(sLocatie) - Len(sBestandsnaam))

    PadEnBestand_5 = Array(sPad, sBestandsnaam)

End Function

Deze manier geniet mijn voorkeur.

Matrixformules

Bestudeer de VBA functies goed: de uitkomst van deze 4 nieuwe functies in telkens een Array: een matrix bestaande uit 1 rij en 2 kolommen. Dit heeft gevolgen voor de manier van ingeven in een Excel werkblad. Referend aan de afbeelding hogerop in het artikel, zien we dat de matrixformule voor cel A9 is: =PadEnBestand_1(locatie). Dit is tevens de formule voor B9! Wat meer is, je mag de { } karakters niet intypen. Dit is de exacte werkwijze:

1. Selecteer A9:B9 (want de functie geeft een matrix van 1 bij 2 cellen terug)
2. Druk op F2 of zet de cursor in formulebalk en plak de formule. Of type de formule zelf in. De formule geef je in zonder { }
3. Druk NIET op Enter om de formule te bevestigen. Maar druk Ctrl + Shift + Enter. Excel duidt aan dat het om een matrixformule gaat door het plaatsen van de { }. Dat doet Excel m.a.w. zelf.
4. Dat is het, je bent klaar.

Ik had ook 2 aparte functies kunnen nemen; 1 functie voor het pad en 1 functie voor de bestandsnaam. Ik verkoos echter om deze meer geavanceerde mogelijkheid te laten zien. Een UDF declareren als Variant geeft een aantal mogelijkheden. De uitkomst kan een matrix bevatten (zoals hier uiteengezet). Of kan verschillende andere data types huizen: tekst, getallen, foutmeldingen, bereiken, …

Nu je dit weet hoef je je dus niet meer te beperken tot String, Integer of Range.

• « Vorig Excel artikel
• Volgend Excel artikel »