• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Overzicht maken van alle excelbestanden op de pc

Status
Niet open voor verdere reacties.
E

Evelthoven

Gebruiker
Lid geworden
26 sep 2006
Berichten
690
Goedenavond,

Ik zou graag een excelbestand willen maken met daarin een overzicht van alle excelbestanden die ik op mijn laptop heb staan. Het zijn er inmiddels zoveel geworden in de loop van de jaren dat ik helemaal niet meer weet wat ik allemaal heb.
Er is een C-schijf, D-schijf (een USB stick) en een E-schijf (een externe harde schijf). De bestanden staan per schijf verdeeld over meerdere directories en subdirectories.

Ik ben hier al langere tijd mee bezig en van een kennis heb ik de volgende suggestie gekregen om dit in eerste instantie aan te pakken via een DOS-commando.
Dit doe ik alsvolgt per schijf:
CMD
CD..
C:\>
DIR *XL* /s /b d>C:\Users\Velthoven\Documents\xls.bestanden.txt

Als dit gebeurd is dan open ik een nieuw bestand in excel met de extensie .txt en na vele bewerkingen krijg ik dan een overzicht in excel. Zoals aangegeven moet ik dit doen per schijf en dan alles samenvoegen en bewerken. Alles is heel bewerkelijk met natuurlijk een grote kans op fouten.

Mijn vraag is nu of iemand een andere methode heeft/kent om dit te realiseren. Graag wil ik een belangrijke kolom toevoegen met daarin per rij een hyperlink naar het bestand op C, D of E. Ik heb dat nu ook al maar zoals al aangegeven het is ontzettend bewerkelijk. En het grootste nadeel is dat er geen nieuwe bestanden die ik later heb aangemaakt worden toegevoegd aan deze lijst. Dus eigenlijk is het nooit compleet en dat zou ik heel graag willen.
Met wat ik nu heb, ik heb er een tabel van gemaakt, ben ik al behoorlijk tevreden. Vooral de hyperlinks zijn fijn want dan wordt bij het aanklikken meteen het bijbehorende excelbestand geopend. Maar mochten er betere methoden zijn dan zou ik daar natuurlijk liever mee gaan werken en vooral als automatisch de nieuwe bestanden toegevoegd worden aan de lijst.

Ik ben heel benieuwd of er mogelijkheden hiervoor zijn. Het heeft absoluut geen haast.
(ik werk met Windows 10, Office 365 en een 64-bits laptop).

Voor de volledigheid heb ik een klein deeltje van mijn huidige overzicht in excel als bestand bijgesloten.

Alvast bedankt voor alle eventuele reacties.

Groeten,
Eric
 

Bijlagen

Met totalcommander kun je zoeken op excel files.

Als je die dan selecteert en aangeeft dat je ze met pad wilt kopiëren kun je dat in excel plakken.
Het zal misschien precies aan je behoeften voldoen, maar het gaat wel sneller. Als je het dan in diverse tabbladen plakt kun je het bestand op diverse manieren bewerken en als het aan je wensen voldoet samenvoegen.
Hier een paar afbeeldingen hoe het gaat.
Bekijk bijlage 353873Bekijk bijlage 353875Bekijk bijlage 353876Bekijk bijlage 353877
 
Dit is een voorbeeld van hoe je de bestanden van 1 folder zoekt en onder elkaar zet in je excel tabblad.

Code: 
Sub j()
Dim folderName As String
 folderName = "C:\Users\....\....\....\"
  With CreateObject("Scripting.FileSystemObject")
     Set Folder = .GetFolder(folderName)
      For Each File In Folder.Files
'        Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1) = File.Name
         Debug.Print File.Name
      Next
   Set Folder = Nothing
  End With
End Sub
 
Incl. onderliggende mappen, maar vraag me af of dit de vraag is.

Code: 
Sub hsv()
dim a
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\.... \.... \*.xls""/b/o:d/s").stdout.readall, vbCrLf) 
Cells(1).Resize(UBound(a)) = Application.Transpose(a)
End Sub
 
