Excel, Access, VBA ....

Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

RSS

VBA - przykładowe rozwiązania

środa, 03 stycznia 2018

Ile plików w katalogu?

W komentarzu pod notką
Odczyt listy plików w katalogu
pojawił się komentarz z pytaniem jak przerobić makro, aby liczyło ilość plików. Przerobiłam makro, przy okazji zmieniając procedurę na funkcję i wyszło mi coś takiego:

Public Function PoliczPliki()
Dim Katalog As String
Dim NazwaPliku As String
Katalog = Range("A1").Value
Katalog = Katalog & "\"
PoliczPliki = 0
NazwaPliku = Dir(Katalog & "*.*")
Do While NazwaPliku <> ""
PoliczPliki = PoliczPliki + 1
NazwaPliku = Dir
Loop
End Function

Powinno zadziałać, także w folderze sieciowym.

Tagi: funkcja UDF kod VBA

06:41, marzatela , VBA - przykładowe rozwiązania
Link Dodaj komentarz »

poniedziałek, 08 sierpnia 2016

Automatyczne otwieranie pliku Excela

Jak wiadomo - makra i procedury Excela służą do automatyzacji działań, pobierania czy analizy danych. A ponieważ "apetyt rośnie w miarę jedzenia" zawsze pojawia się chęć, aby jeszcze bardziej zoptymalizować, zautomatyzować i uprościć. Może warto się zastanowić, czy da się to zrobić zupełnie bez udziału użytkownika? Tak, aby nawet nie trzeba było otwierać skoroszytu Excela?

Załóżmy, że mamy plik z procedurą np. pobierającą z internetu aktualną wartość kursu waluty i zapisującą ją w kolejnych wierszach arkusza Excela. Czy nie byłoby fajnie, gdyby takie pobieranie danych odbywało się w tle, codziennie o określonej porze?
Przyjmijmy na roboczo, że takie makro (choć to oczywiście tylko przykład, dotyczy to każdego makra) nazywa się Aktualizuj i znajduje się w pliku Excela KursWalut.xlsm. Chcąc, aby automatycznie uruchamiało się przy otwarciu skoroszytu, musimy je umieścić w module skoroszytu. W zależności od wersji językowej Excela - jest on oznaczony jako Ten_skoroszyt lub This_workbook.

Moduł skoroszytu

Klikając w tym miejscu - przejdziemy do modułu skoroszytu. W tym module tworzymy makro Workbook_Open i wpisujemy nazwę makra, które ma się uruchomić przy otwarciu skoroszytu.

W tym przypadku do zdarzenia skoroszytu przy otwarciu - dopisane jest makro Aktualizuj, znajdujące się w module ogólnym.
Teraz wystarczy zapisać i zamknąć plik - przy każdym jego otwarciu uruchomione będzie makro Aktualizuj.

A na koniec - pozostaje tylko ustawienie terminu i częstotliwości, z jaką nasz skoroszyt powinien się otwierać. To już jednak nie w Excelu, a w harmonogramie zadań Windowsa. Uruchamiamy go z paska startowego Windowsa:

harmonogram zadań

Następnie tworzymy nowe zadanie:

harmonogram zadań

Następnie w zakładce Akcje wpisujemy nazwę pliku do otwarcia (po naciśnięciu przycisku Przeglądaj z prawej strony możemy oczywiście odszukać plik na dysku):

harmonogram zadań

Kolejny krok to przejście do zakładki Wyzwalacze:

Naciskamy przycisk Nowy i w kolejnym formularzu ustalamy termin i częstotliwość.
Potem wystarczy już tylko nacisnąć OK, choć oczywiście zawsze można przyjrzeć się wszystkim opcjom.

W zależności od wersji Windowsa - wygląd harmonogramu zadań może się różnić, choć zasada działania jest identyczna. Moje screeny pochodzą z Windows10.

Tagi: makro zdarzenia skoroszytu

