Použitie VBA FileSystemObject (FSO) v programe Excel - jednoduchý prehľad a príklady

Keď používame VBA v Exceli, väčšinou ide o automatizáciu našich úloh.

To tiež znamená, že väčšinu času pracujeme s bunkami a rozsahmi, pracovnými listami, zošitmi a inými objektmi, ktoré sú súčasťou aplikácie Excel.

VBA je však oveľa výkonnejší a dá sa použiť aj na prácu s vecami mimo Excelu.

V tomto tutoriále vám ukážem, ako používať VBA FileSystemObject (FSO) na prácu so súbormi a priečinkami na vašom systéme alebo sieťových jednotkách.

Čo je to VBA FileSystemObject (FSO)?

Program FileSystemObject (FSO) vám umožňuje prístup k systému súborov vášho počítača. Pomocou neho môžete pristupovať k súborom/priečinkom/adresárom vo vašom počítačovom systéme a upravovať ich.

Nasledujú napríklad niektoré z vecí, ktoré môžete vykonávať pomocou programu FileSystemObject v programe Excel VBA:

• Skontrolujte, či existuje súbor alebo priečinok.
• Vytvorte alebo premenujte priečinky/súbory.
• Získajte zoznam všetkých názvov súborov (alebo názvov podpriečinkov) v priečinku.
• Skopírujte súbory z jedného priečinka do druhého.

Dúfam, že pochopíte.

Všetky tieto vyššie uvedené príklady (a ďalšie) pokryjem neskôr v tomto návode.

Aj keď niektoré z vyššie uvedených vecí je možné vykonať aj pomocou tradičných funkcií VBA (ako je funkcia DIR) a metód, viedlo by to k dlhším a komplikovanejším kódom. Program FileSystemObject uľahčuje prácu so súbormi a priečinkami a zároveň udržuje kód čistý a krátky.

Poznámka: FSO je možné použiť iba v programe Excel 2000 a novších verziách.

K akým všetkým objektom máte prístup prostredníctvom FileSystemObject?

Ako som už uviedol vyššie, k súborom a priečinkom môžete pristupovať a upravovať ich pomocou FileSystemObject vo VBA.

Nasleduje tabuľka, ktorá zobrazuje najdôležitejšie objekty, ku ktorým máte prístup a upravovať ich pomocou FSO:

Objekt	Popis
Drive	Drive Object vám umožňuje získať informácie o jednotke, napríklad či existuje alebo nie, názov cesty, typ disku (vymeniteľný alebo pevný), jeho veľkosť atď.
Priečinok	Objekt priečinok vám umožňuje vytvárať alebo upravovať priečinky vo vašom systéme. Pomocou tohto objektu môžete napríklad vytvárať, mazať, premenovávať, kopírovať priečinky.
Súbor	File Object vám umožňuje pracovať so súbormi vo vašom systéme. Pomocou tohto objektu môžete napríklad vytvárať, otvárať, kopírovať, presúvať a odstraňovať súbory.
TextStream	Objekt TextStream vám umožňuje vytvárať alebo čítať textové súbory.

Každý z vyššie uvedených objektov má metódy, ktoré môžete použiť na prácu s nimi.

Ako príklad uvediem, že ak chcete odstrániť priečinok, použijete metódu DeleteFolder objektu Folder. Podobne, ak chcete skopírovať súbor, použijete metódu CopyFile objektu File.

Nerobte si starosti, ak sa vám to zdá zdrvujúce alebo ťažko pochopiteľné. Oveľa lepšie porozumiete, keď si prejdete príklady, ktorým som sa venoval v tomto návode.

Len na referenčné účely som na konci tohto tutoriálu pokryl všetky metódy FileSystemObject (pre každý objekt).

Povolenie FileSystemObject v programe Excel VBA

Program FileSystemObject nie je v programe Excel VBA predvolene k dispozícii.

Pretože máme do činenia so súbormi a priečinkami, ktoré sú mimo aplikácie Excel, musíme najskôr vytvoriť odkaz na knižnicu, ktorá obsahuje tieto objekty (jednotky, súbory, priečinky).

Teraz existujú dva spôsoby, ako môžete začať používať FileSystemObject v programe Excel VBA:

1. Nastavenie odkazu na runtime knižnicu Microsoft Scripting (Scrrun.dll)
2. Vytvorenie objektu, ktorý bude odkazovať na knižnicu zo samotného kódu

Aj keď obe tieto metódy fungujú (a ja vám ukážem, ako to urobiť ďalej), odporúčam použiť prvú metódu.

Poznámka: Keď povolíte FileSystemObject, budete mať prístup ku všetkým objektom v ňom. Patria sem FileSystemObject, Drive, Files, Folders, atď. V tomto návode sa budem zameriavať predovšetkým na FileSystemObject.