Laatst bewerkt:
Beste mensen,

Allereerst bedankt voor alle reacties. Ik moet nog verschillende dingen gaan testen, maar met de macro van HSV is er al een goed begin.
Ik krijg nu een lijst te zien van de excelbestanden in de directory Excelbes en uit de subdirectories. Wat nu nog heel mooi zou zijn is dat ik nu op een eenvoudige en snelle manier per regel een hyperlink zou kunnen maken. Als ik dan op de cel klik waarin de koppeling staat dat dan meteen het bijbehorende bestand wordt geopend. Is dit nog te maken?
 
=Hyperlink(A1)
 
Code: 
Sub hsv()
Dim a, cl As Range
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\....\....\*.xls""/b/o:d/s").stdout.readall, vbCrLf) 'incl. bestanden in onderliggende mappen door /s
Cells(1).Resize(UBound(a)) = Application.Transpose(a)
For Each cl In Cells(1).CurrentRegion
  ActiveSheet.Hyperlinks.Add cl, cl.Text, , , cl.Text
Next cl
End Sub
 
Zoals aangegeven moet ik dit doen per schijf en dan alles samenvoegen en bewerken. Alles is heel bewerkelijk met natuurlijk een grote kans op fouten. Mijn vraag is nu of iemand een andere methode heeft/kent om dit te realiseren.
Klik om te vergroten...

Als jij dat graag wilt op die manier dan moet je het zo doen als jij omschrijft. Echter naar mijn mening kan het een grote tijdverkwisting zijn/worden. Waarom? Immers de structuur, bestandsnamen en paden veranderen met de dag en dan moet je dat weer aanpassen in jouw lijst, dat schrijf je zelf ook. En je moet dan wel een heel goede specialist in vba zijn en waarschijnlijk heel veel hulp krijgen van vba specialisten op dit forum om dat voor elkaar te krijgen wat jij wilt.

Maar goed, is het werken met vba leuk en wil je er tijd instoppen en kennis vergaren, dan moet je het doen. vba is vaak boven mijn niveau en kost mij te veel tijd en daarom werk ik met de Zoekmachine Everything van Voidtools. Die kan zoekresultaten exporteren naar een csv-bestand en daar kun je die bewerken. Je moet wel even de menu's/tabbladen van Everything nalopen, dan weet je waar wat zit. Als je dat doet dan kan ik je verzekeren dat je een hoop plezier van het programma hebt. Ik blijf echter vraagtekens zetten bij het vastleggen van de structuur in een bestand. Everything laat je met de juiste zoekfuncties en filters binnen een seconde zien wat je wilt en dat van iedere schijf en alles gesorteerd zoals jij dat wilt. Dat zijn de feiten.
 
Laatst bewerkt:
Goedenavond,

Met jullie hulp is het gelukt. Iedereen bedankt voor alle hulp.
Met de macro van HSV ben ik verder gegaan. Ik krijg nu supersnel alle bestanden te zien. Ik heb 3 tabbladen gemaakt (C-schijf, D-schijf en E-schijf) en voor elk tabblad het ik via invoegen vanuit ontwikkelaars een knop toegevoegd om per schijf de bestanden op te kunnen halen. Voor elk tabblad de macro een kleine aanpassing gedaan en verder heb ik (met behulp van Macro opnemen) een sorteermogelijkheid eraan toegevoegd aan kolom A.

Nu heb ik toch nog een aanvullende vraag:
Is het mogelijk om bij het ophalen van alle bestandsnamen van de schijven (in de verkenner) ook nog als info de datum in de 2e kolom "Gewijzigd op" mee te kunnen nemen in de macro. Als dat kan dan kan ik ook nog sorteren op datum, zodat ik kan zien welke bestanden ik als laatste heb gebruikt? De kolom "Gewijzigd op" in de verkenner is steeds de tweede kolom, dus onmiddellijk na de kolom "Naam".
Als dat heel veel werk is dan hoeven jullie er niet teveel energie in te steken, want met wat ik nu al heb ben ik supertevreden!

Voor de volledigheid geef ik hieronder de precieze code die ik nu gebruik. Hopelijk geeft dit wat meer duidelijkheid.


Code: 
Sub hsvschijfC()
Dim a
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\velthoven\documents\excelbes\*.xls*""/b/o:d/s").stdout.readall, vbCrLf)
Cells(2, 1).Resize(UBound(a)) = Application.Transpose(a)
Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    ActiveWorkbook.Worksheets("C-schijf").ListObjects("tblCschijf").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("C-schijf").ListObjects("tblCschijf").Sort.SortFields _
        .Add2 Key:=Range("A1:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("C-schijf").ListObjects("tblCschijf").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub hsvschijfD()
Dim a
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""D:\*.xls*""/b/o:d/s").stdout.readall, vbCrLf)
Cells(2, 1).Resize(UBound(a)) = Application.Transpose(a)
Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    ActiveWorkbook.Worksheets("D-schijf").ListObjects("tblDschijf").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("D-schijf").ListObjects("tblDschijf").Sort.SortFields _
        .Add2 Key:=Range("A1:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("D-schijf").ListObjects("tblDschijf").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub hsvschijfE()
Dim a
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""E:\*.xls*""/b/o:d/s").stdout.readall, vbCrLf)
Cells(2, 1).Resize(UBound(a)) = Application.Transpose(a)
Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    ActiveWorkbook.Worksheets("E-schijf").ListObjects("tblEschijf").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("E-schijf").ListObjects("tblEschijf").Sort.SortFields _
        .Add2 Key:=Range("A1:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("E-schijf").ListObjects("tblEschijf").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Gesorteerd door o:n + laatst opgeslagen.

Code: 
Sub hsv()
Dim a, cl As Range
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\....\....\*.xls""/b/[COLOR=#ff0000]o:n[/COLOR]/s").stdout.readall, vbCrLf) 'incl. bestanden in onderliggende mappen door /s + gesorteerd
Cells(1).Resize(UBound(a)) = Application.Transpose(a)
  For Each cl In Cells(1).CurrentRegion.Columns(1).SpecialCells(2)
     ActiveSheet.Hyperlinks.Add cl, cl.Text, , , cl.Text
    cl.Offset(, 1) = FileDateTime(cl)
  Next cl

End Sub
 
Goedemiddag,

Allereerst bedankt voor uw reactie.

Het gaat bijna goed.
De code ziet er nu zo uit:

Code: 
Sub hsv()
Dim a, cl As Range
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\velthoven\documents\excelbes\*.xls*").stdout.readall, vbCrLf) 'incl. bestanden in onderliggende mappen door /s + gesorteerd
Cells(1).Resize(UBound(a)) = Application.Transpose(a)
  For Each cl In Cells(1).CurrentRegion.Columns(1).SpecialCells(2)
     ActiveSheet.Hyperlinks.Add cl, cl.Text, , , cl.Text
    cl.Offset(, 1) = FileDateTime(cl)
  Next cl

End Sub

Graag een paar opmerkingen:
1. de regel:
Code: 
cl.Offset(, 1) = FileDateTime(cl)
wordt geel. Er is dus een foutmelding.
2. het resultaat geeft nu de bestanden te zien inclusief datum van de hoofddirectory EXCELBES, maar niet meer van de subdirectories. Dat was
oorspronkelijk wel het geval.
3. indien mogelijk mogen de cijfers vóór de bestandsnamen (ik neem aan de grootte) wegblijven. (dus 22.902, 23.721 enz.)

Een deel van het resultaat zie er nu zo uit:
13-10-2019 13:21 22.902 01 Rooster, test 061019.xlsx
13-10-2019 18:03 23.721 01 Rooster, test 131019.xlsx
17-11-2019 17:50 34.771 01 Rooster, test 1711019.xlsm
09-01-2014 22:01 120.822 2014-calendar-with-event-planner-monday-week.xlsx
09-01-2014 22:12 121.135 2014-calendar-with-event-planner-v2.xlsx
25-01-2019 17:10 41.472 Aantal datums per maand.xls
17-01-2020 22:43 183.189 Aantal weekdagen in een ingestelde periode.xlsx
 
Bestudeer mijn code nog een goed.

De regel a=................ is incompleet.
 
Hoera!
Ik denk dat ik heb het gevonden heb. Ik was in betreffende regel een deel vergeten n.l. /b/o:d/s". Nu dit eraan toegevoegd is werkt de macro en krijg ik een fantastisch overzicht. Nu ga ik nog proberen via het opnemen van een macro de datum en tijd, die nu aan elkaar staan los te maken en aan de macro toe te voegen zodat er 2 kolommen zijn. Ik denk bij het opnemen via Gegevens, en dan Tekst naar Kolommen.

Bedankt HSV. Mocht het niet lukken dan kom ik er nog even op terug.
 
Er worden al twee kolommen aangemaakt.
De tweede met de datum.
 
Ik durf het bijna niet vragen, maar toch zou ik nog graag één kleine aanpassing willen.
Nu wordt de lijst gemaakt vanaf cel A1, maar ik zou hem graag willen laten beginnen in A2.
Ik heb het geprobeerd met:
Code: 
cl.Offset(2, 1) = FileDateTime(cl)
maar dat lukt niet. Dan gaat hij toch gewoon naar A1.
Wat is wel de juiste aanpassing?
Ik ben een complete leek op het gebied van macro's, maar ik probeer altijd met knip- en plakwerk en info van internet toch altijd iets voor elkaar te krijgen. Ik ben benieuwd.
 
Ik zag al dat je een Listobject gebruikte, maar wilde daar nog geen gehoor aan geven.

Code: 
Sub hsv()
Dim a, cl As Range
a = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\velthoven\documents\excelbes\*.xls""/b/o:n/s").stdout.readall, vbCrLf) 'incl. bestanden in onderliggende mappen door /s + gesorteerd A_Z  door o:n
With ActiveSheet.ListObjects(1)
 If .ListRows.Count > 0 Then .DataBodyRange.Delete
  .ListRows.Add.Range.Resize(UBound(a), 1) = Application.Transpose(a)
     For Each cl In .ListColumns(1).DataBodyRange
       .Parent.Hyperlinks.Add cl, cl.Text, , , cl.Text
       cl.Offset(, 1) = FileDateTime(cl)
     Next cl
End With
End Sub
 
Dag HSV,

Wat je me schrijft is helaas volledig Abacadabra.
Maar ik heb het nu geprobeerd met je aangepaste macro. Eerst via uitvoeren van de macro, maar dan blijft het scherm leeg.
Daarna ben ik gegaan naar Visual Basic en heb hier via Uitvoeren geprobeerd wat er gebeurde.

Nu werd de regel
Code: 
With ActiveSheet.ListObjects(1)
geel en verscheen er een foutmelding. De melding is: Fout 9 tijdens uitvoering: Het subscript valt buiten het bereik.

Nogmaals: als het je teveel tijd kost dan laat het verder maar zitten, ondanks dat ik dat heel jammer zou vinden, want ik ben al zeer goed geholpen en ik wil niet nog meer beslag op je tijd leggen. En zijn meerdere mensen met vragen die graag een antwoord hebben.
 
Maak even een tabel aan van twee rijen of meer en twee kolommen breed met kopteksten.

Nb. Anders de Listobjects(1) veranderen in de naam die jij er aan gegeven hebt → Listobjects("tblCschijf")
 
Laatst bewerkt:
Beste HSV,

Ik heb de tabel aangemaakt en toen de macro uitgevoerd en het werkt perfect!

Nogmaals, dank voor alles.

Ik zal hem nog even open laten staan en als ik geen vragen meer heb dan zet ik deze vraag z.s.m. op Opgelost.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan