Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Kolejne części tego kursu traktować będą nt. obsługi danych zewnętrznych z poziomu języka VBA. W pierwszym rozdziale temu poświęconym skupimy się na plikach tekstowych. Zobaczymy zarówno jak zapisywane są w kodzie języka VBA kwerendy pobierające dane - używając zwykłego rejestratora makr i analizując potem kod. Następnie spojrzymy też na typowe polecenia odczytujące dane z plików - bez użycia kwerend i rejestratora. Sprawdzimy jak otwierać i zamykać pliki, jak śledzić ich zawartość oraz jak zapisywać dane w takim pliku.

Jako pierwszy element tej części zarejestrujemy makro, które pobierze zawartość pliku, który jest drugim z powyższych załączników. Co ważne, poniższe właściwości można stosować także dla innych źródeł danych (łatwo to sprawdzić) takich jak chociażby baza danych czy sieć internet - my jednak skupimy się na plikach tekstowych.
Włączamy rejestrator makr zostawiając domyślne ustawienia nazwy oraz jego lokalizacji:

Następnie standardową procedurą, importujemy zawartość załącznika:

Pamiętajmy, aby zmienić pochodzenie pliku na "1250: Środkowoeuropejski (Windows)" i rozdzielany tabulatorem. Po pobraniu pliku, zatrzymujemy rejestrację:

i przechodzimy do podglądu kodu zarejestrowanej czynności importu:

Sub Makro1()
'
' Makro1 Makro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\dane_import.txt", _
        Destination:=Range("$A$1"))
        .Name = "dane_import"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1250
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