14:05, marzatela , VBA - przykładowe rozwiązania
Link Komentarze (4) »

sobota, 21 listopada 2015

Lista plików w katalogu

Kilka lat temu napisałam na tym blogu notkę na temat odczytu listy plików z katalogu:

Odczyt listy plików z katalogu

Kilka dni temu zostałam poproszona o modyfikację przedstawionego tam makra tak, aby pliki nie tylko zostały wylistowane, ale aby w skoroszycie znalazły się do nich hiperłącza.

Nowe makro wygląda tak:

Public Sub ListaPlikow()

Dim Katalog As String
Dim NazwaPliku As String
Dim IndexSheet As Worksheet
Dim KolejnyWiersz As Long

KolejnyWiersz = 3

Set IndexSheet = ThisWorkbook.ActiveSheet
Katalog = Range("b1").Value
If Right(Katalog, 1) <> "\" Then Katalog = Katalog & "\"
If Dir(Katalog, vbDirectory) = "" Then
    MsgBox "Brak katalogu", vbCritical, "Błędne dane"
    IndexSheet.Cells(2, 2).Activate
    Exit Sub
End If
NazwaPliku = Dir(Katalog & "*.xls*")
Do While NazwaPliku <> ""
IndexSheet.Hyperlinks.Add Anchor:=Cells(KolejnyWiersz, 1), Address:=Katalog & NazwaPliku, _
        TextToDisplay:=NazwaPliku
KolejnyWiersz = KolejnyWiersz + 1
NazwaPliku = Dir
Loop

End Sub

Plik do pobrania jest na Dropboxie:

Lista plików

Dodatkowo jest tam umieszczone makro czyszczące zakres wprowadzanych danych.

Tagi: kod VBA

22:24, marzatela , VBA - przykładowe rozwiązania
Link Komentarze (1) »

wtorek, 06 października 2015

Wysyłanie maila kodem VBA

Z poziomu kodu VBA (zarówno w Excelu jak i Accessie) można też wysyłać maile. Jak to zrobić z wykorzystaniem programu Microsoft Outlook?
Przykładowe uproszczone rozwiązanie wygląda tak:

Public Sub WyslijMail()
Dim Poczta As Object
Dim MojMail As Object

Set Poczta = CreateObject("outlook.application")
Set MojMail = Poczta.createitem(0)
    With MojMail
        .To = "AdresDo@domena.pl"
        .CC = "AdresDW@DOMENA.PL"
        .Subject = "Temat maila"
        .ReadReceiptRequested = False
        .OriginatorDeliveryReportRequested = False
        .body = "Treść maila"
        .Attachments.Add "c:\Folder\MojZalacznik.xlsx"
        .Display
        '.send
    End With
End Sub

Jak widać - tworzymy obiekt Outlook.Application oraz w jego ramach - nową pozycję czyli mail (tu Obiekt MojMail).
Potem wystarczy tylko ustawić poszczególne właściwości maila. W przykładzie są one wpisane na sztywno, ale oczywiście można odwołać się tu np. do komórek arkusza z zapisanymi wartościami.
Makro może być zaimplementowane także w kodzie VBA w Accessie.

Najważniejsze właściwości to:

To - adresat właściwości. Jeżeli adresatów jest kilku - ich adresy mailowe powinny być oddzielone średnikami;
CC - adresy do wiadomości
UCC - ukryte adresy do wiadomości
Subject - temat maila
Body - temat maila
ReadReceiptRequested - żądanie potwierdzenia odczytu maila (ustawione na False lub True)
OriginatorDeliveryReportRequested - żądanie potwierdzenia dostarczenia maila (ustawione na False lub True)
Attachments.Add - dodanie załącznika do maila

Jeżeli mail ma być wysłany automatycznie, bez wyświetlania go na ekranie - stosujemy metodę Send. Chcąc obejrzeć tak stworzoną wiadomość mailową (a wysłać ręcznie) musimy zastosować metodę Display.

