Op my eerste dag by 'n klein konsultasiefirma is 'n driedaagse korttermynprojek aan my toegewys.Die werk is eenvoudig.
Daar is baie dopgehou op 'n netwerkaandrywer, elk met honderde lêers.
Ek moes hierdie drie stappe volg:
- Kies die lêer en kopieer die naam daarvan.
- Plak die naam in 'n sel in Excel en druk Enter.
- Gaan na die volgende lêer en herhaal stappe 1 en 2.
Klink eenvoudig, reg?
Dit is eenvoudig en baie tydrowend.
Wat ek in drie dae gedoen het, kan binne minute gedoen word as ek die regte tegniek ken.
In hierdie tutoriaal sal ek jou verskillende maniere wys om die hele proses super vinnig en super maklik te maak (met en sonder VBA).
Beperkings van die metodes wat in hierdie tutoriaal getoon word:Deur die tegniek hieronder te gebruik, sal jy net die name van die lêers in jou tuislêergids kan kry.Jy kry nie die name van lêers in subgidse in die hoofgids nie.Hier is 'n manier om lêername van dopgehou en subgidse te kry met behulp van Power Query
Contents [show]
Gebruik die FILES-funksie om 'n lys lêername uit 'n gids te kry
daarvan gehoorFILES-funksie?
As jy dit nie doen nie, moenie bekommerd wees nie.
Dit is van 'n Excel-sigblad van kleintyd af (weergawe 4-formules).
Alhoewel hierdie formule nie met werkbladselle werk nie, werk dit steeds met benoemde reekse.Ons sal hierdie feit gebruik om 'n lys lêername uit die gespesifiseerde vouer te kry.
Gestel nou jy het 'n lêer op jou lessenaar genaamd " Toetsgids ” en jy wil 'n lys van lêername vir alle lêers in daardie gids kry.
Hier is die stappe wat vir jou die lêername in hierdie gids sal gee:
- Voer in sel A1 die volledige gidsadres in gevolg deur 'n asterisk (*)
- Byvoorbeeld, as jou gids in die C-skyf was, sou die adres iets soos
C:\Users\JOU NAAM\Desktop\Test Folder\* - As jy nie seker is hoe om die gidsadres te kry nie, gebruik die volgende metode:
-
- In die gids waaruit jy die lêernaam wil kry, skep 'n nuwe Excel-werkboek of maak 'n bestaande werkboek in die gids oop en gebruik die volgende formule in enige sel.Hierdie formule sal vir jou die gidsadres gee met 'n asterisk (*) aan die einde.Jy kan nou hierdie adres kopieer-plak (plak as waarde) in enige sel in die werkboek (A1 in hierdie voorbeeld) waar jy die lêernaam wil hê.
=VERVANG(SEL("lêernaam"),VIND("[",SEL("lêernaam")),LEN(SEL("lêernaam")),"*")[As jy 'n nuwe werkboek in 'n vouer geskep het om die formule hierbo te gebruik en die voueradres te kry, sal jy dit dalk moet uitvee sodat dit nie in die lys lêers in daardie vouer is nie]
- In die gids waaruit jy die lêernaam wil kry, skep 'n nuwe Excel-werkboek of maak 'n bestaande werkboek in die gids oop en gebruik die volgende formule in enige sel.Hierdie formule sal vir jou die gidsadres gee met 'n asterisk (*) aan die einde.Jy kan nou hierdie adres kopieer-plak (plak as waarde) in enige sel in die werkboek (A1 in hierdie voorbeeld) waar jy die lêernaam wil hê.
-
- Byvoorbeeld, as jou gids in die C-skyf was, sou die adres iets soos
- Gaan na die "Formules"-oortjie en klik op die "Definieer Name" opsie.
- Gebruik die volgende besonderhede in die Nuwe Naam dialoog
- Naam: FileNameList (kies gerus enige naam waarvan jy hou)
- Bestek: Werkboek
- Verwys na: =LêERS(Blad1!$A$1)
- Om nou 'n lys lêers te kry, sal ons 'n genoemde reeks in die INDEX-funksie gebruik.Gaan na sel A3 (of watter sel jy ook al wil hê die lys name moet begin) en voer die volgende formule in:
=IFERROR(INDEX(LêernaamLys,RY()-2),"") - Sleep dit af en dit sal vir jou 'n lys van alle lêername in die gids gee
Wil u lêers met 'n spesifieke uitbreiding onttrek? ?
As jy alle lêers met 'n spesifieke uitbreiding wil kry, verander net die asterisk met daardie lêeruitbreiding.Byvoorbeeld, as jy net die Excel-lêer wil hê, kan jy *xls* gebruik in plaas van *
Dus is die gidsadres wat u moet gebruikC:UsersSumitDesktopTest Folder*xls*
Net so, vir Word-dokumentlêers, gebruik *doc*
Hoe werk dit?
Die FILES-formule haal die name van alle lêers met die gespesifiseerde uitbreiding in die gespesifiseerde vouer op.
In die INDEX formule gee ons die lêername as 'n skikking en gebruik die ROW funksie om die eerste, tweede, derde, ens. lêername terug te gee.
Let daarop dat ek gebruik hetRY()-2, aangesien ons op die derde reël begin.So wanneer die rynommer 4 is, sal ROW()-2 1 wees vir die eerste instansie, 2 vir die tweede, ensovoorts.
Kry 'n lys van alle lêername uit 'n gids met VBA
Nou moet ek sê dat die bogenoemde metode 'n bietjie ingewikkeld is (daar is baie stappe).
Dit is egter baie beter as om dit met die hand te doen.
As jy egter gemaklik is om VBA te gebruik (of as jy goed is om die presiese stappe te volg wat ek hieronder sal lys), kan jy 'n pasgemaakte funksie (UDF) skep wat maklik die name van alle lêers kan kry.
Voordele van die gebruik van gebruikergedefinieerde funksies (UDF's)是jykansalfunksieStoor in 'n persoonlike makro-werkboek en hergebruik maklik sonder om hierdie stappe weer en weer te herhaal.Jy kan ook byvoegings skep en hierdie funksionaliteit met ander deel.
Laat ek nou eers vir jou die VBA-kode gee wat 'n funksie sal skep om 'n lys van alle lêername van 'n gids in Excel te kry.
Funksie GetFileNames(ByVal FolderPath As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Stel MyFolder = MyFSO. GetFolder(FolderPath) Stel MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Vir elke MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Volgende MyFile GetFileNames = Resultaat Eindfunksie
Die bogenoemde kode sal 'n funksie GetFileNames skep wat in die werkblad gebruik kan word (net soos 'n gewone funksie).
Waar om hierdie kode te plaas?
Volg die stappe hieronder om hierdie kode in die VB-redigeerder te kopieer.
Hoe om hierdie funksie te gebruik?
Hier is die stappe om hierdie kenmerk in 'n werkblad te gebruik:
- Voer in enige sel die voueradres van die vouer in waaruit jy lêername wil lys.
- In die sel waar jy die lys wil hê, voer die volgende formule in (ek het dit in sel A3 ingevoer):
=IFERROR(INDEX(GetFileNames($A$1),RY()-2),"") - Kopieer en plak die formule in die sel hieronder om 'n lys van alle lêers te kry.
Let daarop dat ek die gidsligging in een sel ingevoer het en toe inKry lêernameHierdie sel word in 'n formule gebruik.U kan ook die gidsadres in die formule soos volg hardkodeer:
=IFERROR(INDEX(GetFileNames("C:\Users\JOU NAAM\Desktop\Test Folder"),RY()-2),"")
In die formule hierbo het ons ROW()-2 gebruik en ons begin met die derde ry.Dit verseker dat wanneer ek die formule in die sel hieronder kopieer, dit met 1 sal toeneem.As jy die formule in die eerste ry van die kolom invoer, kan jy eenvoudig ROW() gebruik.
Hoe werk hierdie formule?
Die GetFileNames-formule gee 'n skikking terug wat die name van al die lêers in die gids bevat.
Die INDEX-funksie word gebruik om een lêernaam per sel te lys, begin met die eerste.
Die IFERROR-funksie word gebruik om leeg in plaas van #REF!Fout word vertoon wanneer 'n formule in 'n sel gekopieer word, maar daar is nie meer lêername om te lys nie.
Kry 'n lys van alle lêername met 'n spesifieke uitbreiding met VBA
Bogenoemde formule is nuttig wanneer jy 'n lys van alle lêername van 'n gids in Excel wil kry.
Wat as jy egter net die naam van 'n videolêer wil kry, of net 'n Excel-lêer, of net 'n lêer wat 'n sekere sleutelwoord bevat.
In hierdie geval kan jy 'n effens ander funksie gebruik.
Die kode hieronder sal jou toelaat om alle lêername te kry wat 'n spesifieke sleutelwoord (of 'n spesifieke uitbreiding) in het.
Funksie GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Result As Variant Dim i As Heelgetal Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Stel MyFolder = MyFSO.GetFolder(FolderPath) Stel MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Vir elke MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Resultaat Eindfunksie
Die bogenoemde kode sal 'n funksie skep wat in die werkblad gebruik kan word" GetFileNamesbyExt " (net soos gewone funksies).
Hierdie funksie neem twee parameters - die gidsligging en die uitbreidingsleutelwoord.Dit gee 'n verskeidenheid lêername terug wat ooreenstem met die gegewe uitbreiding.As geen uitbreiding of sleutelwoord gespesifiseer is nie, sal dit alle lêername in die gespesifiseerde vouer terugstuur.
Sintaksis: =GetFileNamesbyExt("Folder Location","Extension")
Waar om hierdie kode te plaas?
Volg die stappe hieronder om hierdie kode in die VB-redigeerder te kopieer.
- Gaan na die ontwikkelaaroortjie.
- Klik op die Visual Basic-knoppie.Dit sal die VB-redigeerder oopmaak.
- In die VB-redigeerder, regskliek op enige voorwerp in die werkboek waaraan jy werk, gaan na Insert en klik Module.As jy nie Project Explorer sien nie, gebruik die sleutelbordkortpad Control + R (hou beheer in en druk "R").
- Dubbelklik op die module-objek en kopieer en plak die bogenoemde kode in die modulekode-venster.
Hoe om hierdie funksie te gebruik?
Hier is die stappe om hierdie kenmerk in 'n werkblad te gebruik:
- Voer in enige sel die voueradres van die vouer in waaruit jy lêername wil lys.Ek het dit in sel A1 ingevoer.
- Voer in die sel die uitbreiding (of sleutelwoord) in wat jy wil hê dat alle lêername moet wees.Ek het dit in sel B1 ingevoer.
- In die sel waar jy die lys wil hê, voer die volgende formule in (ek het dit in sel A3 ingevoer):
=IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),RY()-2),"") - Kopieer en plak die formule in die sel hieronder om 'n lys van alle lêers te kry.
Wat van jouEnige Excel-truuks wat jy gebruik om jou lewe te vereenvoudig.Ek sal graag by jou wil leer.Deel dit in die kommentaar afdeling!
