Operacje na plikach W Excelu

W Excelu można wykonywać operacje na plikach na dwa sposoby:

• Za pomocą tradycyjnych instrukcji i funkcji języka VBA (ta metoda działa we wszystkich wersjach Excela).

• Za pomocą obiektu FlleSystemObject wykorzystującego bibliotekę Microsoft Scripling Library (ta metoda działa w Excelu 2000 i późniejszych wersjach).

Zastosowanie poleceń języka VBA do wykonywania operacji na plikach

Zestawienie poleceń VBA, które można wykorzystać do wykonywania operacji na plikach, zostało zamieszczone w tabeli 1. Większość poleceń nie wymaga specjalnego komen­tarza, a wszystkie są opisane w systemie pomocy Excela.

Tabela 1. Polecenia operacji na plikach w języku VBA

Nazwa polecenia	Opis działania
ChDir	Zmienia bieżący katalog
ChDrive	Zmienia bieżący napęd
Dir	Zwraca nazwę pliku lub katalog pasujący do określonego wzorca lub atrybutu pliku
FileCopy	Kopiuje plik
FileDateTime	Zwraca datę i godzinę ostatniej modyfikacji pliku
FileLen	Zwraca rozmiar pliku w bajtach
GetAttr	Zwraca wartość reprezentującą atrybut pliku
Kill	Usuwa plik
MkDir	Tworzy nowy katalog
Name	Zmienia nazwę pliku lub katalogu
RmDir	Usuwa pusty katalog
SetAttr	Zmienia atrybut pliku

W dalszej części tego rozdziału znajdziesz szereg przykładów ilustrujących zastosowanie niektórych poleceń.

Zastosowanie obiektu FileSystemObject do wykonywania operacji na plikach

Obiekt FileSystemObject należy do biblioteki Windows Scripting Host i zapewnia dostęp do systemu plików komputera. Obiekt ten często jest wykorzystywany na stronach WWW zawierających skrypty (np. VBScript lub JavaScripi). Można z niego korzystać w Excelu 2000 i późniejszych wersjach.

Mechanizm Windows Scripting Hostjest czasami wykorzystywany do rozpowszechniania wirusów komputerowych, dlatego funkcja ta w wielu systemach jest wyłączona. Projektując aplikacje, które będą wykorzystywane na wielu komputerach, należy o tym pamiętać i zachować szczególną ostrożność.

Nazwa FileSystemObject może być nieco myląca, ponieważ obiekt ten w rzeczywistości składa się z szeregu innych obiektów, z których każdy posiada swoje osobne, ściśle okre­ślone przeznaczenie:

- Drive - reprezentuje napęd dyskowy lub całą kolekcję napędów dyskowych.

- File - reprezentuje plik lub kolekcję plików.

- Folder - reprezentuje folder lub kolekcję folderów.

- TextStream - reprezentuje strumień tekstu odczytywany, zapisywany lub dołączany do pliku tekstowego.

Aby skorzystać z obiektu FileSystemObject, powinieneś najpierw utworzyć instancję tego obiektu. Możesz tego dokonać na dwa sposoby: za pomocą tzw. metody wczesnego wią­zania (ang. early binding) lub metody późnego wiązania (ang. łatę binding).

Metoda późnego wiązania wykorzystuje sekwencję dwóch poleceń, na przykład:

Dim FileSys As Object

Set FileSys = CreateObject("Scripting.FileSystemObject")

Zwróć uwagę, że zmienna obiektowa FileSys została zadeklarowana jako ogólny typ Object, a nie jako konkretny typ obiektowy - rodzaj obiektu zostanie ustalony podczas działania programu.

Metoda wczesnego wiązania wymaga utworzenia odwołania do modelu obiektowego Windows Scripting Host. Aby to zrobić, powinieneś w edytorze VBE wybrać z menu głównego polecenie Tools/References i następnie w oknie dialogowym References zazna­czyć odpowiednią opcję (patrz rysunek 1). Po utworzeniu odwołania możesz utworzyć obiekt za pomocą następującej sekwencji poleceń:

Dim FileSys As Object

Set FileSys = CreateObject("Scripting.FileSystemObject")

Rysunek 1. Tworzenie odwołania do modelu obiektowego Windows Script Host

Zastosowanie metody wczesnego wiązania pozwala na skorzystanie z mechanizmu Auto List Members edytora VBE, który znakomicie ułatwia wpisywanie oraz identyfikację odpowiednich właściwości i metod obiektów. Co więcej, dzięki temu możesz również skorzystać z przeglądarki obiektów (ang. Object Browser) i sprawdzić informacje na temat danego obiektu. Aby to zrobić, wystarczy po wejściu do VBE nacisnąć klawisz F2.

W kolejnych przykładach poniżej przedstawimy zastosowanie obiektu FileSystenObject do wielu różnych zadań.

Wybieranie nazwę pliku i katalogu

Aplikacja musi wiedzić nazwę pliku i katalogu.

Funkcja lnputBox języka VBA (przycisk 2)

Składnia funkcji InputBox języka VBA jest następująca:

InputBox(komunikat[, tytuł, wart_domyślna][, _

wsp_x][, wsp_y ][, plik_pomocy, kontekst])

• komunikat (wymagany) - tekst wyświetlany w oknie wprowadzania danych;

• tytuł (opcjonalny) - tytuł okna wprowadzania danych;

• wart_domyślnd (opcjonalna) - domyślna wartość wyświetlana w oknie dialogowym;

• wsp_x, wsp_y (opcjonalne) - współrzędne pozycji górnego lewego narożnika okna;

• plik_pomocy, kontekst (opcjonalne) - plik i temat pomocy.

Funkcja InputBox prosi użytkownika o wprowadzenie pojedynczego wiersza informacji. Zawsze zwraca łańcuch, dlatego może być wykonanie konwersji wyniki na wartość liczbową.

Komunikat może liczyć około 1024 znaków (mniej więcej, w zależności od ich szero­kości). Dodatkowo można zdefiniować tytuł okna dialogowego i wartość domyślną oraz określić położenie okna na ekranie. Poza tym można określić niestandardowy temat pomocy. W tym przypadku w oknie wprowadzania danych pojawi się przycisk Pomoc.

W poniższej przykładowej procedurze, której efekt działania pokazano na rysunku 2, zastosowano funkcję InputBox języka VBA, która prosi użytkownika o wprowadzenie imienia i nazwiska. Później procedura pobiera imię i przy użyciu okna komunikatu wyświe­tla nowitanie.

Rysunek 2. Efekt wywołania funkcji InputBox języka VBA

Sub GetName()

Dim UserName As String

Dim FirstSpace As Integer

Do Until UserName <> ""

UserName = InputBox("Podaj imię: ", _

"Przedstaw się!")

Loop

FirstSpace = InStr(UserName, " ")

If FirstSpace <> 0 Then

UserName = Left(UserName, FirstSpace - 1)

End If

MsgBox "Witaj, " & UserName

End Sub