------------------------------------------------------------------------------

Tagi: mail mailing obiekt

12:37, marzatela , VBA - przykładowe rozwiązania
Link Komentarze (11) »

czwartek, 23 stycznia 2014

Lotto w Excelu

Przykład prostego makra losującego 6 liczb z 49:

Public Sub Lotto()
'Makro losujące 6 cyfr z 49
Dim i As Integer
Dim Liczba As Integer

For i = 1 To 6
    Randomize
    Liczba = Int(Rnd * (49) + 1)
    Range("A" & i) = Liczba
Next i
End Sub

Makro losuje liczby i wpisuje je do kolejnych wierszy kolumny A.

Wynik działania widoczny jest tu (każde uruchomienie makra powoduje inne wyniki):

losowanie w Excelu

Procedura jest bardzo prosta i ograniczona. Przede wszystkim nie ma sprawdzania powtarzalności liczb, więc nie gwarantuje unikalności.
W samym kodzie zostały wykorzystane funkcje Randomize oraz pętla For ... Next.

Tagi: makro pętla

08:38, marzatela , VBA - przykładowe rozwiązania
Link Komentarze (1) »

niedziela, 17 listopada 2013

Wstawianie łączy w komórkach Excela

W odpowiedzi na komentarz pod notką:

Odczyt i porównanie wartości komórek z różnych plików

makro będące modyfikacją przedstawionego tam przykładu. W tym przypadku - wstawiane jest łącze nie do konkretnego arkusza w linkowanym skoroszycie, a do arkusza, który był ostatnio aktywny.
Przy okazji mała dygresja - w stosunku do zamkniętego skoroszytu trudno mówić o aktywnym arkuszu. Chcąc wiedzieć jaki to arkusz - konieczne jest otwarcie go i pobranie nazwy (lub indexu) aktywnego arkusza, a następnie zamknięcie go.

Makro wstawiające takie łącza może wyglądać np. tak:

Public Sub WpiszDane()
Dim Kolumna As Range
Dim Komorka As Range
Dim Gdzie As String
Dim JakiKatalog As String
Dim JakiPlik As String
Dim JakiArkusz As String
Dim JakaKomorka
Dim Wartosc
Dim i As Integer
Dim NazwaPliku
Dim OtwPlik

JakiKatalog = Range("A1").Value
i = 0
For Each Kolumna In Range("B2:Z2")
If Kolumna.Value <> "" Then
    i = i + 1
    JakaKomorka = Kolumna.Value
         For Each Komorka In Range("a3:a1000")
            If Komorka.Value <> "" Then
               JakiPlik = Komorka.Value
               NazwaPliku = JakiKatalog & "\" & JakiPlik
               Set OtwPlik = Workbooks.Open(NazwaPliku, , True)
               JakiArkusz = OtwPlik.ActiveSheet.Name
               OtwPlik.Close SaveChanges:=False
               Set OtwPlik = Nothing
               Gdzie = "'" & JakiKatalog & "\" & "[" & JakiPlik & "]" &JakiArkusz & "'!" & _
               JakaKomorka
               Wartosc = "=" & Gdzie
               Komorka.Offset(0, i).Value = Wartosc
            End If
         Next Komorka
End If
Next Kolumna
End Sub

I już. Sprawdzałam, u mnie ładnie działa.

13:27, marzatela , VBA - przykładowe rozwiązania
Link Dodaj komentarz »

wtorek, 10 września 2013

Odświeżanie łączy - uzupełnienie

W komentarzach do jednej ze starszych notek:

Odczyt i porównanie wartości komórek z wielu plików

pojawił się problem z odświeżaniem łączy w pliku. Wprawdzie na podstawie przekazanych mi informacji nie wiem, co może być tego przyczyną, ale odpowiadając na prośbę czytelnika - zamieszczam makro otwierające i od razu zamykające wszystkie pliki z danego katalogu.
Na pewno odświeży to wartości we wszystkich powiązanych komórkach. Adres katalogu jest pobierany z komórki A1 aktywnego arkusza. Otwieranie i zamykanie kolejnych plików odbywa się w tle - na czas wykonywania makra wyłączone jest odświeżanie ekranu.