• .Name - nazwa kwerendy pod jaką będzie zapisana ona w arkuszu.
• .FieldNames - przyjmuje wartość "True" lub "False" i definiuje, czy kwerenda ma traktować pierwszy wiersz importowanego pliku jako nagłówki kolumn, czy też nie.
• .RowNumbers - "True" lub "False" - dodaje kolumnę z numerem wiersza, ale nie działa w przypadku importowania plików tekstowych, za to działa świetnie podczas importu z baz danych.
• .FillAdjacentFormulas - kolejna właściwość z ustawieniem "True"" lub "False", jej użycie powoduje, iż ewentualne formuły umieszczone w kolumnach przylegających do prawej strony kwerendy będą się automatycznie uzupełniały lub usuwały w zależności ile mamy wierszy w wyniku jej uruchomienia - będzie ich tyle ile wierszy kwerendy.
• .PreserveFormatting - "True" lub "False" - ustawienie odpowiadające zachowywaniu formatowania komórek podczas kolejnych odświeżeń importu.
• .RefreshOnFileOpen - także "True" lub "False" - ustawienie, które "mówi" programowi, czy podczas otwierania pliku Excela, dane zapytanie importu danych ma być automatycznie odświeżone. Automatycznie.
• .RefreshStyle - posiada kilka możliwych ustawień, w zależności od tego jak arkusz ma się zachowywać w trakcie ponownego odświeżania importu. Możemy ustawić, czy komórki mają być dodawane/usuwane w zależności od ilości danych źródłowych (ustawienie xlInsertDeleteCells), można nic z nimi nie robić, licząc się z tym, że aktualne komórki mogą być nadpisane przez importowane dane (xlOverwriteCells), lub też pozwolić na wstawienie wierszy, w celu uniknięcia nadpisania danych, ale bez usuwania (xlInsertEntireRows).
• .SavePassword - "True" lub "False" - określa czy w przypadku korzystania ze źródła danych ODBC, w pamięci ma być przechowywane hasło.
• .SaveData - jeśli "True" arkusz zapisuje dane, jeśli "False" zapisana zostanie jedynie definicja importu (kwerendy).
• .AdjustColumnWidth - ponownie "True" lub "False" - można określić, czy szerokość kolumn ma być dostosowywana do danych przy każdym odświeżaniu zapytania.
• .RefreshPeriod - domyślnie przyjmuje wartość "0", co oznacza, że odświeżania automatycznego nie będzie, wartości podawane w tej właściwości wyrażone są w minutach.
• .TextFilePromptOnRefresh - jeśli "True" program będzie pytał o lokalizację pliku za każdym odświeżeniem.
• .TextFilePlatform - podajemy kodowanie pliku.
• .TextFileStartRow - w formie liczby całkowitej, podajemy od którego wiersza ma być realizowany import danych.
• .TextFileParseType - określenie, czy importowany tekst w kolumnach jest stałej szerokości (xlFixedWidth), czy też rozdzielany określonym znakiem (xlDelimited).
• .TextFileTextQualifier - określa wyróżnik tekstu - może być brak oznaczenia (xlTextQualifierNone), może być cudzysłów, który jest domyślnym ustawieniem (xlTextQualifierDoubleQuote), lub też pojedynczy apostrof (xlTextQualifierSingleQuote).
• .TextFileConsecutiveDelimiter - oznaczenie odpowiadające pozycji w kreatorze: "kolejne ograniczniki traktuj jako jeden" - czyli "True" tylko w przypadku tylko importu plików tekstowych rozdzielanych znakami.
• .TextFileTabDelimiter/.TextFileSemicolonDelimiter/.TextFileCommaDelimiter/.TextFileSpaceDelimiter - określenie jaki znak rozdziela kolumny w importowanym pliku tekstowym: tabulacja, średnik, przecinek, spacja.
• .TextFileColumnDataTypes - podawane w postaci: Array(",",") - gdzie po przecinku podajemy formaty kolejnych kolumn w postaci określeń lub cyfr. 1 - oznacza format ogólny, 2 - format tekstowy, 3 - pominięcie danej kolumny, 4 i kolejne formaty zawiązane z datą.

Pozostałe metody oraz właściwości dotyczące QueryTable można znaleźć w systemie pomocy VBA - wystarczy wpisać do wyszukiwarki "QueryTable Object Members" i na pierwszym miejscu pojawi się taki właśnie temat, w którym można odnaleźć wyczerpujące informacje.

Zauważmy, że w powyższym temacie użyliśmy metody "Add" i dodaliśmy nową kwerendę do arkusza. Jeśli określona kwerenda jest już dodana do arkusza, można ją zwyczajnie odświeżyć poleceniem: Range("A1").QueryTable.Refresh, wskazując lokalizację i potem używając definicji "QueryTable" i metody "Refresh". W taki sam sposób (zamiast "Refresh") możemy zmienić określoną właściwość danej kwerendy. Możemy też sterować lub zmieniać właściwości kwerend bez podawania lokalizacji, za to wskazując ją samą: ActiveSheet.QueryTables(1).Refresh.
Na zakończenie tej części wspomnieć trzeba, iż może samo dodawanie kwerend do arkusza przy wykorzystaniu języka VBA nie ma specjalnego sensu, za to jednak samo już później odświeżanie w połączeniu z innymi poleceniami - o wiele bardziej - stąd temat ten został w tym miejscu przedstawiony.

W dalszej części tego rozdziału, krótko omówimy sobie obsługę plików z poziomu języka VBA, ale w kontekście ich otwierania, odczytu, zapisu oraz zamykania. Jako, że są to bardzo powiązane zagadnienia, wspomnimy także jak tworzyć, wybierać, usuwać foldery na dysku komputera.
Zaczniemy od końca, czyli od operacji na folderach. Przyjrzyjmy się poniższemu kodowi (jest on także dostępny w naszym załączniku do tego rozdziału):

Sub operacje_foldery_pliki()

MkDir ("C:\folder_testowy")
MsgBox "Lokalizacja: 'C:\folder_testowy' - sprawdź", vbOKOnly, "Folder utworzono pomyślnie"
RmDir ("C:\folder_testowy")
MsgBox "Lokalizacja: 'C:\folder_testowy' - sprawdź", vbOKOnly, "Folder usunięto pomyślnie"

End Sub

Sub pliki()

'Definiujemy zmienne tablicowe:
Dim tablica_in() As String
Dim tablica_out(1 To 100, 1 To 100) As String

'Definiujemy obiekt pozwalający na systemową obsługę plików i tworzymy nowy plik na dysku:
Set obiekt_systemu_plikow = CreateObject("Scripting.FileSystemObject")
Set plik_out = obiekt_systemu_plikow.CreateTextFile("C:\output.txt", True)

'--------ISTNIEJĄCY PLIK - CZYTANIE

'Otwieramy istniejący plik:
Open ("C:\input.txt") For Input As #1

'Ustawiamy liczniki wierszy i kolumn:
licznik1 = 1
licznik2 = 1

'Przechodzimy przez kolejne linie pliku:
Do While Not EOF(1)
	Line Input #1, linijka
	tablica_in = Split(linijka, ";")
licznik2 = 1

'I w ramach danej linii rozbijamy tekst na kolumny,
'zapisując je w drugim wymiarze macierzy, dla określonego pierwszego wymiaru:
For Each Item In tablica_in
	tablica_out(licznik1, licznik2) = tablica_in(licznik2 - 1)
	licznik2 = licznik2 + 1
Next

'Kolejny wiersz:
	licznik1 = licznik1 + 1
Loop

'Określamy końcową liczbę wierszy oraz kolumn w pliku wynikowym:
wiersze = licznik2 - 1
kolumny = licznik1 - 1

'--------NOWY PLIK - WPISYWANIE

'Tworzymy tekst nowych wierszy, korzystając z elementów macierzy:
'Wg wierszy:
For licznik1 = 1 To wiersze
    licznik2 = licznik1
    
    '...i kolumn w ramach wiersza
    For licznik2 = 1 To kolumny
    
    'Sprawdzamy, czy to nie jest ostatni element (od czego zależe wpisanie kolejnego średnika lub nie):
		If licznik2 = kolumny Then
			tekst = tekst & tablica_out(licznik2, licznik1)
			Else
				tekst = tekst & tablica_out(licznik2, licznik1) & ";"
		End If
    
    Next licznik2
    
    'Wpisanie tekstu do kolejnej linii pliku wynikowego:
    plik_out.writeline (tekst)
    tekst = ""
    
'Kolejny wiersz:
Next licznik1

'Zamykamy pliki:
Close #1
plik_out.Close

End Sub

• Definiujemy zmienne tablicowe - użyjemy dwóch typów zmiennych tablicowych: "tablica_in" - dynamiczna, jednowymiarowa zmienna tablicowa (o zmiennej liczbie elementów typu String), "tablica_out" - stała, dwuwymiarowa zmienna tablicowa (macierz 100 x 100 elementów o typie String).
• Definiujemy obiekt, który umożliwi nam utworzenie nowego pliku na dysku komputera - używając polecenia CreateObject("Scripting.FileSystemObject") poprzedzonego wyrażeniem Set. Następnie w odniesieniu do obiektu tego typu, język umożliwia stworzenie pliku tekstowego metodą CreateTextFile.
• Otwieramy plik zapisany na dysku komputera - poleceniem Open - jest to plik, z którego będziemy odczytywać zawartość i ją przetwarzać. Sformułowanie For Input oznacza, że zamierzamy odczytywać zawartość pliku. Alternatywnie podać można sformułowanie For Output dla zapisu danych (i wówczas nieistniejący plik zostanie utworzony - jest to swego rodzaju "alternatywą" dla powyżej podanej metody CreateTextFile). Po tym wyrażeniu następuje oznaczenie numeryczne pliku poprzedzone znakiem "#" - może ono przyjmować wartości od 1 do 255. Alternatywnie, jeśli nie znamy kolejnego numeru, który jest wolny, możemy użyć funkcji FreeFile.
• Kolejny krok, to ustawienie na pozycję 1 zmiennych będących licznikami wierszy i kolumn, które będą nam pomocne podczas poruszania się po pliku. Zmienna "licznik1" będzie liczyć wiersze, "licznik2" kolumny (czyli kolejne liczby, które są oddzielone średnikiem w ramach jednej linii).
• Dalej używamy pętli Do While w celu odczytu i zapisania w zmiennych tablicowych kolejnych wierszy i liczb w nich umieszczonych. Podany warunek Not EOF(1) oznacza: powtarzaj dopóki nie będzie końca pliku oznaczonego (wcześniej) "jedynką".
• Line Input jest poleceniem użytym wewnątrz pętli, którego zadaniem jest zapisanie kolejnych linii do zmiennej "linijka".
• Zaraz po tym następuje "rozbicie" linii i jej zapis w zmiennej tablicowej "tablica_in" - przy użyciu funkcji Split, którą mieliśmy okazję poznać już wcześniej.
• Wiersz kolejny: licznik2=1 ma za zadanie ustawić dla każdej linii (bez względu, która aktualnie jest przemierzana) tę zmienną na pierwszej kolumnie.
• Ustawienie zmiennej "licznik2" na wartości równe 1 za każdym razem w pętli, o której przed chwilą wspomnieliśmy jest spowodowane koniecznością jej ponownego użycia w kolejnej pętli, która jest zagnieżdżona w poprzedniej (można by to określić jako "podpętla"). Jest ona wyrażona poleceniem For Each Item In tablica_in i zakończona Next. Zawiera ona przypisanie już poszczególnych liczb (umieszczonych w ramach danego wiersza) do dwu wymiarowej zmiennej tablicowej "tablica_out".
• Kolejne kroki w ramach tych pętli to zwiększenie liczników kolumn i wierszy.
• Ostatni etap to przypisanie zawartości liczników do zmiennych "wiersze" i "kolumny" w celu późniejszego ich wykorzystania - zauważmy, że dokonaliśmy w tym momencie "transpozycji" przypisując zmienną "licznik1" oznaczającą pierwotnie wiersze do "kolumny" i odwrotnie. W ramach odpowiednich, transponowanych wierszy i kolumn budujemy dwie pętle (zewnętrzną po wierszach i wewnętrzną po kolumnach) zapisując odpowiednie elementy zmiennej "tablica_out" w pliku "output.txt". Pomijając budowę pętli, zauważyć trzeba, że użyliśmy dodatkowo polecenia warunkowego If..Then w celu określenia, czy powinniśmy dodać kolejny średnik, czy też już nie (w ramach danej linii). Sam zapis do pliku po uprzednim zbudowaniu danej linijki odbywa się poleceniem .writeline
• Odpowiednio użytymi poleceniami Close zamykamy oba pliki kończąc procedurę.

Procedura jest oczywiście napisana w taki sposób, że działa dla plików z tekstem/liczbami rozdzielonymi średnikami dla maksymalnie 100 wierszy i 100 słów/liczb w każdym wierszu. Mimo dosyć prostego zadania, jakie przy jej pomocy zrealizowaliśmy, zawarto w niej dość szeroki zakres poleceń służących obsłudze plików tekstowych, które czytelnicy mogą wykorzystać w innym (niż zaprezentowany) celu.