Aby zagwarantować, że po pojawieniu się okna zostaną wprowadzone dane, funkcja InputBox została umieszczona wewnątrz pętli Do Until. Jeżeli użytkownik naciśnie przy­cisk Cancel lub nie wprowadzi żadnego tekstu, zmiennej UserName zostanie przypisany pusty łańcuch, po czym okno wprowadzania danych pojawi się ponownie. Po wprowa­dzeniu danych procedura szuka pierwszej spacji (przy użyciu funkcji InStr), aby pobrać imię, a następnie przy wykorzystaniu funkcji Left pobiera wszystkie znaki przed spacją. Jeżeli nie znajdzie spacji, pobierze cały wprowadzony łańcuch.

Jak już wspomniałem, funkcja InputBox zawsze zwraca łańcuch. Jeżeli zwrócony łańcuch wygląda na liczbę, można go na nią zamienić za pomocą funkcji Val języka VBA. Można też zastosować metodę InputBox Excela, która zostanie omówiona w kolejnym podrozdziale.

Na rysunku 3 przedstawiono kolejny przykład zastosowania funkcji InputBox języka VBA. Użytkownik jest proszony o wprowadzenie brakującego słowa. Przykład ten ilustruje również sposób użycia argumentów posiadających nazwy. Tekst zapowiedzi jest pobie­rany z komórki arkusza i przypisany do zmiennej p.

Rysunek 3. Użycie funkcji lnputBox języka VBA do wyświetlenia długiej zapowiedzi

Sub GetWord()

Dim TheWord As String

Dim p As String

Dim t As String

p = "Jest zasadą wojskowych, aby: nie atakować wroga, kiedy ten jest na wzniesieniu, lub kiedy może się na nie wycofać; nie gonić armii, która udaje, że ucieka; nie uderzać, kiedy wróg tryska zapałem; nie dać się zwabić na przynętę; nie podążać za armią wycofującą się na własny _______________; otaczając wroga, nie należy tego czynić szczelnie - wroga na straconej pozycji nie należy zbytnio przypierać do muru, bo wtedy może wpaść w szał i bić się bez opamiętania."

t = "Podaj brakujące słowo!"

TheWord = InputBox(prompt:=p, Title:=t)

If UCase(TheWord) = "TEREN" Then

MsgBox "Zgadza się!"

Else

MsgBox "Niestety nie! Spróbuj ponownie."

End If

End Sub

Działanie funkcji InputBox możliwo emulować formularzem UserForm (przycisk 4).

Metoda lnputBox Excela (przycisk 3)

Użycie metody InputBox Excela zamiast funkcji InputBox języka VBA przynosi nastę­pujące korzyści:

• pojawia się możliwość określenia typu danych zwracanej wartości;

• użytkownik może zdefiniować zakres poprzez przeciągnięcie myszą w obrębie arkusza;

• sprawdzanie poprawności wprowadzonych danych jest wykonywane automatycznie.

Składnia metody InputBox Excela jest następująca:

obiekt.InputBox (komunikat, tytuł, wart_domyślna, _

wsp_x, wsp_y, plik_pomocy, kontekst, typ)

• komunikat (wymagany) - tekst wyświetlany w oknie wprowadzania danych;

• tytuł (opcjonalny) - tytuł okna wprowadzania danych;

• wdrt_domyślna (opcjonalna) - domyślna wartość zwracana przez funkcję, gdy użytkownik nie wprowadzi danych;

• wsp_x, wsp_y (opcjonalne) - współrzędne pozycji górnego lewego narożnika okna;

• plik_pomocy, kontekst (opcjonalne) - plik i temat pomocy;

• typ (opcjonalny) - kod identyfikujący typ danych zwracanej wartości (tabela 2).

Tabela 2. Kod identyfikujący typ danych zwracanych przez metodę lnputBox Excela

Kod	Znaczenie
0	Formuła
l	Liczba
2	Łańcuch tekstowy
4	Wartość logiczna (True lub False)
8	Odwołanie do komórki będące obiektem Range
16	Wartość błędu, na przykład #N/D!
64	Tablica wartości

Metoda InputBox Excela jest dość wszechstronna. Aby funkcja zwracała wartość więcej niż jednego typu danych, należy użyć sumy odpowiednich kodów. Przykładowo: aby wyświe­tlić okno umożliwiające wprowadzanie tekstu i liczb, jako wartość typ należy podać 3 (1+2 lub liczba+łańcuch tekstowy). Jeżeli wartością argumentu typ będzie 8, użytkownik może ręcznie wprowadzić adres komórki lub zakresu albo wskazać go w arkuszu.

Procedura EraseRange, której kod zamieszczamy poniżej, używa metody InputBox do umożliwienia użytkownikowi wybrania zakresu, który zostanie wyczyszczony (patrz rysunek 12.3). Użytkownik może ręcznie wprowadzić adres zakresu lub zaznaczyć go w arkuszu za pomocą myszy.

Metoda InputBox z argumentem Type o wartości 8 zwraca obiekt Rangę (zwróć uwagę na słowo kluczowe Set). Zakres jest następnie czyszczony przy użyciu metody Clear. Domyślna wartość wyświetlana w oknie wprowadzania danych jest adresem aktualnego zaznaczenia. Jeżeli w oknie zostanie kliknięty przycisk Anuluj, instrukcja On Error zakoń­czy wykonywanie procedury.

Metoda InputBox Excela jest dość wszechstronna. Aby funkcja zwracała wartość więcej niż jednego typu danych, należy użyć sumy odpowiednich kodów. Przykładowo: aby wyświe­tlić okno umożliwiające wprowadzanie tekstu i liczb, jako wartość typ należy podać 3 (1+2 lub liczba+łańcuch tekstowy). Jeżeli wartością argumentu typ będzie 8, użytkownik może ręcznie wprowadzić adres komórki lub zakresu albo wskazać go w arkuszu.

Procedura EraseRange, której kod zamieszczamy poniżej, używa metody InputBox do umożliwienia użytkownikowi wybrania zakresu, który zostanie wyczyszczony (patrz rysunek 4). Użytkownik może ręcznie wprowadzić adres zakresu lub zaznaczyć go w arkuszu za pomocą myszy.

Sub EraseRange()

Dim UserRange As Range

On Error GoTo Canceled

Set UserRange = Application.InputBox _

(Prompt:="Podaj zakres, który zostanie wyczyszczony:", _

Title:="Czyszczenie zakresu", _

Default:=Selection.Address, _

Type:=8)

UserRange.Clear

UserRange.Select

Canceled:

End Sub

Rysunek 4. Użycie okna wprowadzania danych do zdefiniowania zakresu

Metoda InputBox z argumentem Type o wartości 8 zwraca obiekt Range (zwróć uwagę na słowo kluczowe Set). Zakres jest następnie czyszczony przy użyciu metody Clear. Domyślna wartość wyświetlana w oknie wprowadzania danych jest adresem aktualnego zaznaczenia. Jeżeli w oknie zostanie kliknięty przycisk Anuluj, instrukcja On Error zakoń­czy wykonywanie procedury.

Kolejną korzyścią wynikającą z zastosowania metody InputBox Excela jest automatyczne sprawdzenie wprowadzonych danych. Jeżeli w przykładowej procedurze EraseRange zostanie wprowadzone coś innego niż adres zakresu, Excel wyświetli komunikat i umoż­liwi użytkownikowi ponowne wykonanie operacji (patrz rysunek 5).

Rysunek 5. Metoda InputBox Excela automatycznie sprawdza poprawność wprowadzonych danych