Nastavenie odkazu na runtime knižnicu Microsoft Scripting Runtime

Keď vytvoríte odkaz na runtime knižnicu skriptov, umožníte programu Excel VBA prístup ku všetkým vlastnostiam a metódam súborov a priečinka. Akonáhle to urobíte, môžete sa na objekt súborov/priečinkov/jednotiek odkazovať z programu Excel VBA (rovnako ako môžete odkazovať na bunky, pracovné hárky alebo zošity).

Nasledujú kroky na vytvorenie odkazu na knižnicu Microsoft Scripting Runtime Library:

1. V editore VB kliknite na Nástroje.
2. Kliknite na Referencie.
3. V dialógovom okne Referencie, ktoré sa otvorí, si posuňte dostupné referencie a začiarknite možnosť „Microsoft Scripting Runtime“.
4. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by vám teraz umožnili odkazovať na objekty FSO z programu Excel VBA.

Vytvorenie inštancie FileSystemObject v kóde

Akonáhle nastavíte odkaz na knižnicu Scripting FileSystemObject, musíte vo svojom kóde vytvoriť inštanciu objektu FSO.

Akonáhle je tento vytvorený, môžete ho použiť vo VBA.

Nasleduje kód, ktorý nastaví premennú objektu MyFSO na objekt FileSystemObject:

Sub CreatingFSO () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject End Sub

V tomto kóde som najskôr deklaroval premennú MyFSO ako objekt typu FileSystemObject. Je to možné len preto, že som vytvoril odkaz na runtime knižnicu Microsoft Scripting Runtime. Ak odkaz nie je vytvorený, dôjde k chybe (pretože Excel nerozpozná, čo znamená FileSystemObject).

V druhom riadku sa stávajú dve veci:

1. Kľúčové slovo NOVÉ vytvára inštanciu FileSystemObject. To znamená, že teraz môžem používať všetky metódy programu FileSystemObject na prácu so súbormi a priečinkami. Ak nevytvoríte túto inštanciu, nebudete mať prístup k metódam FSO.
2. Kľúčové slovo SET nastavuje objekt MyFSO na túto novú inštanciu FileSystemObject. To mi umožňuje používať tento objekt na prístup k súborom a priečinkom. Ak napríklad potrebujem vytvoriť priečinok, môžem použiť metódu MyFSO.CreateFolder.

Ak chcete, môžete vyššie uvedené dva výroky tiež skombinovať do jedného, ​​ako je uvedené nižšie:

Sub CreatingFSO () Dim MyFSO as New FileSystemObject End Sub

Veľkou výhodou použitia tejto metódy (ktorou je nastavenie odkazu na knižnicu Microsoft Scripting Runtime Library) je, že keď vo svojom kóde použijete objekty FSO, budete môcť používať funkciu IntelliSense, ktorá zobrazuje metódy a vlastnosti súvisiace s predmet (ako je uvedené nižšie).

To nie je možné, keď vytvoríte referenciu z kódu (popísané ďalej).

Vytvorenie objektu z kódu

Ďalší spôsob, ako vytvoriť odkaz na FSO, je urobiť to z kódu. Pri tejto metóde nemusíte vytvárať žiadne referencie (ako sa to robilo v predchádzajúcej metóde).

Pri písaní kódu môžete vytvoriť objekt z kódu a obrátiť sa na Scripting.FileSystemObject.

Nasledujúci kód vytvorí objekt FSO a potom z neho urobí typ FileSystemObject.

Sub FSODemo () Dim FSO as Object Set FSO = CreateObject ("Scripting.FileSystemObject") End Sub

Aj keď sa to môže zdať pohodlnejšie, veľkou nevýhodou použitia tejto metódy je, že pri práci s objektmi vo FSO sa nezobrazí IntelliSense. Pre mňa je to obrovské negatívum a vždy odporúčam použiť predchádzajúci spôsob povolenia FSO (tj. Nastavením odkazu na „Microsoft Scripting Runtime“)

Príklady VBA FileSystemObject

Teraz sa ponoríme a pozrime sa na niekoľko praktických príkladov použitia programu FileSystemObject v programe Excel.

Príklad 1: Skontrolujte, či existuje súbor alebo priečinok

Nasledujúci kód skontroluje, či priečinok s názvom „Test“ existuje alebo nie (v uvedenom umiestnení).

Ak priečinok existuje, podmienka IF je True a v poli so správou sa zobrazí správa „Priečinok existuje“. A ak neexistuje, zobrazí sa správa - Priečinok neexistuje “.

Sub CheckFolderExist () Dim MyFSO as FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Then MsgBox "The Folks Exists" Else MsgBox "Priečinok neexistuje" Koniec, ak je koniec Sub

Podobne môžete tiež skontrolovať, či súbor existuje alebo nie.

Nasledujúci kód kontroluje, či sa v zadanom priečinku nachádza súbor s názvom Test.xlsx alebo nie.

Sub CheckFileExist () Dim MyFSO as FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists ("C: \ Users \ sumit \ Desktop \ Test \ Test.xlsx") Then MsgBox "The File Exists" Else MsgBox "Súbor neexistuje „End If End Sub

Príklad 2: Vytvorte nový priečinok v určenom umiestnení

Nasledujúci kód by vytvoril priečinok s názvom „Test“ na jednotke C môjho systému (budete musieť zadať cestu vo svojom systéme, kde chcete priečinok vytvoriť).

Sub CreateFolder () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test") End Sub

Aj keď tento kód funguje dobre, v prípade, že priečinok už existuje, zobrazí sa chyba.

Nasledujúci kód kontroluje, či priečinok už existuje, a pokiaľ nie, priečinok vytvorí. V prípade, že priečinok už existuje, zobrazí sa správa. Na kontrolu, či priečinok existuje, som použil príkaz Metóda FolderExists FSO.

Sub CreateFolder () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Then MsgBox "The Folder already exist" Else MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test ") End If End Sub

Príklad 3: Získajte zoznam všetkých súborov v priečinku

Nasledujúci kód by zobrazil názvy všetkých súborov v zadanom priečinku.

Sub GetFileNames () Dim MyFSO as FileSystemObject Dim MyFile as File Dim MyFolder As Folder Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder ("C: \ Users \ sumit \ Desktop \ Test") for each MyFile In MyFolder.Files Debug.Print MyFile.Name Next MyFile End Sub

Tento kód je o niečo zložitejší ako tie, ktoré sme už videli.

Ako som už uviedol vyššie v tomto návode, keď sa odvolávate na „knižnicu Microsoft Scripting Runtime Library“, môžete použiť program FileSystemObject aj všetky ostatné objekty (napríklad súbory a priečinky).

Vo vyššie uvedenom kóde používam tri objekty - FileSystemObject, File a Folder. To mi umožňuje prejsť každý súbor v určenom priečinku. Potom použijem vlastnosť name na získanie zoznamu všetkých názvov súborov.

Všimnite si toho, že používam Debug.Print na získanie názvov všetkých súborov. Tieto názvy budú uvedené v bezprostrednom okne editora VB.

Príklad 4: Získajte zoznam všetkých podpriečinkov v priečinku

Nasledujúci kód poskytne názvy všetkých podpriečinkov v zadanom priečinku. Logika je úplne rovnaká ako v predchádzajúcom prípade. Namiesto súborov sme v tomto kóde použili podpriečinky.

Sub GetSubFolderNames () Dim MyFSO As FileSystemObject Dim MyFile as File Dim MyFolder As Folder Dim MySubFolder As Folder Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder ("C: \ Users \ sumit \ Desktop \ Test") For each V MyFolder.SubFolders Debug.Print MySubFolder.Name Ďalej MySubFolder Koniec Sub

Príklad 5: Skopírujte súbor z jedného miesta na druhé

Nasledujúci kód skopíruje súbor z priečinka „Zdroj“ a skopíruje ho do priečinka „Cieľ“.

Sub CopyFile () Dim MyFSO as FileSystemObject Dim SourceFile as String Dim DestinationFolder as String Set MyFSO = New Scripting.FileSystemObject SourceFile = "C: \ Users \ sumit \ Desktop \ Source \ SampleFile.xlsx" DestinationFolder = "C: \ Users \ sumit \ Desktop \ Destination "MyFSO.CopyFile Zdroj: = SourceFile, Cieľ: = DestinationFolder &" \ SampleFileCopy.xlsx "End Sub

Vo vyššie uvedenom kóde som použil dve premenné - SourceFile a DestinationFolder.

Zdrojový súbor obsahuje adresu súboru, ktorý chcem skopírovať, a premenná DestinationFolder obsahuje adresu priečinka, do ktorého chcem súbor skopírovať.

Pri kopírovaní súboru nestačí uviesť názov cieľového priečinka. Musíte tiež zadať názov súboru. Môžete použiť rovnaký názov súboru alebo ho tiež môžete zmeniť. Vo vyššie uvedenom príklade som skopíroval súbor a pomenoval ho SampleFileCopy.xlsx

Príklad 6: Skopírujte všetky súbory z jedného priečinka do druhého

Nasledujúci kód skopíruje všetky súbory zo zdrojového priečinka do cieľového priečinka.