Public Sub OtworzPliki()

Dim Katalog As String
Dim NazwaPliku As String
Application.ScreenUpdating = False
Katalog = Range("A1").Value
If Right(Katalog, 1) <> "\" Then Katalog = Katalog & "\"
NazwaPliku = Dir(Katalog & "*.xls*")
Do While NazwaPliku <> ""
Workbooks.Open Filename:=Katalog & NazwaPliku, ReadOnly:=True
ActiveWindow.Close savechanges:=False
NazwaPliku = Dir
Loop
Application.ScreenUpdating = True
End Sub

Mam nadzieję, ze teraz będzie działać.

Tagi: kod VBA łącza procedura

18:04, marzatela , VBA - przykładowe rozwiązania
Link Komentarze (5) »

wtorek, 14 maja 2013

Funkcja Split w praktycznym wykorzystaniu

Jak w praktyce wykorzystać funkcję VBA Split() do rozdzielenia tekstu? Sprawdźmy to na przykładzie zilustrowanym w notce o rozdzielaniu tekstu.

rozdzielanie ciągu znaków

W arkuszu mamy dane będące złożonymi ciągami prezentującymi oznaczenia faktur. Znakiem rozdzielającym jest / . Proste makro wstawiające w kolejne komórki poszczególne części każdej z takich faktur może wyglądać tak:

Public Sub Podziel()
Dim Komorka As Range
Dim i As Integer
Dim All

For Each Komorka In Range("A1:A3")
All = Split(Komorka, "/", , vbTextCompare)
     For i = 0 To UBound(All)
       Komorka.Offset(0, i + 1) = All(i)
     Next i
Next Komorka
End Sub

Pierwsza pętla przechodzi przez poszczególne komórki. Wewnątrz niej jest jest druga pętla, która odczytuje poszczególne wartości zmiennej tablicowej All.

Kod wykorzystuje funkcje i metody:
Split, Offset, pętla For ... to, Pętla For Each In ...

Efekt działania kodu makra:

Split

Tagi: słowa kluczowe VBA

16:02, marzatela , VBA - przykładowe rozwiązania
Link Dodaj komentarz »

piątek, 05 kwietnia 2013

Dodawanie ułamków zwykłych

Dziś przykład bardzo praktyczny - jak w arkuszu Excela dodać ułamki zwykłe?W jednej komórce jest np. 1/3, w drugiej - 1/2 - jak dodać je do siebie, aby w trzeciej komórce otrzymać wynik 5/6?
Nie da się zrobić tego bezpośrednio - ani w Excelu ani w kodzie VBA. Można jednak stworzyć sobie własną funkcję użytkownika , która będzie wykonywała takie obliczenia tak samo jak wszystkie standardowe funkcje Excela. Taką funkcję nazywamy funkcją UDF (User-Defined Function).

Przykład takiej funkcji to

Public Function SumaUlamkow(Ulamek1, Ulamek2)
'Autor marzatela http://mojezmaganiainformatyczne.blox.pl

Dim Mianownik1
Dim Mianownik2
Dim Licznik1
Dim Licznik2
Dim Mianownik
Dim Licznik
Dim i As Integer

Ulamek1 = Replace(Ulamek1, " ", "")
i = InStr(1, Ulamek1, "/")
Licznik1 = Left(Ulamek1, i - 1)
Mianownik1 = Right(Ulamek1, Len(Ulamek1) - i)
Ulamek2 = Replace(Ulamek2, " ", "")
i = InStr(1, Ulamek2, "/")
Licznik2 = Left(Ulamek2, i - 1)
Mianownik2 = Right(Ulamek2, Len(Ulamek2) - i)
Licznik1 = CInt(Licznik1)
Mianownik1 = CInt(Mianownik1)
Licznik2 = CInt(Licznik2)
Mianownik2 = CInt(Mianownik2)
Mianownik = WorksheetFunction.Lcm(Mianownik1, Mianownik2)
Licznik = Licznik1 * (Mianownik / Mianownik1) + Licznik2 * (Mianownik / Mianownik2)
i = WorksheetFunction.Gcd(Licznik, Mianownik)
If i > 1 Then
Licznik = Licznik / i
Mianownik = Mianownik / i
End If
SumaUlamkow = Licznik & "/" & Mianownik
End Function

SumaUlamkow jest funkcją dodająca 2 ułamki zwykłe - określone poprzez argumenty wejściowe Ulamek1 i Ulamek2. Wprawdzie typ danych nie jest tu określony, ale należy się spodziewać, że jest to typ tekstowy czyli String. Wynika to głównie z tego, że wpisując ciąg znaków np. 1/3 do komórki - po wyjściu z niej Excel automatycznie przekształci do w datę. Musimy więc zadeklarować to jako tekst czyli wpisać na początku apostrof - oznacza to że wpisujemy tekst. Szerzej pisałam o tym w notce:
Ułamek zwykły w komórce Excela
Można także wstawić w dowolnym miejscu znak spacji.

Na początku funkcji SumaUlamkow następuje przekształcenie tych zmiennych czyli wyodrębnienie licznika i mianownika ułamków będących argumentami funkcji oraz przekształcenie ich w liczby całkowite. Kolejny krok to oszukanie najmniejszej wspólnej wielokrotności mianowników, rozszerzenie ułamków oraz dodanie liczników. Na końcu jeszcze tylko sprawdzenie największego wspólnego podzielnika i ewentualne skrócenie ułamka sumy.

W samej funkcji użyłam funkcji VBA: Replace, InStr, Left, Right.

Efekt końcowy wygląda tak:

suma ułamków zwykłych

A każdego kto ma ochotę nauczyć się VBA zapraszam tu:

VBA w Excelu - pierwsze kroki

VBA w Excelu dla tych, którzy już coś wiedzą

Tagi: funkcje UDF

18:57, marzatela , VBA - przykładowe rozwiązania
Link Komentarze (5) »

niedziela, 17 czerwca 2012

Zamiana adresu względnego na bezwzględny

O adresie względnym i bezwzględnym pisałm juz kiedyś:
Adres względny i bezwzględny
Tym razem spróbuję odpowiedzieć na pytanie - jak zamienić domyślnie ustawiony adres względny na bezwzględny. Oczywiście pomijając ręczne wpisywanie znaku $ do formuły.
W pojedyńczych przypadkach wystarczy wejść w edycję formuły i ustawić się na oznaczeniu komórki:

adres względny i bezwzględny

Kolejny krok to naciśnięcie F4. I już.

adres względny i bezwzględny

Jeśli jednak w arkuszu formuły występują w wielu komórkach - będzie to pracochłonne.
W takim wypadku najlepszym wyjściem jest stworzenie procedury w VBA, która wykona to za nas. Najlepiej wykorzystać tu funkcję Application.ConvertFormula()

Na przykład tak:

Public Sub ZmienADRES
Dim Komorka As Range
Dim C

  For Each Komorka In Range("A2:C14")
                      'Obszar do zamiany formuł dostosowujemy do własnych potrzeb
    C = Left(Komorka.Formula, 1)
    If C = "=" Then
       Komorka.Formula = Application.ConvertFormula(Formula:=Komorka.Formula,    fromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1,ToAbsolute:=True)
   Next Komorka
       End If
End Sub

Oczywiście, przedstawiony tu przykład można modyfikować w zależności od potrzeb. Kod prosty i krótki. I działa, choć zdaje się, że dopiero od wersji Excel 2007 - nie jestem jednak pewna.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .