Mam w pliku Excela około 20 tabel ze zdefiniowanymi nazwami, dla których co tydzień aktualizuję dane (zmienia się zakres). Tabele w Excelu są źródłem danych dla bazy raportowej w Accessie. Po dodaniu dodatkowych danych do tabel w Excelu muszę poprzez menedżera nazw uaktualnić wszystkie zakresy. Czy istnieje jakiś sposób, aby zautomatyzować ten proces?
Trzeba na każdej z tabel stworzyć Obiekt Tabela. Najprościej i najszybciej będzie wykorzystać w tym celu polecenie Formatuj jako tabelę ze wstążki Narzędzia główne. Każdą z tabel z danymi trzeba najpierw potraktować tym poleceniem, a dopiero w drugiej kolejności ponadawać im nazwy. Jedną z przydatnych cech obiektu Tabela jest to, że "rośnie" ona po wpisaniu nowych danych. A równolegle ze wzrostem obiektu Tabela powiększa swoje rozmiary nazwany zakres. Polecenie: formatuj jako tabelę jest najszybszym sposobem tworzenia "rozciągliwych" nazw zakresów, bez potrzeby pisania formuł i działania w menedżerze nazw.
Obiekt Tabela jest tutaj bardzo użyteczny i prosty w przygotowaniu. Budowanie dynamicznie zmieniającego się zakresu jest również możliwe z wykorzystaniem funkcji =Przesunięcie(). Jest to jednak znacznie trudniejsze i zawiera duży margines błędu. Jednak jest to do wykonania. Przykład takiego rozwiązania w załączniku.
Wykorzystane:
Załączniki
Ewentualnie można przy kilku założeniach wykorzystać poniższe makro. A założenia to
1. wszystkie tabele mają w pierwszym wierszu nagłówek
2. tabele nie stykają się, pomiędzy tabelami istnieje co najmniej jeden wiersz i jedna kolumna pustych komórek wokół
hmm no to z kilku założeń wyszły dwa, choć u mnie makro działa to oczywiście sugerowałbym testować na kopii excela.
Minusem oczywiście jest konieczność uruchamiania po każdym dodaniu danych, czego nie ma przy wykorzystaniu Obiektu Tabela.
Sub RozszerzZakresNazwy()
For Each nazwa In ActiveWorkbook.Names
'aktywuje zakładke w której jest dana nazwa
Worksheets(Mid(nazwa, 2, InStr(1, nazwa, "!") - 2)).Activate
'zaznacza wszystkie stykające się (wypełnione)komórki wokół danego zakresu
Range(nazwa).CurrentRegion.Select
'wybiera i zaznacza z powyższego zaznaczenia wszystkie komórki ale bez nagłówka
Range(Selection.Cells(Selection.Columns.Count + 1), Selection.Cells(Selection.Cells.Count)).Select
'nadaje tą samą nazwę rozszerzonemu zakresowi
Selection.Name = nazwa.Name
Next
End Sub
Moja wersja będzie mniej skomplikowana:
Sub AktualizujNazwy()
Dim Nazwa As Name
For Each Nazwa In Names
Nazwa.RefersToRange.CurrentRegion.Name = Nazwa.Name
Next
End SubOczywiście opieramy się na założeniu, że nazwane zakresy nie stykają się z innymi danymi
Zapomniałem dodać, że nazwy które mają na końcu "anchor" powinny zostać pominięte, bo są to pojedyncze komórki (kotwice) i tak powinno zostać.
Sub AktualizujNazwy()
Dim Nazwa As Name
For Each Nazwa In Names
If Right(Nazwa, 6) <> "anchor" Then
Nazwa.RefersToRange.CurrentRegion.Name = Nazwa.Name
Else
End If
Next
End Sub
