Autor: marzatela

Power Query

Power Query to jedno z narzędzi Excela o bardzo szerokim zastosowaniu. Umożliwia pobranie i połączenie, a finalnie także przetwarzanie danych z różnych źródeł.
Korzystanie z PQ nie wymaga znajomość VBA. Od wersji Excela 2016 jest to narzędzie wbudowane, w wersjach wcześniejszych Excela – konieczne jest (było?) pobranie i zainstalowanie oddzielnego dodatku do Excela. 

Power Query jest dostępne na karcie Dane –> grupa opcji Pobieranie i przekształcanie danych.

Power Query umożliwia pobieranie danych z wielu różnych źródeł, jest tu naprawdę sporo możliwości.
Podstawowe grupy typów to:

  • Z pliku
    do pobierania danych z plików m.in Excela, tekstowych lub JSON, a nawet całych folderów. 
  • Z bazy danych
    tu źródłem mogą być różne typy baz danych
  • Z platformy Azure
    dane pobrane z połaczenia z usługą Azure
  • Z usług online
    czyli to, co można pozyskać z sieci Microsoftu
  • Z innych źródeł
    to, co jest dostępne na stronach internetowych

Każde z nich wymaga osobnego omówienia, co postaram się sukcesywnie robić i rozbudowywać. 

Właściwości pliku

GetFile to jedna z metod obiektu FileSystem.Object.

Ma jeden parametr wejściowy:

    • filespec– pełna nazwa pliku
      Argument obowiązkowy.

Za pomocą tej metody można odczytać następujące właściwości pliku:

    • Name – nazwa folderu.
    • Path – ścieżka do folderu.
    • Size– rozmiar wszystkich plików folderze.
    • DateCreated– data utworzenia.
    • DateLastModified– data ostatniej modyfikacji.
    • ParentFolder– folder nadrzędny.

Lista podfolderów w katalogu

W jaki sposób wylistować listę podfolderów w katalogu?  Jednym z lepszych sposobów jest wykorzystanie metody FileSystemObject.GetFolder. 

Załóżmy, że mamy taki taki układ folderów (przykład z mojego dysku):

kliknij, aby powiększyć

Katalog z listą podfolderów można wylistować w komórkach Excela do takiej postaci:

kliknij, aby powiększyć

Przykładowy kod może wyglądać tak:

Public Sub Foldery()
Dim FSO As Object
Dim Folder As Object
Dim Podfolder As Object
Dim JNazwa As String
Dim NF As String
Dim i As Long
Dim k As Long
Set FSO = CreateObject(„Scripting.FileSystemObject”)
Set Folder = FSO.GetFolder(Range(„A2”).Value)
i = 2
For Each Podfolder In Folder.SubFolders
i = i + 1
JNazwa = Podfolder.Name
Cells(i, 2) = JNazwa
i = Podfoldery(Folder & „\” & JNazwa, 3, i)
Next Podfolder
End Sub
_________________________________
Public Function Podfoldery(JF As String, k As Long, w As Long)
Dim FSOP As Object
Dim FolderP As Object
Dim PodfolderP As Object
Dim JNazwaP As String
Set FSOP = CreateObject(„Scripting.FileSystemObject”)
Set FolderP = FSOP.GetFolder(JF)
For Each PodfolderP In FolderP.SubFolders
w = w + 1
JNazwaP = PodfolderP.Name
Cells(w, 3) = JNazwaP
Next PodfolderP
Podfoldery = w
Set FolderP = Nothing
Set FSOP = Nothing
End Function

Oczywiście kod można rozbudować do wylistowania kolejnych poziomów. 
Jeśli trzeba – mogę pomóc. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

 

Właściwości folderu

GetFolder to jedna z częściej wykorzystywanych metod obiektu FileSystem.Object.

Ma jeden parametr wejściowy:

    • folderspec– ścieżka do konkretnego obiektu.
      Argument obowiązkowy.

Za pomocą tej metody można odczytać następujące właściwości folderu:

    • Name – nazwa folderu.
    • Path – ścieżka do folderu.
    • Size– rozmiar wszystkich plików folderze.
    • DateCreated– data utworzenia.
    • DateLastModified– data ostatniej modyfikacji.
    • DateLastAccess– data ostatniego otwarcia.
    • ParentFolder– folder nadrzędny.
    • Files– kolekcja plików w folderze.
    • SubFolders– kolekcja podfolderów.

Błąd wykonania 9

kliknij, aby powiększyć

Błąd wykonania 9 – Subscript out of range

Błąd ten pojawia się w sytuacji odwołania do nieistniejącego obiektu (np.arkusza, tabeli) lub do wartości spoza przypisanego zakresu (np.5 kolumna w 4-kolumnowym zakresie). 

Jak się przed tym zabezpieczyć? Oprócz ogólnej obsługi błędów na pewno trzeba pilnować się przed „literówkami” w kodzie.  Warto też uchronić się przed ingerencją użytkowników końcowych. Ja często stosuję nazwy kodowe arkuszy uodparniające na zmianę nazwy arkusza.  

Inne błędy wykonania VBA (Run-time) są tu:
Błędy wykonania VBA