Excel: Macro/controle op bestaand bestand

toru

Well-known member
Crowdfunder FE
Ik doe de boekhouding voor een vzw. Er worden heel wat dingen in weggeschreven, maar op één ding blijf ik vastzitten.

Een lijn in de Excel staat voor een inkomst/uitgave (afhankelijk per tabblad). Elke lijn heeft bij gevolgd dus ook een volgnummer.
Dit volgnummer komt terug in een bepaalde mappenstructuur

Stel (simpel voorgesteld):
Datum / Volgnummer / Beschrijving / Bedrag / Controle/ 01/01/2022 / 2022-U-001 / Rekening Gamma / 10 / JA Mappenstructuur Boekhouding > 2022 >> Boekhouding.xlsx > Stukken >> Uitgaven >>> 2022-U-001.pdf >> Inkomsten

Wat ik eigenlijk zou willen is dat die laatste kolom (de JA dus in de Excel) gaat controleren of er een bestand bestaat met als naam het volgnummer. Ongeacht de bestandsextensie. Ik heb geen flauw idee hoe er aan te beginnen. Ik hoef geen uitgebreide oplossing, maar als iemand me op weg kan helpen dan klungel ik liefst zelf verder tot ik heb, zodat ik er ook van leer.
Moest dit ook niet gaan, liefst feedback. Dan weet ik dat ik er niet verder moet aan beginnen :).

Merci!
 
Dat zou een oplossing zijn, echter zijn de bestanden niet definitief.
Elke dag zou er bijvoorbeeld een volgnummer bij kunnen komen en moet er dus ook een bewijsstuk in die map worden gezet. Ik wil eigenlijk een controle in mijn boekhoudexcel die zegt: bewijsstuk aanwezig/niet aanwezig.
Zo kan ik (en toekomst gericht ook andere mensen) in één oogopslag zien waar er nog geen bewijsstuk van voorhanden is. :)
 
Als je Office 365 hebt en je bestanden opslaat in Sharepoint/Onedrive, kan je deze check gemakkelijk doen via Power Automate, de no code automatiseringstool van Microsoft.
 
Ik heb ooit iets gelijkaardigs moeten maken, maar dan moest de macro gaan zoeken naar het xls/txt bestand op de drive, het openen en er informatie uit ophalen. Ik ben ermee begonnen, maar uit frustratie ermee gestopt.
 
Ik vind die mappenstructuur niet helemaal duidelijk en je opdracht niet 100% duidelijk.

  • Staan alle bestanden met volgnummer in \Boekhouding\2022\Uitgaven?
  • Staan je gegevens in een tabel, vast aantal kolommen en namen en dit in alle worksheets?
  • Wil je enkel controleren indien er JA staat of wil je elke dag, voor elke lijn, nagaan of een bestandsnaam met volgnummer gevonden worden en vervolgens JA schrijven?
Ik kan wel helpen, maar niet zo eenvoudig...
 
Laatst bewerkt:
@toru

Even een dagje mijn tanden erop stukgebeten.

Voor elke rij, in elke tabel, in elk werkblad zoekt de code of de waarde van kolom 2 (Volgnummer) bestaat in de bestandsnamen (ongeacht extensie) van een gegeven folder + sub folders en plaatst hij een TRUE/FALSE in kolom 5 (Controle).

Enjoy!

PS: met Power Query in Excel kan je normaliter ook de bestandsnamen lezen van een (sub)folder.

Code:
Option Explicit

    Dim objFiles As Object 'This variable is used in multiple subs.

Sub Controle()

    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rw As Range
 
    'Find all bewijsstukken
    Call Files
 
    'Verify if the volgnummer is found in bewijsstukken
    'Loop worksheets
    For Each ws In wb.Worksheets
        'Loop tables
        For Each tbl In ws.ListObjects
            'Loop rows of 2nd column "Volgnummer"
                For Each rw In tbl.ListColumns(2).DataBodyRange
                    'Function that checks if the row value is in the dictionary and fills in the 5th column "Controle"
                    If FileExists(rw) = True Then
                        rw.Offset(0, 3) = True
                    Else
                        rw.Offset(0, 3) = False
                    End If
                Next rw
        Next tbl
    Next ws
 
End Sub

Sub Files()
'https://stackoverflow.com/questions/33893133/is-it-possible-to-list-all-the-files-and-folders-in-a-custom-directory-excel-v

    Dim strFolder As String
    Dim objFolders As Object, objItem As Object
    Dim i As Long

    'Target folder
    strFolder = "C:\Users\AJD8801\Desktop"

    'Loop through all folders and files
    Set objFolders = CreateObject("Scripting.Dictionary")
    Set objFiles = CreateObject("Scripting.Dictionary")
    objFolders(0) = strFolder
    i = 0
    With CreateObject("Scripting.FileSystemObject")
        Do
            With .GetFolder(objFolders(i))
                For Each objItem In .Files
                    objFiles(objFiles.Count) = objItem.Name 'Bestandsnaam met extensie zit hier
                Next
                For Each objItem In .SubFolders
                    objFolders(objFolders.Count) = objItem.Path
                Next
            End With
            i = i + 1
        Loop Until i = objFolders.Count
    End With
 
'    'Results output to a new worksheet
'    Dim shFiles As Worksheet
'    Set shFiles = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
'    With shFiles
'        .Name = "Files"
'        .Select
'        .Cells.Delete
'        .Range(.Cells(1, 1), .Cells(objFiles.Count, 1)).Value = Application.Transpose(objFiles.Items)
'    End With

End Sub

Function FileExists(rw) As Boolean

    Dim item As Variant
 
    For Each item In objFiles.Items
        If rw = Left(item, InStr(item, ".") - 1) Then
            FileExists = True
            Exit For
        Else
            FileExists = False
        End If
    Next item

End Function

wldPoc2.jpg
 
Laatst bewerkt:
Terug
Bovenaan