Metoda GetOpenFilename programu Excel. Okno wybierania pliku (przycisk 5)

Jeżeli aplikacja musi poprosić użytkownika o podanie nazwy pliku, można w tym celu użyć funkcji InputBox. Niestety rozwiązanie takie jest niezbyt eleganckie i podatne na błędy.

Lepszą propozycją jest zastosowanie metody GetOpenFilename obiektu Application, która gwarantuje, że aplikacja pobierze prawidłową nazwę pliku, a także jego pełną ścieżkę.

Metoda wyświetla standardowe okno dialogowe Otwieranie, ale nie otwiera pliku, którego nazwę wybierze użytkownik. Zamiast tego zwraca łańcuch zawierający ścieżkę i nazwę wybranego pliku. Teraz możesz z łatwością napisać procedurę, która będzie w dowolny sposób przetwarzała nazwę pliku.

Składnia metody GetOpenFilename jest następująca (wszystkie argumenty są opcjonalne):

Application.GetOpenFilename(FileFilter, Filterlndex, Title, ButtonText, MultiSelect)

• FileFilter - łańcuch określający kryterium filtrowania plików;

• FilterIndex - numery indeksów domyślnego kryterium filtrowania plików;

• Title - tytuł okna dialogowego (jeżeli zostanie pominięty, tytułem będzie łańcuch Otwieranie)',

• ButtonText - argument stosowany tylko w przypadku systemu Macintosh;

• MultiSelect - jeżeli argument ma wartość True, wybranych może być wiele plików; wartością domyślną jest False.

Argument FileFilter określa, co pojawi się na liście rozwijanej Pliki typu okna dialo­gowego. Argument składa się z łańcuchów identyfikujących filtr plików, za którymi znajdują się symbole filtrujących znaków wieloznacznych, w przypadku których każda część i każda para jest oddzielona przecinkami. Jeżeli wartość argumentu nie zostanie podana, będzie użyta wartość domyślna o następującej postaci:

"Wszystkie pliki (*.*),*.*"

Pierwsza część powyższego łańcucha (Wszystkie pliki (*.*)) jest tekstem wyświetla­nym na liście rozwijanej Pliki typu. Z kolei jego druga część (*.*) określa, jakie pliki zostaną wyświetlone w oknie.

Poniższa instrukcja przypisuje łańcuch tekstu do zmiennej o nazwie Filt. Ten łańcuch tekstu może następnie zostać użyty jako argument FileFilter metody GetOpenFilename. W tym przypadku okno dialogowe umożliwi użytkownikowi wybranie jednego z czterech różnych typów plików (dodatkowo dostępna jest pozycja Wszystkie pliki). Przy definio­waniu zmiennej Filt posłużyłem się znakami kontynuacji polecenia VBA w następnym wierszu. Dzięki temu łatwiejsze będzie modyfikowanie tego raczej złożonego argumentu.

Filt = _

"Pliki tekstowe (*.txt),*.txt," & _

"Pliki arkusza kalkulacyjnego firmy Lotus (*.prn),*.prn," & _

"Pliki używające przecinka jako separatora (*.csv),*.csv," & _

"Pliki ASCII (*.asc),*.asc," & _

"Wszystkie pliki (*.*),*.*"

Argument FilterIndex określa, który filtr plików będzie domyślny (przypisany zmiennej FileFilter), natomiast argument Title zawiera tekst wyświetlany na pasku tytułu. Jeżeli wartością argumentu MultiSelect jest True, użytkownik może wybrać wiele plików, które zostaną zwrócone w tablicy.

W poniższym przykładzie użytkownik jest proszony o podanie nazwy pliku. W procedurze zdefiniowano pięć filtrów plików.

Sub GetImportFileName()

Dim Filt As String

Dim FilterIndex As Integer

Dim Title As String

Dim FileName As Variant

' Tworzenie listy filtrów plików

Filt ="Pliki tekstowe (*.txt),*.txt," & _

"Pliki arkusza kalkulacyjnego firmy Lotus (*.prn),*.prn," & _

"Pliki używające przecinka jako separatora (*.csv),*.csv," & _

"Pliki ASCII (*.asc),*.asc," & _

"Wszystkie pliki (*.*),*.*"

' Domyślnie wyświetlany jest filtr *.*

FilterIndex = 5

' Tworzenie tytułu okna dialogowego

Title = "Wybierz plik, który chcesz importować"

' Pobranie nazwy pliku

FileName = Application.GetOpenFilename( _

FileFilter:=Filt, _

FilterIndex:=FilterIndex, _

Title:=Title)

' Zakończenie pracy, jeżeli w oknie dialogowym zostanie

' naciśnięty przycisk Anuluj

If FileName = False Then

MsgBox "Nie wybrano żadnego pliku."

Exit Sub

End If

' Wyświetlenie pełnej ścieżki i nazwy wskazanego pliku

'MsgBox "Wybrałeś plik: " & FileName

Range("A15") = FileName

End Sub

Na rysunku 6 pokazano okno dialogowe pojawiające się po wywołaniu procedury, kiedy użytkownik jako filtr wybierze pliki formatu *.txt.

Rysunek 6. Metoda GetOpenFilename wyświetla okno dialogowe dostosowane do własnych wymagań użytkownika

Skoroszyt z tym przykładem - OperacjeNaPlikach.xlsm.

Kolejny przykład jest podobny do poprzedniego. Różnica polega na tym, że użytkownik może wcisnąć klawisz Ctrl lub Shift i po otwarciu okna dialogowego wybrać wiele plików. Poprzez określenie, czy zmienna FileName jest tablicą, sprawdzamy, czy został naciśnięty przycisk Anuluj. Jeżeli użytkownik nie naciśnie przycisku Anuluj, wynikiem będzie tablica z przynajmniej jednym elementem. W naszym przykładzie lista wybranych plików jest wyświetlana w oknie komunikatu.

Sub GetImportFileName2()

Dim Filt As String

Dim FilterIndex As Integer

Dim FileName As Variant

Dim Title As String

Dim i As Integer

Dim Msg As String

' Tworzenie listy filtrów plików

Filt ="Pliki tekstowe (*.txt),*.txt," & _

"Pliki arkusza kalkulacyjnego firmy Lotus (*.prn),*.prn," & _

"Pliki używające przecinka jako separatora (*.csv),*.csv," & _

"Pliki ASCII (*.asc),*.asc," & _

"Wszystkie pliki (*.*),*.*"

' Domyślnie wyświetlany jest filtr *.*

FilterIndex = 5

' Tworzenie tytułu okna dialogowego

Title = "Wybierz plik do zaimportowania"

' Pobranie nazwy pliku

FileName = Application.GetOpenFilename _

(FileFilter:=Filt, _

FilterIndex:=FilterIndex, _

Title:=Title, _

MultiSelect:=True)

' Zakończenie pracy, jeżeli w oknie

' dialogowym zostanie naciśnięty przycisk Anuluj

If Not IsArray(FileName) Then

MsgBox "Nie wybrano żadnego pliku."

Exit Sub

End If

' Wyświetlenie pełnych ścieżek i nazw plików

For i = LBound(FileName) To UBound(FileName)

Msg = Msg & FileName(i) & vbCrLf

Next i

MsgBox "Wybrałeś następujące pliki:" & vbCrLf & Msg

End Sub

Zmienna FileName została zdefiniowana przy użyciu typu Variant, a nie String, jak w poprzednich przykładach. Wynika to stąd, że zmienna FileName zamiast pojedynczej nazwy pliku może przechowywać tablicę nazw.

Metoda GetSaveAsFilename programu Excel. Okno wybierania pliku (przycisk 6)

Metoda GetSaveAsFilename jest bardzo podobna do metody GetOpenFilename. Wyświe­tla okno dialogowe Zapisywanie jako i umożliwia użytkownikowi wybranie pliku lub podanie jego nazwy. Funkcja zwraca nazwę pliku i jego ścieżkę, ale nie podejmuje żad­nych działań. Podobnie jak w przypadku metody GetOpenFilename wszystkie argumenty metody GetSaveAsFilename są opcjonalne.

Składnia metody GetSaveAsFilename jest następująca:

Application.GetSaveAsFilename(InitialFilename, FIleFilter, _

Filterlndex, Title, ButtonText)

Oto jej argumenty:

• InitialFilename (opcjonalny) - pozwala określić sugerowaną nazwę pliku (wraz ze ścieżką do pliku);

• FileFilter (opcjonalny) - łańcuch określający kryterium filtrowania plików;

• FilterIndex (opcjonalny) - numery indeksu domyślnego kryterium filtrowania plików;

• Tltle (opcjonalny) - tytuł okna dialogowego;

• ButtonText - argument używany tylko dla systemu Macintosh.

Skoroszyt z przykładem - OperacjeNaPlikach.xlsm.

Metoda FileDialog programu Excel. Okno wybierania katalogu (przycisk 12)

Jeżeli chcesz pobrać nazwę pliku, to najprostszym rozwiązaniem jest zastosowanie metody GetOpenFilename, co już zostało omówione w poprzednim podrozdziale. Jeżeli jednak musisz pobrać tylko nazwę wybranego katalogu (a nie nazwę pliku), możesz użyć funkcji FileDialog programu Excel.

Procedura przedstawiona poniżej wyświetla okno dialogowe (patrz rysunek 7), które pozwala użytkownikowi na wybranie nazwy katalogu. Po naciśnięciu przycisku OK pro­cedura wyświetla przy użyciu funkcji MsgBox pełną ścieżkę wybranego katalogu. Jeżeli użytkownik naciśnie przycisk Anuluj, w oknie komunikatu pojawi się łańcuch Anulowano.

Rysunek 7. Zastosowanie obiektu FileDialog do wybrania katalogu

Sub GetAFolder()

With Application.FileDialog(msoFileDialogFolderPicker)

.InitialFilename = Application.DefaultFilePath & "\"

.Title = "Wybierz lokalizację kopii zapasowej:"

.Show

If .SelectedItems.Count = 0 Then

MsgBox "Operacja została anulowana"

Else

' MsgBox .SelectedItems(1)

Range("A34") = .SelectedItems(1)

End If

End With

End Sub

Obiekt FileDlalog umożliwia określenie katalogu startowego poprzez ustawienie odpo­wiedniej wartości właściwości InitialFilename. W tym przypadku kod procedury jako katalogu startowego używa domyślnej ścieżki programu Excel.

Skoroszyt z tym przykładem - OperacjeNaPlikach.xlsm.

Okno wyboru folderu. Zastosowanie Win 32-bit API (przycisk 13)

Istnieje czasem potrzeba dialogu z użytkownikiem w postaci pytania o folder.

Poniższy kod zawiera deklarację odpowiedniej funkcji API (32-bit) oraz funkcji użytkownika zwracającej w postaci ciągu znaków wskazaną przez nas ścieżkę (przykład pochodzi z samples.xls):

 

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type


Function PobierzFolder(Optional Msg) As String
 

Dim bInfo As BROWSEINFO
Dim ścieżka As String
Dim r As Long, x As Long, pos As Integer

' Folder główny - Pulpit
    bInfo.pidlRoot = 0&

' Tytuł okna dialogowego
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Wybierz folder"
    Else
        bInfo.lpszTitle = Msg
    End If

' Typ zwracanego foldera
    bInfo.ulFlags = &H1

' Wyświatlanie okna dialogowego
    x = SHBrowseForFolder(bInfo)

' Analiza zwróconej wartości
    ścieżka = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal ścieżka)
    If r Then
        pos = InStr(ścieżka, Chr$(0))
        PobierzFolder = Left(ścieżka, pos - 1)
    Else
        PobierzFolder = ""
    End If
End Function

Skoroszyt z tym przykładem - OperacjeNaPlikach.xlsm.

Funkcja VBA sprawdzająca, czy istnieje dany plik (FileExists)

Poniższa funkcja zwraca wartość True, jeżeli określony plik istnieje, lub wartość False, jeżeli plik nie zostanie odnaleziony. Jeżeli funkcja Dir zwraca pusty ciąg znaków, oznacza to, że nie można odnaleźć żądanego pliku i funkcja FileExists zwraca wartość False.

Function FileExists(fname) As Boolean

FileExists = Dir(fname) <> ""

End Function

Skoroszyt z tym przykładem - OperacjeNaPlikach.xlsm.

Argumentem funkcji FileExists jest pełna ścieżka dostępu do pliku wraz z jego nazwą. Funkcję można wykorzystać w arkuszu lub wywołać z poziomu procedury VBA. A oto przykład wywołania takiej funkcji:

Sub A1()

MyFile = "c:\Budzet\2011-propozycja budżetu.docx"

MsgBox MyFile & " " & FileExists(MyFile)

MyPath = ActiveWorkbook.Path

MsgBox MyPath & " " & FileExists(MyPath)

MyFile1 = ActiveWorkbook.FullName

MsgBox MyFile1 & " " & FileExists(MyFile1)

MyFile2 = ActiveWorkbook.Path & "\" & "Zeszyt2.xlsm"

MsgBox MyFile2 & " " & FileExists(MyFile2)

End Sub

Funkcja która zwraca nazwę pliku (FileNameOnly) (FileNameOnly2)

Funkcja pobiera jeden argument (ścieżka pliku wraz z jego nazwą) i zwraca tylko nazwę pliku (innymi słowy - usuwa ścieżkę pliku):

Function FileNameOnly(pname) As String

'Zwraca nazwę pobraną z lańcucha

'złożonego ze ściezki i nazwy pliku

Dim temp As Variant

Length = Len(pname)

temp = Split(pname, Application.PathSeparator)

FileNameOnly = temp(UBound(temp))

End Function

Funkcja FileNameOnly wykorzystuje funkcję Split VBA, która pobiera łańcuch tekstu (oraz separator) i zwraca tabelę typu Variant, zawierającą elementy łańcucha znajdujące się pomiędzy znakami separatora.

W tym przypadku zmienna temp zawiera tablicę z łańcu­chami tekstu znajdującymi się pomiędzy separatorami definiowanymi przez Application.PathSeparator (zaznaczaj są to znaki lewego ukośnika).

Jeżeli argumentem wywołania funkcji jest ścieżka c:\excelfiles\2010\backup\budget.xlsm, funkcja zwróci łańcuch budget.xlsm.

Funkcja FileNameOnly przetwarza dowolną ścieżkę i nazwę pliku (nawet jeżeli plik nie istnieje). Jeżeli plik istnieje, poniższa funkcja oferuje prostszą metodę usuwania ścieżki i zwracania tylko nazwy pliku:

Private Function FileNameOnly2(pname) As String

FileNameOnly2 = Dir(pname)

End Function

Skoroszyt z tymi przykładami - OperacjeNaPlikach.xlsm.

Funkcja VBA sprawdzająca, czy istnieje dany katalog (PathExists)

Poniższa funkcja zwraca wartość True, jeżeli określony katalog istnieje, lub wartość False, jeżeli katalog nie zostanie odnaleziony:

Function PathExists(pname) As Boolean

'Zwraca wartość True, jeżeli katalog istnieje

On Error Resume Next

PathExists = (GetAttr(pname) And vbDirectory) = vbDirectory

End Function

Argument pname ma postać łańcucha tekstu, który zawiera ścieżkę katalogu (bez nazwy pliku). Znak ukośnika zamykający ścieżkę jest opcjonalny. Poniżej przedstawiamy przy­kład wywołania takiej funkcji.

MyFolder = "c:\uzytkownicy\jan\pulpit\pobieranie\"

MsgBox PathExists(MyFolder)

Skoroszyt z tym przykładem - OperacjeNaPlikach.xlsm.

Zastosowanie obiektu FileSystemObject do sprawdzenia, czy dany plik istnieje (FileExists3)

Poniższa funkcja pobiera jeden argument (ścieżkę wraz z nazwą pliku) i jeżeli plik istnieje, zwraca wartość True:

Function FileExists3(fname) As Boolean

Dim FileSys As Object ' FileSystemObject