Sub CopyAllFiles () Dim MyFSO As FileSystemObject Dim MyFile as File Dim SourceFolder as String Dim DestinationFolder as String Dim MyFolder as Folder Dim MySubFolder As Folder SourceFolder = "C: \ Users \ sumit \ Desktop \ Source" DestinationFolder = "C: \ Users \ sumit \ Desktop \ Destination "Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder (SourceFolder) For each MyFile In MyFolder.Files MyFSO.CopyFile Source: = MyFSO.GetFile (MyFile), _ Destination: \ DestinationFolder &" "& MyFile.Name, Overwritefiles: = False Next MyFile End Sub

Vyššie uvedený kód skopíruje všetky súbory zo zdrojového priečinka do cieľového priečinka.

Všimnite si toho, že v metóde MyFSO.CopyFile som určil, že vlastnosť ‘Overwritefiles’ je False (toto je predvolene True). To zaisťuje, že v prípade, že už súbor v priečinku máte, nebude skopírovaný (a zobrazí sa chyba). Ak odstránite súbory „Overwritefiles“ alebo ich nastavíte na hodnotu True, v prípade, že sa v cieľovom priečinku nachádzajú súbory s rovnakým názvom, tieto súbory sa prepíšu.

Pro tip: Pri kopírovaní súborov existuje vždy šanca na prepísanie súborov. V tomto prípade je dobré pridať k názvu časovú pečiatku. Zaistíte tak, že názvy budú vždy odlišné a budete môcť ľahko sledovať, ktoré súbory boli v akom čase skopírované.

Ak chcete kopírovať iba súbory s určitou príponou, môžete to urobiť pomocou príkazu IF Then, aby ste skontrolovali, či je prípona xlsx alebo nie.

Sub CopyExcelFilesOnly () Dim MyFSO As FileSystemObject Dim MyFile as File Dim SourceFolder as String Dim DestinationFolder as String Dim MyFolder As Folder Dim MySubFolder As Folder SourceFolder = "C: \ Users \ sumit \ Desktop \ Source" DestinationFolder = "C: \ Users \ sumit \ Desktop \ Destination "Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder (SourceFolder) For each MyFile In MyFolder.Files If MyFSO.GetExtensionName (MyFile) =" xlsx "Then MyFSO.CopyFile Source: = MyFS (MyFile), _ Destination: = DestinationFolder & "\" & MyFile.Name, Overwritefiles: = False End If Next MyFile End Sub

Metódy FileSystemObject (FSO)

Tu sú metódy, ktoré môžete použiť pre každý objekt. Toto je len referenčný účel a príliš si s tým nerobíme starosti. Použitie niektorých z nich bolo ukázané v príkladoch uvedených vyššie.

Metódy FSO	Pre objekt	Popis
DriveExists	Drive	Skontroluje, či jednotka existuje alebo nie
GetDrive	Drive	Vráti inštanciu objektu jednotky na základe zadanej cesty
GetDriveName	Drive	Znova spustí názov jednotky
BuildPath	Priečinok súborov	Vygenerujte cestu z existujúcej cesty a názvu
CopyFile	Priečinok súborov	Skopíruje súbor
GetAbsolutePathName	Priečinok súborov	Vráťte kanonickú reprezentáciu cesty
GetBaseName	Priečinok súborov	Vráťte základný názov z cesty. Napríklad „D: \ TestFolder \ TestFile.xlsm“ vráti súbor TextFile.xlsm
GetTempName	Priečinok súborov	Vygenerujte názov, ktorý možno použiť na pomenovanie dočasného súboru
CopyFolder	Priečinok	Skopíruje priečinok z jedného umiestnenia do druhého
Vytvoriť priečinok	Priečinok	Vytvorí nový priečinok
DeleteFolder	Priečinok	Odstráni zadaný priečinok
FolderExists	Priečinok	Skontroluje, či priečinok existuje alebo nie
GetFolder	Priečinok	Vráti inštanciu objektu priečinka na základe zadanej cesty
GetParentFolderName	Priečinok	Znovu spustí názov nadradeného priečinka na základe zadanej cesty
GetSpecialFolder	Priečinok	Zistite umiestnenie rôznych systémových priečinkov.
MoveFolder	Priečinok	Presúva priečinok z jedného umiestnenia do druhého
Zmazať súbor	Súbor	Odstráni súbor
FileExists	Súbor	Skontroluje, či súbor existuje alebo nie
GetExtensionName	Súbor	Vráti príponu súboru
GetFile	Súbor	Vráti inštanciu objektu súboru na základe zadanej cesty
GetFileName	Súbor	Vráti názov súboru
GetFileVersion	Súbor	Vráti verziu súboru
MoveFile	Súbor	Presunie súbor
CreateTextFile	Súbor	Vytvorí textový súbor
GetStandardStream	Súbor	Načítajte štandardný vstupný, výstupný alebo chybový tok
OpenTextFile	Súbor	Otvorte súbor ako TextStream