Kiedy używamy VBA w Excelu, większość z nich to automatyzacja naszych zadań.
Oznacza to również, że przez większość czasu pracujemy z komórkami i zakresami, arkuszami, skoroszytami i innymi obiektami, które są częścią aplikacji Excel.
Ale VBA jest o wiele potężniejszy i może być również używany do pracy z rzeczami spoza programu Excel.
W tym samouczku pokażę, jak używać VBA FileSystemObject (FSO) do pracy z plikami i folderami w systemie lub dyskach sieciowych.
Co to jest obiekt systemu plików VBA (FSO)?
FileSystemObject (FSO) umożliwia dostęp do systemu plików komputera. Za jego pomocą możesz uzyskać dostęp i modyfikować pliki/foldery/katalogi w swoim systemie komputerowym.
Na przykład poniżej znajdują się niektóre z rzeczy, które możesz zrobić za pomocą FileSystemObject w Excel VBA:
- Sprawdź, czy istnieje plik lub folder.
- Utwórz lub zmień nazwy folderów/plików.
- Uzyskaj listę wszystkich nazw plików (lub nazw podfolderów) w folderze.
- Skopiuj pliki z jednego folderu do drugiego.
Mam nadzieję, że wpadłeś na pomysł.
Omówię wszystkie powyższe przykłady (plus więcej) w dalszej części tego samouczka.
Chociaż niektóre z wymienionych powyżej rzeczy można również wykonać za pomocą tradycyjnych funkcji VBA (takich jak funkcja DIR) i metod, prowadziłoby to do dłuższych i bardziej skomplikowanych kodów. FileSystemObject ułatwia pracę z plikami i folderami, zachowując jednocześnie przejrzysty i krótki kod.
Uwaga: FSO można używać tylko w programie Excel 2000 i nowszych wersjach.
Do jakich wszystkich obiektów można uzyskać dostęp za pośrednictwem FileSystemObject?
Jak wspomniałem powyżej, możesz uzyskać dostęp i modyfikować pliki i foldery za pomocą FileSystemObject w VBA.
Poniżej znajduje się tabela przedstawiająca najważniejsze obiekty, do których można uzyskać dostęp i modyfikować je za pomocą FSO:
| Obiekt | Opis |
| Napęd | Drive Object pozwala uzyskać informacje o dysku, takie jak to, czy istnieje, czy nie, jego nazwa ścieżki, typ dysku (wymienny lub stały), jego rozmiar itp. |
| Teczka | Obiekt folderu umożliwia tworzenie lub modyfikowanie folderów w systemie. Na przykład możesz tworzyć, usuwać, zmieniać nazwy, kopiować foldery za pomocą tego obiektu. |
| Plik | File Object umożliwia pracę z plikami w systemie. Za pomocą tego obiektu można na przykład tworzyć, otwierać, kopiować, przenosić i usuwać pliki. |
| Strumień tekstowy | Obiekt TextStream umożliwia tworzenie lub odczytywanie plików tekstowych. |
Każdy z powyższych obiektów ma metody, których możesz użyć do pracy z nimi.
Aby podać przykład, jeśli chcesz usunąć folder, użyjesz metody DeleteFolder obiektu Folder. Podobnie, jeśli chcesz skopiować plik, użyjesz metody CopyFile obiektu File.
Nie martw się, jeśli wydaje się to przytłaczające lub trudne do zrozumienia. O wiele lepiej zrozumiesz, kiedy przejdziesz przez przykłady, które omówiłem w tym samouczku.
Tylko w celach informacyjnych omówiłem wszystkie metody FileSystemObject (dla każdego obiektu) na końcu tego samouczka.
Włączanie FileSystemObject w Excel VBA
FileSystemObject nie jest domyślnie dostępny w programie Excel VBA.
Ponieważ mamy do czynienia z plikami i folderami, które są poza aplikacją Excel, musimy najpierw stworzyć odniesienie do biblioteki, która przechowuje te obiekty (dyski, pliki, foldery).
Teraz możesz zacząć używać FileSystemObject w Excel VBA na dwa sposoby:
- Ustawianie odwołania do biblioteki Microsoft Scripting Runtime Library (Scrrun.dll)
- Tworzenie obiektu do odwoływania się do biblioteki z samego kodu
Chociaż obie te metody działają (i pokażę ci, jak to zrobić dalej), polecam użyć pierwszej metody.
Uwaga: Po włączeniu FileSystemObject możesz uzyskać dostęp do wszystkich znajdujących się w nim obiektów. Obejmuje to FileSystemObject, Dysk, pliki, foldery itp. W tym samouczku skupię się głównie na FileSystemObject.Ustawianie odniesienia do biblioteki wykonawczej skryptów firmy Microsoft
Tworząc odwołanie do biblioteki wykonawczej skryptów, zezwalasz programowi Excel VBA na dostęp do wszystkich właściwości i metod plików i folderów. Po wykonaniu tej czynności możesz odwoływać się do obiektu plików/folderów/dysków z poziomu VBA programu Excel (podobnie jak możesz odwoływać się do komórek, arkuszy roboczych lub skoroszytów).
Poniżej znajdują się kroki, aby utworzyć odwołanie do biblioteki Microsoft Scripting Runtime:
- W Edytorze VB kliknij Narzędzia.
- Kliknij Referencje.
- W otwartym oknie dialogowym Referencje przewiń dostępne referencje i zaznacz opcję „Microsoft Scripting Runtime”.
- Kliknij OK.
Powyższe kroki umożliwiłyby teraz odwoływanie się do obiektów FSO z Excel VBA.
Tworzenie instancji FileSystemObject w kodzie
Po ustawieniu odwołania do biblioteki Scripting FileSystemObject, musisz utworzyć instancję obiektu FSO w swoim kodzie.
Po utworzeniu możesz go używać w VBA.
Poniżej znajduje się kod, który ustawi zmienną obiektu MyFSO jako obiekt FileSystemObject:
Sub TworzenieFSO() Dim MyFSO As FileSystemObject Ustaw MyFSO = Nowy FileSystemObject Koniec Sub
W tym kodzie najpierw zadeklarowałem zmienną MyFSO jako obiekt typu FileSystemObject. Jest to możliwe tylko dlatego, że stworzyłem odniesienie do biblioteki Microsoft Scripting Runtime Library. Jeśli odwołanie nie zostanie utworzone, spowoduje to błąd (ponieważ program Excel nie rozpoznałby, co oznacza FileSystemObject).
W drugiej linii dzieją się dwie rzeczy:
- Słowo kluczowe NEW tworzy instancję FileSystemObject. Oznacza to, że teraz mogę używać wszystkich metod FileSystemObject do pracy z plikami i folderami. Jeśli nie utworzysz tej instancji, nie będziesz mieć dostępu do metod FSO.
- Słowo kluczowe SET ustawia obiekt MyFSO na tę nową instancję FileSystemObject. To pozwala mi używać tego obiektu do uzyskiwania dostępu do plików i folderów. Na przykład, jeśli potrzebuję utworzyć folder, mogę użyć metody MyFSO.CreateFolder.
Jeśli chcesz, możesz również połączyć powyższe dwa stwierdzenia w jedno, jak pokazano poniżej:
Sub TworzenieFSO() Dim MyFSO jako nowy obiekt systemu plików Koniec Sub
Dużą zaletą korzystania z tej metody (która polega na ustawieniu odwołania do biblioteki Microsoft Scripting Runtime Library) jest to, że gdy użyjesz obiektów FSO w swoim kodzie, będziesz mógł użyć funkcji IntelliSense, która pokazuje metody i właściwości skojarzone z obiekt (jak pokazano poniżej).
Nie jest to możliwe, gdy tworzysz odwołanie z poziomu kodu (omówione dalej).
Tworzenie obiektu z kodu
Innym sposobem na stworzenie referencji do FSO jest zrobienie tego z kodu. W tej metodzie nie musisz tworzyć żadnych odwołań (jak to zrobiono w poprzedniej metodzie).
Podczas pisania kodu można utworzyć obiekt z poziomu kodu i odwołać się do Scripting.FileSystemObject.
Poniższy kod tworzy obiekt FSO, a następnie czyni go typem FileSystemObject.
Sub FSODemo() Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") End Sub
Chociaż może się to wydawać wygodniejsze, dużym minusem korzystania z tej metody jest to, że nie pokazuje ona IntelliSense podczas pracy z obiektami w FSO. Dla mnie jest to ogromny minus i zawsze polecam korzystanie z poprzedniej metody włączania FSO (czyli przez ustawienie odniesienia do „Microsoft Scripting Runtime”)
Przykłady obiektów systemu plików VBA
Teraz zanurkujmy i spójrzmy na kilka praktycznych przykładów użycia FileSystemObject w Excelu.
Przykład 1: Sprawdź, czy plik lub folder istnieje
Poniższy kod sprawdzi, czy folder o nazwie „Test” istnieje, czy nie (w określonej lokalizacji).
Jeśli folder istnieje, warunek JEŻELI ma wartość Prawda i wyświetla komunikat „Folder istnieje” w oknie komunikatu. A jeśli nie istnieje, wyświetla komunikat „Folder nie istnieje”.
Sub CheckFolderExist() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "Folder istnieje" Else MsgBox "Folder nie istnieje" End If End Pod
Podobnie możesz również sprawdzić, czy plik istnieje, czy nie.
Poniższy kod sprawdza, czy w określonym folderze znajduje się plik o nazwie Test.xlsx, czy nie.
Sub CheckFileExist() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists("C:\Users\sumit\Desktop\Test\Test.xlsx") Then MsgBox "Plik istnieje" Else MsgBox "Plik nie istnieje " Koniec, jeśli koniec Sub
Przykład 2: Utwórz nowy folder w określonej lokalizacji
Poniższy kod utworzy folder o nazwie „Test” na dysku C mojego systemu (będziesz musiał określić ścieżkę w swoim systemie, w której chcesz utworzyć folder).
Sub CreateFolder() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test") End Sub
Chociaż ten kod działa dobrze, wyświetli błąd, jeśli folder już istnieje.
Poniższy kod sprawdza, czy folder już istnieje i tworzy folder, jeśli nie. Jeśli folder już istnieje, wyświetla komunikat. Aby sprawdzić, czy folder istnieje, użyłem Metoda FolderExists FSO.
Sub CreateFolder() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "Folder już istnieje" Else MyFSO.CreateFolder ("C:\Users\ sumit\Desktop\Test") End If End Sub
Przykład 3: Uzyskaj listę wszystkich plików w folderze
Poniższy kod pokaże nazwy wszystkich plików w określonym folderze.
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") dla każdego MyFile In MyFolder.Files Debug.Print MyFile.Name Next MyFile End Sub
Ten kod jest trochę bardziej złożony niż te, które już widzieliśmy.
Jak wspomniałem powyżej w tym samouczku, kiedy odwołujesz się do „Biblioteki wykonawczej skryptów Microsoft”, możesz użyć FileSystemObject, a także wszystkich innych obiektów (takich jak pliki i foldery).
W powyższym kodzie używam trzech obiektów - FileSystemObject, File i Folder. To pozwala mi przejść przez każdy plik w określonym folderze. Następnie używam właściwości name, aby uzyskać listę wszystkich nazw plików.
Zauważ, że używam Debug.Print, aby uzyskać nazwy wszystkich plików. Nazwy te zostaną wyświetlone w bezpośrednim oknie Edytora VB.
Przykład 4: Pobierz listę wszystkich podfolderów w folderze
Poniższy kod poda nazwy wszystkich podfolderów w określonym folderze. Logika jest dokładnie taka sama, jak w powyższym przykładzie. Zamiast plików w tym kodzie użyliśmy podfolderów.
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") Dla każdego MySubFold W MyFolder.SubFolders Debug.Print MySubFolder.Name Dalej MySubFolder End Sub
Przykład 5: Skopiuj plik z jednego miejsca do drugiego
Poniższy kod skopiuje plik z folderu „Źródło” i skopiuje go do folderu „Destination”.
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 Source:=SourceFile, Destination:=DestinationFolder & "\SampleFileCopy.xlsx" End Sub
W powyższym kodzie użyłem dwóch zmiennych - SourceFile i DestinationFolder.
Plik źródłowy zawiera adres pliku, który chcę skopiować, a zmienna DestinationFolder zawiera adres folderu, do którego plik ma zostać skopiowany.
Pamiętaj, że podczas kopiowania pliku nie wystarczy podać nazwę folderu docelowego. Musisz także podać nazwę pliku. Możesz użyć tej samej nazwy pliku lub możesz ją również zmienić. W powyższym przykładzie skopiowałem plik i nazwałem go SampleFileCopy.xlsx
Przykład 6: Skopiuj wszystkie pliki z jednego folderu do drugiego
Poniższy kod skopiuje wszystkie pliki z folderu źródłowego do folderu docelowego.
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) dla każdego MyFile In MyFolder.Files MyFSO.CopyFile Source:=MyFSO.GetFile(MyFile), _ Destination:=Destination " & MyFile.Name, Overwritefiles:=False Next MyFile End Sub
Powyższy kod skopiuje wszystkie pliki z folderu źródłowego do folderu docelowego.
Zauważ, że w metodzie MyFSO.CopyFile określiłem właściwość „Overwritefiles” na False (domyślnie jest to True). Dzięki temu masz pewność, że jeśli masz już plik w folderze, nie zostanie on skopiowany (i zobaczysz błąd). Jeśli usuniesz „Overwritefiles” lub ustawisz to na True, jeśli w folderze docelowym znajdują się pliki o tej samej nazwie, zostaną one nadpisane.
Wskazówka dla profesjonalistów: Podczas kopiowania plików zawsze istnieje możliwość nadpisania plików. W takim przypadku dobrym pomysłem jest dodanie sygnatury czasowej wraz z nazwą. Zapewni to, że nazwy będą zawsze różne i będziesz mógł łatwo śledzić, które pliki zostały skopiowane w jakim czasie.Jeśli chcesz skopiować tylko pliki o określonym rozszerzeniu, możesz to zrobić za pomocą instrukcji IF Then, aby sprawdzić, czy rozszerzenie to xlsx, czy 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) dla każdego MyFile In MyFolder.Files If MyFSO.GetExtensionName(MyFile) = "xlsx" Następnie MyFSO.MyFSOFile Source:=etFile (MyFile), _ Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=False End If Next MyFile End Sub
Metody FileSystemObject (FSO)
Oto metody, których możesz użyć dla każdego obiektu. To jest tylko w celach informacyjnych i nie przejmuje się tym zbytnio. Wykorzystanie niektórych z nich zostało pokazane w przykładach omówionych powyżej.
| Metody FSO | Dla obiektu | Opis |
| DyskIstnieje | Napęd | Sprawdza, czy napęd istnieje, czy nie |
| GetDrive | Napęd | Zwraca instancję obiektu dysku na podstawie określonej ścieżki |
| GetDriveName | Napęd | Ponownie uruchamia nazwę dysku |
| ŚcieżkaBudowania | Folder plików | Wygeneruj ścieżkę z istniejącej ścieżki i nazwy |
| Skopiuj plik | Folder plików | Kopiuje plik |
| GetAbsolutePathName | Folder plików | Zwróć kanoniczną reprezentację ścieżki |
| GetBaseName | Folder plików | Zwróć nazwę podstawową ze ścieżki. Na przykład „D:\TestFolder\TestFile.xlsm” zwróci TextFile.xlsm |
| GetTempName | Folder plików | Wygeneruj nazwę, która może być użyta do nazwania pliku tymczasowego |
| Kopiuj folder | Teczka | Kopiuje folder z jednej lokalizacji do drugiej |
| Utwórz folder | Teczka | Tworzy nowy folder |
| Usunięty folder | Teczka | Usuwa określony folder |
| Folder istnieje | Teczka | Sprawdza, czy folder istnieje, czy nie |
| Pobierz folder | Teczka | Zwraca instancję obiektu folderu na podstawie określonej ścieżki |
| Pobierz nazwę folderu nadrzędnego | Teczka | Ponawia nazwę folderu nadrzędnego na podstawie określonej ścieżki |
| Pobierz folder specjalny | Teczka | Uzyskaj lokalizację różnych folderów systemowych. |
| Przenieśfolder | Teczka | Przenosi folder z jednej lokalizacji do drugiej |
| Usunąć plik | Plik | Usuwa plik |
| Plik istnieje | Plik | Sprawdza, czy plik istnieje, czy nie |
| Pobierz nazwę rozszerzenia | Plik | Zwraca rozszerzenie pliku |
| Dostać plik | Plik | Zwraca instancję obiektu pliku na podstawie określonej ścieżki |
| PobierzNazwęPliku | Plik | Zwraca nazwę pliku |
| Pobierz wersję pliku | Plik | Zwraca wersję pliku |
| Przenieś plik | Plik | Przenosi plik |
| Utwórz plik tekstowy | Plik | Tworzy plik tekstowy |
| Pobierz strumień standardowy | Plik | Pobierz standardowe wejście, wyjście lub strumień błędów |
| Otwórz plik tekstowy | Plik | Otwórz plik jako TextStream |