Set FileSys = CreateObject("Scripting.FileSystemObject”)

FileExists3 = FileSys.FileExists(fname)

End Function

Funkcja tworzy nowy obiekt FileSystemObject o nazwie FileSys, a następnie sprawdza właściwość FileExists tego obiektu.

Skoroszyt z tym przykładem - OperacjeNaPlikach.xlsm.

Zastosowanie obiektu FileSystemObject do sprawdzenia, czy istnieje dany katalog (PathExists2)

Ponizsza funkcja pobiera jeden argument (katalog) i zwraca wartość True, jeżeli ten kalałog istnieje:

Function PathExists2(pname) As Boolean

Dim FileSys As Object ' FileSystemObject

Set FileSys = CreateObject("Scripting.FileSystemObject")

PathExists2 = FileSys.FolderExists(pname)

End Function

Skoroszyt z tym przykładem - OperacjeNaPlikach.xlsm.

Przykłady wykonywania operacji na plikach

Procedura VBA wyświetlająca listę plików w katalogu

Poniższa procedura wyświetla w aktywnym arkuszu listę plików z określonego katalogu wraz z rozmiarem i datą modyfikacji pliku:

Sub ListFiles()

Dim Directory As String

Dim r As Long

Dim f As String

Dim FileSize As Double

'Directory = "E:\2011_Podstawy_VBA_WWW\cw09_OperacjeNaPlikach\"

MyPath = ActiveWorkbook.Path & "\"

Directory = MyPath

r = 1

' Wstaw nagłówki

Cells.ClearContents

Cells(r, 1) = "Nazwa pliku"

Cells(r, 2) = "Rozmiar"

Cells(r, 3) = "Data/godzina"

Range("A1:C1").Font.Bold = True

' Pobierz pierwszy plik

f = Dir(Directory, vbReadOnly + vbHidden + vbSystem)

Do While f <> ""

r = r + 1

Cells(r, 1) = f

' Poprawka na pliki o wielkości ponad 2GB

FileSize = FileLen(Directory & f)

If FileSize < 0 Then FileSize = FileSize + 4294967296#

Cells(r, 2) = FileSize

Cells(r, 3) = FileDateTime(Directory & f)

'Pobierz następny plik

f = Dir()

Loop

End Sub

Przykładowy wynik działania procedury ListFiles pokazano na rysunku 8.

Rysunek 8. Wynik działania procedury ListFiles

Funkcja FileLen języka VBA wykorzystuje dane typu Long, stąd w przypadku plików większych niż 2 GB będzie zwracała niepoprawny rozmiar pliku (liczbę ujemną). Kod procedury sprawdza, czy funkcja FileLen zwróciła wartość ujemną i jeżeli tak, dokonuje odpowiednich poprawek.

Zwróć uwagę, że procedura dwukrotnie wykorzystuje funkcję Dir. Za pierwszym razem (wywołanie z argumentem) funkcja pobiera pierwszą znalezioną nazwę pliku. Kolejne wywołania w pętli (bez argumentu) powodują pobranie nazw kolejnych plików. Jeżeli nie ma więcej plików, funkcja zwraca pusty ciąg znaków.

Pierwszym argumentem funkcji Dir może być nazwa plików podana w postaci wzorca (przy użyciu symboli wieloznacznych). Na przykład: aby uzyskać listę plików programu Excel, możesz użyć polecenia przedstawionego poniżej:

f = Dir(Directory & "*.xl??", vbReadOnly + vbHidden + vbSystem)

Wykonanie tego polecenia spowoduje pobranie z podanego katalogu nazwy pierwszego pliku zgodnego z wzorcem *.xl??. Takie użycie symboli wieloznacznych powoduje, że zwracane są nazwy plików posiadających czteroznakowe rozszerzenie zaczynające się od liter XL. Na przykład, mogą to być pliki o rozszerzeniach .xlsx, .xltx czy .xlam. Drugi argument funkcji Dir umożliwia wprowadzenie atrybutów plików (definiowanych w postaci wbudowanych stałych). W tym przykładzie funkcja Dir pobiera nazwy plików, które mają ustawione następujące atrybuty: bez atrybutów, tylko do odczytu, plik ukryty oraz plik systemowy.

W tabeli 3 zamieszczono zestawienie stałych, które mogą być argumentami funkcji Dir.

Tabela 3. Zestawienie stałych, które mogą być argumentami funkcji Dir

Nazwa stałej	Wartość	Opis
vbNormal	0	Plik bez atrybutów, jest to domyślne ustawienie atrybutów dla tej funkcji
vbReadOnly	l	Pliki tylko do odczytu
vbHidden	2	Pliki ukryte
vbSystem	4	Pliki systemowe
vbVolume	8	Etykieta woluminu. Jeżeli w wywołaniu funkcji został użyty jakikolwiek inny atrybut, ten atrybut będzie ignorowany
vbDirectory	16	Katalogi. Ten atrybut... po prostu nie działa. Wywołanie funkcji Dir z atrybutem vbDirectory rectory nie zwraca nazw podkatalogów

Jeżeli używasz funkcji Dir do przechodzenia w pętli przez kolejne pliki i wywoływania innych procedur przetwarzających pliki, upewnij się, że nie zawierają one polecenia Dir - za każdym razem może być aktywna tylko jedna instancja polecenia Dir.

Skoroszyt z bardziej zaawansowaną wersją tej procedury, umożliwiającą wybranie katalogu za pomocą okna dialogowego - ListaPlików.xlsm.

Rekurencyjna procedura VBA wyświetlająca listę plików w katalogu

Procedura przedstawiona w tym podrozdziale tworzy lisicy plików znajdujących w danym katalogu oraz wszystkich jego podkalalogach. Sama procedura jest nieco nie­typowa, ponieważ zawiera wywołania do samej siebie takie rozwiązanie nazywamy rekurencją.

Public Sub RecursiveDir(ByVal CurrDir As String)

Dim Dirs() As String

Dim NumDirs As Long

Dim FileName As String

Dim PathAndName As String

Dim i As Long

' Upewnij się, że ścieżka kończy się znakiem \

If Right(CurrDir, 1) <> "\" Then CurrDir = CurrDir & "\"

' Wstaw nagłówki kolumn

Cells(1, 1) = "Ścieżka"

Cells(1, 2) = "Nazwa pliku"

Cells(1, 3) = "Rozmiar"

Cells(1, 4) = "Data/czas"

Range("A1:D1").Font.Bold = True

' Pobierz pliki

FileName = Dir(CurrDir & "*.*", vbDirectory)

Do While Len(FileName) <> 0

If Left(FileName, 1) <> "." Then 'Bieżący katalog

PathAndName = CurrDir & FileName

If (GetAttr(PathAndName) And vbDirectory) = vbDirectory Then

'Zapamiętaj odnalezione podkatalogi

ReDim Preserve Dirs(0 To NumDirs) As String

Dirs(NumDirs) = PathAndName

NumDirs = NumDirs + 1

Else

' Zapisz ścieżkę i plik do arkusza

Cells(WorksheetFunction.CountA(Range("A:A")) + 1, 1) = CurrDir

Cells(WorksheetFunction.CountA(Range("B:B")) + 1, 2) = FileName

Cells(WorksheetFunction.CountA(Range("C:C")) + 1, 3) = FileLen(PathAndName)

Cells(WorksheetFunction.CountA(Range("D:D")) + 1, 4) = FileDateTime(PathAndName)

End If

End If

FileName = Dir()

Loop

' Przetwarzaj rekurencyjnie odnalezione katalogi

For i = 0 To NumDirs - 1

RecursiveDir Dirs(i)

Next i

End Sub

Procedura pobiera tylko jeden argument, CurrDir, reprezentujący przetwarzany katalog. Informacja o poszczególnych odnalezionych plikach jest wyświetlana na aktywnym arku­szu. Nazwy podkatalogów odnalezione podczas rekurencyjnego przetwarzania plików są zapamiętywane w tablicy o nazwie Dirs. Kiedy w bieżącym katalogu nie ma już więcej plików do przetwarzania, procedura wywołuje samą siebie, pobierając jako argument wywołania nazwę kolejnego podkatalogu z tablicy Dirs. Procedura kończy działanie po zakończeniu przetwarzania wszystkich podkatalogów zapisanych w tablicy Dirs.

Ponieważ procedura RecursiveDir wymaga podania odpowiedniego argumentu, musi być wywoływana z poziomu innej procedury, na przykład za pomocą następującego polecenia:

Call RecursiveDir("c:\nazwa_katalogu\")

Skoroszyt z tym przykładem - ListaPlikówRekurencja.xlsm.

Wykorzystanie obiektu FileSystemObject do wyświetlenia informacji o wszystkich dostępnych napędach dysków

Procedura przedstawiona poniżej używa obiektu FileSystemObject do pobrania i wyświe­tlenia różnych informacji na temat dostępnych napędów dyskowych. Procedura prze­twarza w pętli kolekcję Drives i zapisuje wartości różnych właściwości do arkusza.

Na rysunku 9 przedstawiono wyniki działania procedury w systemie posiadającym tszy napędy dyskowe: zawierającym dwa twarde dyski oraz napęd CD-ROM. Wyświetlane dane obejmują literę napędu, informację o gotowości urządzenia, typ urządzenia, nazwę wolumenu, całkowity rozmiar oraz ilość dostępnego miejsca.

Rysunek 9. Wynik działania procedury ShowDriveInfo

Skoroszyt z tym przykładem - PokażInformacjęONapedach.xlsm.

Sub ShowDriveInfo()

Dim FileSys As FileSystemObject

Dim Drv As Drive

Dim Row As Long

Set FileSys = CreateObject("Scripting.FileSystemObject")

Cells.ClearContents

Row = 1

' Nagłówki kolumn

Range("A1:F1") = Array("Napęd", "Gotowy", "Typ", _

"Nazwa wolumenu", "Rozmiar", "Dostępność")

On Error Resume Next

' Pętla przetwarzająca kolejne napędy

For Each Drv In FileSys.Drives

Row = Row + 1

Cells(Row, 1) = Drv.DriveLetter

Cells(Row, 2) = Drv.IsReady

Select Case Drv.DriveType

Case 0: Cells(Row, 3) = "Typ nieznany"

Case 1: Cells(Row, 3) = "Dysk wymienny"

Case 2: Cells(Row, 3) = "Dysk twardy"

Case 3: Cells(Row, 3) = "Dysk sieciowy"

Case 4: Cells(Row, 3) = "Napęd CD-ROM"

Case 5: Cells(Row, 3) = "RAM Disk"

End Select

Cells(Row, 4) = Drv.VolumeName

Cells(Row, 5) = Drv.TotalSize

Cells(Row, 6) = Drv.AvailableSpace

Next Drv

'Utwórz tabelę

ActiveSheet.ListObjects.Add xlSrcRange, _

Range("A1").CurrentRegion, , xlYes

End Sub

Operacje z plikami tekstowymi

W języku VBA istnieje szereg poleceń, które pozwalają na wykonywanie niskopozio-mowych operacji na plikach. Wspomniane polecenia operacji wejścia-wyjścia dają znacznie większą kontrolę nad plikami niż zwykłe opcje importowania i eksportowania plików dostępne w Excelu.

Wyróżniamy trzy metody dostępu do pliku:

- Dostęp sekwencyjny - to metoda najbardziej popularna, pozwalająca odczytywać i zapisywać pojedyncze znaki lub całe wiersze danych.

- Dostęp losowy - wykorzystywany tylko w przypadku tworzenia aplikacji bazodanowych (nie powinno się tego robić w języku VBA, ponieważ istnieją lepsze techniki).

- Dostęp binarny - wykorzystywany jest w celu odczytywania lub zapisywania dowolnego bajta w pliku, na przykład podczas operacji zapisywania lub wyświetlania mapy bitowej (ten sposób jest bardzo rzadko wykorzystywany w języku VBA).

Ponieważ w języku VBA rzadko wykorzystuje się losowy lub binarny dostęp do plików, w tym rozdziale skoncentrujemy się na plikach o dostępie sekwencyjnym, w których wiersze danych są odczytywane są kolejno od początku pliku. W przypadku zapisywania, dane są zapisywane i dołączane na końcu pliku.

W metodzie odczytywania i zapisywania plików tekstowych opisanej w tej książce zastosowano tradycyjne pojęcie kanału danych. Innym, alternatywnym rozwiązaniem jest zastosowanie podejścia obiektowego. Obiekt FileSystemObject zawiera obiekt TextStream, który można wykorzystać do odczytywania lub zapisywania plików tekstowych. Obiekt FileSystemObject należy do biblioteki Windows ScriptingHost. Ta właściwość jest wyłączona w wielu systemach ze względu na duże ryzyko rozpowszechniania wirusów.

Otwieranie plików tekstowych

Do otwierania plików do zapisu lub odczytu służy instrukcja VBA Open ( nie należy mylić z metodą Open obiektu Workbook). Zanim będziesz mógł odczytywał dane, plik musi zostać wcześniej otwarty.

Instrukcja Open jest dość uniwersalna i ma całkiem złożoną składnię:

Open ścieżka For tryb [Access dostęp] [blokada] _

As [#]numer_pliku [Len=rozmiar_rekordu]

- ścieżka (wymagany) — nazwa i ścieżka (opcjonalnie) pliku, który ma być otwarty.

- tryb (wymagany) - może mieć jedną z poniższych wartości:

• Append - tryb dostępu sekwencyjnego, który pozwala na czytanie danych lub ich dołączanie na końcu pliku;

• Input - tryb dostępu sekwencyjnego, który pozwala na czytanie danych, ale nie pozwala na ich zapisywanie;

• Output - tryb dostępu sekwencyjnego, który pozwala na czytanie danych lub ich zapisywanie (w tym trybie zawsze jest tworzony nowy plik, a istniejący wcześniej plik o tej samej nazwie jest usuwany);

• Binary - tryb dostępu losowego, który pozwala na odczytywanie lub zapisywanie danych bajt po bajcie;

• Random - tryb dostępu losowego pozwalający na czytanie lub zapis informacji w blokach, których rozmiar określa ostatni argument instrukcji Open - rozmiar rekordu.

- dostęp (opcjonalny) - określa rodzaj operacji dozwolonych do wykonania z plikiem. Może mieć wartość Read (czytanie), Write (zapisywanie) lub Read Write (czytanie i zapisywanie).

- blokada (opcjonalny) - przydaje się w przypadku używania pliku jednocześnie przez wielu użytkowników. Dopuszczalne wartości to Shared (współdzielony), Lock Read (blokada odczytu), Lock Write (blokada zapisu) oraz Lock Read Write (blokada odczytu i zapisu).

- numer_pliku (wymagany) - numer pliku w zakresie od l do 511. Aby uzyskać następny wolny numer pliku, można skorzystać z funkcji FreeFile (opis funkcji FreeFile można znaleźć w punkcie „Przydzielanie numeru pliku" w dalszej części rozdziału).

- rozmidr_rekordu (opcjonalny) - rozmiar rekordu (dla plików o dostępie losowym) lub rozmiar bufora (dla plików o dostępie sekwencyjnym).

Odczytywanie plików tekstowych

Standardowa procedura odczytywania danych z pliku tekstowego w języku VBA składa się z następujących kroków:

1. Otwarcie pliku za pomocą instrukcji Open.

2. Określenie pozycji w pliku za pomocą funkcji Seek (opcjonalnie).

3. Odczytywanie danych z pliku (za pomocą instrukcji Input, Input # lub Line Input #).

4. Zamknięcie pliku za pomocą instrukcji Close.

Zapisywanie danych do plików tekstowych

Standardowa procedura zapisywania danych do pliku tekstowego jest następująca:

1. Otwarcie lub utworzenie pliku za pomocą instrukcji Open.

2. Określenie pozycji w pliku za pomocą funkcji Seek (opcjonalnie).

3. Zapis danych do pliku za pomocą instrukcji Write # lub Print #.

4. Zamknięcie pliku za pomocą instrukcji Close.

Przydzielanie numeru pliku

Większość programistów VBA po prostu przydziela odpowiedni numer pliku i podaje go jako argument instrukcji Open, na przykład:

Open "mójplik.txt" For Input As #1

Gdy taka instrukcja zostanie wykonana, w dalszej części kodu można się odwoływać do pliku jako do #l.

Jeżeli plik jest otwierany w czasie, kiedy inny jest już otwarty, kolejny plik można oznaczyć jako #2:

Open "inny.txt" For Input As #2

Innym sposobem uzyskania numeru pliku jest użycie funkcji FreeFile w celu pobrania uchwytu do pliku. Po wykonaniu tej funkcji można odwoływać się do pliku za pomocą zmiennej. Oto przykład:

FileHandle = FreeFile

Open "mójplik.txt" For Input As FileHandle

Import i eksport plików tekstowych w Excelu

Excel obsługuje trzy typy plików tekstowych:

• CSV (ang. Comma-Separated Value) - kolumny danych są rozdzielane przecinkami, a każdy wiersz kończy się znakiem powrotu karetki (w niektórych narodowych wersjach Excela zamiast przecinka używany jest średnik).

• PRN - kolumny danych są wyrównywane przez pozycje znaków, a każdy wiersz kończy się znakiem powrotu karetki.

• TXT (pliki z danymi rozdzielanymi znakami tabulacji) - kolumny danych są rozdzielane znakami tabulacji, a każdy wiersz kończy się znakiem powrotu karetki.

Jeżeli spróbujesz otworzyć plik tekstowy za pomocą polecenia Plik/Otwórz, na ekranie może poja­wić się okno Kreatora importu tekstu, ułatwiającego poprawne zdefiniowanie poszczególnych kolumn. Jeżeli plik tekstowy jest rozdzielany znakami tabulacji lub spacji, Excel zazwyczaj otwiera plik bez wyświetlania kreatora. Jeżeli dane nie zostaną poprawnie odczytane, zamknij plik i spróbuj zmienić rozszerzenie jego nazwy na .txt.

Kreator konwersji tekstu na kolumny jesi niemal identyczny, ale działa poprawnie tylko w przypadku danych zapisanych w pojedynczej kolumnie. Aby go uruchomić, przejdź na kartę Dane i naciśnij przycisk Tekst jako kolumny, znajdujący się w grupie opcji Narzędzia danych.

Określanie lub ustawianie pozycji w pliku

W przypadku sekwencyjnego dostępu do plików znajomość bieżącej lokalizacji wewnątrz pliku jest rzadko potrzebna. Jeżeli jednak z jakiegoś powodu taka informacja jest potrzebna możesz użyć funkcji Seek.

Instrukcje pozwalające na odczytywanie i zapisywanie plików

W języku VBA znajdziemy kilka instrukcji pozwalających na odczytywanie i zapisywa­nie danych do pliku.

Do odczytywania danych z plików o dostępie sekwencyjnym służą trzy instrukcje:

• Input - odczytuje z pliku określoną liczbę znaków.

• Input # - odczytuje dane z pliku, przypisując wartości do serii zmiennych oddzielonych od siebie przecinkami.

• Line Input # - odczytuje cały wiersz danych, ograniczony znakami powrotu karetki i (lub) wysunięcia wiersza.

Do zapisywania danych w plikach o dostępie sekwencyjnym służą dwie instrukcje:

• Write # - zapisuje do pliku ciąg wartości, gdzie kolejne wartości są od siebie oddzielone przecinkami i ujęte w apostrofy. W przypadku zakończenia instrukcji średnikiem po wartościach nie jest wprowadzana sekwencja znaków CR LF. Dane zapisywane do pliku za pomocą instrukcji Write # zazwyczaj są odczytywane z pliku za pomocą instrukcji Input #.

• Print # - zapisuje do pliku ciąg wartości, gdzie kolejne wartości są od siebie oddzielone znakiem tabulacji. W przypadku zakończenia instrukcji średnikiem po wartościach nie jest wprowadzana sekwencja znaków CR LF. Dane zapisywane do pliku za pomocą instrukcji Print # zazwyczaj są odczytywane z pliku za pomocą instrukcji Line Input # lub Input.

Przykłady wykonywania operacji na plikach

W tym podrozdziale przedstawimy kilka przykładów ilustrujących różne techniki wyko­nywania operacji na plikach tekstowych.

Importowanie danych z pliku tekstowego

Procedura przedstawiona poniżej odczytuje dane z pliku tekstowego, a następnie umieszcza każdy wiersz danych w osobnej komórce (począwszy od aktywnej komórki):

Sub ImportData()

MyFile3 = ActiveWorkbook.Path & "\" & "text1.txt"

Open MyFile3 For Input As #1

r = 0

Do Until EOF(1)

Line Input #1, Data

ActiveCell.Offset(r, 0) = Data

r = r + 1

Loop

Close #l

End Sub

`-------------------------------------------------------------------------------------------

Sub ImportDataM()

FileHandle = FreeFile

MyFile3 = ActiveWorkbook.Path & "\" & "text1.txt"

Open MyFile3 For Input As FileHandle

r = 0

Do Until EOF(FileHandle)

Line Input #FileHandle, Data

ActiveCell.Offset(r, 0) = Data

r = r + 1

Loop

Close FileHandle

End Sub

`-------------------------------------------------------------------------------------------

W praktyce taka procedura nie będzie jednak zbyt przydatna, poniewaz kazdy wiersz danych jest wpisywany do osobnej komórki. W takiej sytuacji o wiele latwicjszym rozwią-zanicm będzie po prostu bezpośrednie otwarcie pliku tekstowego za pomocą polecenia Plik/Otwórz.

Kiedy Excel niepoprawnie importuje dane...

Czy zdarzyło Ci się kiedyś, że próbowałeś zaimportować plik CSV lub wkleić skopiowane dane do arkusza i okazało się, że Excel niepoprawnie rozdzielił dane do poszczególnych komórek? Jeżeli tak, to przyczyną jest zapewne funkcjonowanie mechanizmu Tekst jako kolumny. Poniżej przedsta­wiamy wygląd drugiego ekranu kreatora konwersji tekstu na kolumny, który pozwala na podzielenie pojedynczej kolumny danych oddzielonych separatorami na wiele kolumn (rysunek 10). W tym przypadku zdefiniowane zostały trzy ograniczniki (separatory) danych: tabulator, przecinek oraz dwukropek.

Rysunek 10. Okno Kreatora importu tekstu

Dzielenie tekstu na poszczególne kolumny jest bardzo użytecznym mechanizmem. Problem polega jednak na tym, że Excel próbuje być pomocny i zapamiętuje ustawienia separatorów dla operacji importowania kolejnych plików CSV i wklejania skopiowanych danych. Czasami zapamiętanie takich ustawień rzeczywiście może być bardzo przydatne, ale jednak w praktyce chyba bardziej przeszka­dza niż pomaga. Aby usunąć ustawienia separatorów, powinieneś przywołać na ekran kreatora konwersji, usunąć zaznaczenie zbędnych separatorów i nacisnąć przycisk Anuluj.

Jeżeli importujesz lub wklejasz dane za pomocą makra, nie ma ono żadnej bezpośredniej możliwo­ści sprawdzenia lub skasowania ustawień separatorów. Rozwiązaniem tego problemu może być swego rodzaju zasymulowanie operacji konwersji tekstu na kolumny. Procedura, której kod prezen­tujemy poniżej, dokonuje właśnie takiej operacji, symulując usunięcie zaznaczeń wszystkich sepa­ratorów (ale nie dokonuje żadnych modyfikacji arkusza).

Sub ClearTextToColumns()

On Error Resume Next

If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"

Range("Al").TextToColumns Destination:=Range("A1"), _

DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, _

ConsecutiveDelimiter:=False, _

Tab:=False, _

Semicolon:=False, _

Comma:=False, _

Space:=False, _

Other:=False, _

OtherChar:=""

If Range("A1") = "XYZZY" Then Range("A1") = ""

If Err.Number <> 0 Then MsgBox Err.Descriptlon

End Sub

Makro zakłada, że arkusz jest aktywny i nie jest chroniony. Zwróć uwagę, że zawartość komórki Al nie zostanie zmieniona, ponieważ przy wywołaniu metody TextToColumns nie zostały zdefiniowane żadne operacje. Jeżeli komórka Al jest pusta, kod wstawia do niej tymczasowy łańcuch tekstu (ponieważ metoda TextToColumns nie będzie działać poprawnie, jeżeli komórka jest pusta). Przed zakończeniem działania procedury tymczasowy łańcuch tekstu jest usuwany.

Rejestrowanie wykorzystania Excela

Kod zaprezentowany w tym punkcie zapisuje dane do pliku tekstowego podczas każdej operacji uruchamiania i zamykania Excela. Aby zaprezentowana procedura działała niezawodnie, musi być umieszczona w skoroszycie, który otwiera się za każdym razem, kiedy uruchamiamy Excela - do tego celu idealnie nadaje się osobisty arkusz makr (ang. Personal Macro Workbook).

Poniższa procedura jest umieszczona w module kodu obiektu ThisWorkbook i jest wykonywana podczas otwierania pliku:

Private Sub Workbook_Open()

Open Application.DefaultFilePath & "\excelusage.txt" For Append As #1

Print #1, " Uruchomienie programu Excel " & Now

Close #1

End Sub

`?Application.DefaultFilePath

`C:\Users\user\Documents

Procedura dodaje wiersz do pliku o nazwie excelusage.txt. Nowy wiersz zawiera bieżącą datę i godzinę i może mieć następującą postać:

Uruchomienie programu Excel 2011-12-15 12:48:50

Zakończenie programu Excel 2011-12-15 12:48:54

Pokazana poniżej procedura wykonuje się podczas zamykania skoroszytu. Jej działanie polega na dodaniu do pliku tekstowego wiersza zawierającego frazę Zakończenie pracy programu wraz z bieżącą datą i godziną.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Open Application.DefaultFilePath & "\excelusage.txt" _

For Append As #1

Print #1, " Zakończenie programu Excel " & Now

Close #1

End Sub

Filtrowanie zawartości pliku tekstowego

W przykładzie zaprezentowanym poniżej zademonstrujemy metodę jednoczesnego prze­twarzania dwóch plików tekstowych. Procedura FilterFile odczytuje dane z pliku tek­stowego (infputfile1.txt) i kopiuje wiersze zawierające określony ciąg znaków (na przykład "Styczeń") do drugiego pliku tekstowego (outputfile1.txt).

Sub FilterFile()

Open ThisWorkbook.Path & "\infputfile1.txt" For Input As #1

Open ThisWorkbook.Path & "\outputfile1.txt" _

For Output As #2

TextToFind = "Styczeń"

Do Until EOF(1)

Line Input #1, Data

If InStr(1, Data, TextToFind) Then

Print #2, Data

End If

Loop

Close #1 'Zamknij wszystkie pliki

Close #2 'Zamknij wszystkie pliki

End Sub

Skoroszyt z tym przykładem - FiltrowaniePlikuTekstowego.xlsm.

---