Akademia VBA

Czyli jak uzuskać władzę nad światem … danych

Wysyłanie wiadomosci e-mail z VBA

Kategorie: Office VBA,Uniwersalne VBA — Darek Kolasa o 14:54, 16 Mar 2017

Collaboration Data Objects (CDO) to biblioteka umożliwiająca wysyłanie wiadomosci e-mail. Była dedykowana dla Windows 2000 ale działa do dziś 🙂

Kod oczywiście wymaga poprawek w zakresie adresów e-mail jak i danych serwera wysyłającego pocztę. Niektóre serwery (np Gmail) mogą wymagać dodatkowych ustawień dla użycia przez CDO

'Wymaga biblioteki Microsoft CDO for Windows 2000 Library
 
Sub WyslijMail_CDO()
    Dim Wiadomosc As CDO.Message
    Dim Konfiguracja As CDO.Configuration
 
    Set Wiadomosc = New CDO.Message
    Set Konfiguracja = New CDO.Configuration
 
    Const ADRES_KONFIGURACJI As String = "http://schemas.microsoft.com/cdo/configuration/"
 
    Konfiguracja.Load cdoDefaults
 
    With Konfiguracja.Fields
        .Item(ADRES_KONFIGURACJI & "sendusername") = "login"          'login
        .Item(ADRES_KONFIGURACJI & "sendpassword") = "hasło"          'hasło
        .Item(ADRES_KONFIGURACJI & "smtpserver") = "poczta.o2.pl"     'serwer SMTP
        .Item(ADRES_KONFIGURACJI & "smtpserverport") = 465            'port
        .Item(ADRES_KONFIGURACJI & "sendusing") = cdoSendUsingPort    'metoda wysyłania
        .Item(ADRES_KONFIGURACJI & "smtpauthenticate") = cdoBasic     'metoda autentykacji
        .Item(ADRES_KONFIGURACJI & "smtpusessl") = 1                  'kodowany kanał
        .Update
    End With
 
    Set Wiadomosc.Configuration = Konfiguracja
 
    Wiadomosc.From = "adres1@o2.pl"
    Wiadomosc.To = "adres2@gmail.com"
    Wiadomosc.Subject = "Test CDO"
    Wiadomosc.TextBody = "Hej" & vbNewLine & "To jest test biblioteki CDO" & _
        vbNewLine & vbNewLine & "Pozdrawiam" & vbNewLine & "Darek"
 
    Wiadomosc.Send
 
End Sub

Przyszłość VBA

Kategorie: Office 2013,Office VBA,Ogólne,Problemy z Office — Darek Kolasa o 18:57, 11 Lut 2015

To ważny temat dla autora tej strony 🙂
Oto kilka cytatów:

1. Dick Kusleika

12 krotny zdobywca tytułu MVP, znawca Office System, SharePoint Server i .NET:

I don’t care if VBA is dead. It still works for me now, I’m very effective with it, and I’m still solving real problems using it every day. If it’s dead, it’s the best damn corpse in the office.

Fajne 🙂

2. Jacques Bourgeois (James Burger)

Ma oficjalny tytuł Geniusza na experts-exchange.com (ok 2,5 miliona punktów od użytkowników)

Enterprises love Word and Excel because they can push them over what they can do through VBA or VB.NET applications. And how does Microsoft react to that love. By toning down Office with these stupid ribbons in 2007, and then removing VBA in their cloud incarnations.

With my 40 years in programming (first as a hobbyist, then as an engineer, and the last 23 as a full time programmer) I have a little bit of experience. And as many programmers of my age, I have the impression that
we are going back to the 80’s, where most of the programmer’s time is spent trying to make the interface work correctly

Przygnębiające ale prawdziwe 🙁

Podsumowując:

Największym wrogiem VBA jest … Microsoft.
VBA nie jest rozwijane praktycznie od ponad 20 lat (może to i lepiej, patrząc na jakość dzisiejszych produktów). Microsoft Office jest coraz wolniejszy, niestabilny, nieergonomiczny. Buja w chmurach i nie obsługuje w nich VBA
Miejmy nadzieję, że ktoś w Microsoft się ocknie i zrozumie, że nie wszystko musi służyć do zabawy na tablecie…

Nawiasem mówiąc najpoważniejszym wrogiem VBA było VSTO (Visual Studio Tools for Office). Microsoft od wersji 2012 już go nie wspiera…
Współczuję tym, którzy zainwestowali 10 lat pracy w ten produkt…

Obecnie w dobie Office 365 największym zagrożeniem dla VBA jest …. HTML, JavaScript i CSS!. To nie żart. Takie są najnowsze plany Microsoftu!

Ja póki co, nadal trzymam się VBA 😉

Źródła:
http://dailydoseofexcel.com/archives/2014/11/08/the-future-of-vba-
development/)
http://www.experts-
exchange.com/Programming/Microsoft_Development/Q_28328440.html

ADO – i wszystkie bazy są nasze :)

Kategorie: Access VBA,Excel VBA,Office VBA,SQL Server — Darek Kolasa o 14:36, 23 Gru 2014

Od 2000 roku czyli już od kilkunastu lat Microsoft Office może bez problemu łączyć się w zasadzie z dowolnymi bazami danych, dzięki bibliotece ADO (Microsoft ActiveX Data Object XX Library). I od tych samych kilkunastu lat budzi to niezmiennie zdziwienie wśród moich klientów i studentów. Jak to? Excel może pobrać dane z Oracle?, SQL Servera? O! Może nawet je edytować?! No oczywiście, że może, i to od wielu lat…
Biblioteka ADO (w kodzie widoczna jako ADODB) jest domyślnie podłączana tylko w Accessie. We wszystkich innych produktach, w tym w Excelu trzeba ją wybrać w referencjach. Tools – References – Microsoft ActiveX Data Object XX Library, gdzie XX to numer wersji biblioteki, ja osobiście wybieram zwykle wersję 2.8
Drugi warunek to oczywiście poprawnie zainstalowany sterownik OLEDB do określonej bazy (http://en.wikipedia.org/wiki/OLE_DB_provider)

Poniżej przykład pobierający do Excela dane z Accessa 2003, ale uważny czytelnik zobaczy drogę pobierania z Accessa 2007/10/13 tudzież z SQL Servera. Wystarczy podmienić wartość właściwości ConnectionString obiektu Connection

Option Explicit
Public Const WERSJA As String = "ADO Klient v.0.1"
Public Const ARK_DANE As String = "Dane"
Public Const ZRODLO As String = "tbBrutto"
 
'Załozenia:
' - mamy arkusz o nazwie "Dane"
' - mamy bazę PodstawyVBA.mdb a w niej tabelę tbBrutto
' - mamy sterownik do mdb
 
'Access mdb
'==========
Public Const CN_STR As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=D:\kursy\AC04\PodstawyVBA.mdb;" & _
    "Persist Security Info=False"
 
 
'Ponizej przykłady konfiguracji dla innych baz
 
'Access accdb
'============
'Public Const CN_STR As String = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=D:\kursy\AC04\PodstawyVBA.accdb;" & _
    "Persist Security Info=False"
 
'SQL Server
'==========
'Public Const CN_STR As String = _
    "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=Produkcyjna;" & _
    "Data Source=SKARBEKMC\SQLEXPRESS"
 
 
Sub OdczytDoExcela()
    Dim Zakres As Range
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
 
    On Error GoTo Obsluga
 
    Sheets(ARK_DANE).Select
    Range("A1").Select
    Set Zakres = _
        Sheets(ARK_DANE).Range("A1").CurrentRegion
    Zakres.ClearContents
 
    cn.ConnectionString = CN_STR
    cn.Open
    rs.Open ZRODLO, cn
    Sheets(ARK_DANE).Range("A2").CopyFromRecordset rs
 
    'nagłówki
    Dim LK As Long, K As Long
    LK = rs.Fields.Count
    For K = 1 To LK
        Sheets(ARK_DANE).Cells(1, K) = rs.Fields(K - 1).Name
    Next
 
    Zakres.EntireColumn.AutoFit
 
Czyszczenie:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    Exit Sub
Obsluga:
    MsgBox Err.Number & " - " & Err.Description, vbExclamation, WERSJA
    Resume Czyszczenie
End Sub

Sprawdzenie dostępności pliku

Kategorie: Access VBA,Excel VBA,Office VBA,Ogólne,Podstawy VBA — Dariusz Kolasa o 1:11, 19 Lip 2012

Czasem chcielibyśmy wiedzieć, czy plik na którym chcemy wykonać jakąś operację, jest dla nas dostępny. Np gdy jest otwarty przez innego użytkownika próba jego użycia może powodować błąd. Ponadto wcześniej należy sprawdzić czy dany plik w ogóle istnieje. Można to sprawdzić przy pomocy poniższych funkcji:

Sub TestFunkcji()
    Const NAZWA_PLIKU As String = "C:\EX04\Baza.xls"
    If BrakPliku(NAZWA_PLIKU) Then Exit Sub
    If PlikNiedostepny(NAZWA_PLIKU) Then Exit Sub
    MsgBox "Plik jest dostępny, można coś z nim robić ;)", vbInformation
End Sub
 
Function PlikNiedostepny(NazwaPliku As String) As Boolean
    Dim NrPliku As Long
 
    On Error GoTo Obsluga
 
    NrPliku = FreeFile()
    Open NazwaPliku For Binary Access Read Write Lock Read Write As #NrPliku
    Close #NrPliku
    Exit Function
Obsluga:
    Select Case Err
    Case 70
        MsgBox "Plik jest obecnie otwarty przez inny proces", vbExclamation
    Case Else
        MsgBox "Przy próbie otwarcia pliku zgłaszany jest błąd: " _
            & Err & " - " & Err.Description, vbCritical
    End Select
    PlikNiedostepny = True
End Function
 
Function BrakPliku(PelnaNazwaPliku As String) As Boolean
    If Dir(PelnaNazwaPliku) = "" Then
        MsgBox "Brak pliku", vbCritical, WERSJA
        BrakPliku = True
    End If
End Function

Deklaracja zmiennych

Kategorie: Office VBA,Ogólne,Podstawy VBA — Dariusz Kolasa o 16:23, 6 Lip 2012

Dla początkujących użytkowników VBA nieodmiennie zagadką jest deklarowanie zmiennych. Po pierwsze w ogóle nie wiadomo po co to pisać, skoro bez tego i tak działa, a po drugie nazwy typów zmiennych nie brzmią zbyt przyjaźnie.
Niestety brak stosowania i rozumienia deklaracji jest głównym powodem błędów, literówek, spowolnienia działania kodu i niewygody jego pisania. Temat nie jest bardzo prosty, ale bardzo ważny. Tu krótko wymienię zalety rozumienia i stosowania właściwych deklaracji:

  • Kod działa nawet do 4 razy szybciej
  • Łatwiej pisać kod bo zmienne zadeklarowane są wspomagane przez technologię podpowiadania słów kluczowych – Intellisense
  • Unikamy literówek
  • Piszemy kod wysokiej jakości pozwalający na szybsze wychwytywanie błędów – np niezgodność typu co przy przeoczeniu może prowadzić do negatywnych konsekwencji
  • Poprawiamy czytelność kodu

Problem polega na tym, że ta wiedza przychodzi z czasem, a tego jak zwykle wszystkim brakuje …

Funkcja do liczenia stażu w latach – PelneLata

Kategorie: Excel VBA,Office VBA — Dariusz Kolasa o 20:48, 28 Mar 2012

Generalnie robi to samo co excelowa DATA.RÓŻNICA ale jest łatwiejsza w użyciu, bo nie trzeba podawać interwału, no i podpowiada nazwy argumentów

Function PelneLata(DataPocz As Date, DataKon As Date) As Long
   '----------------------------------------------
    ' Mam nadzieję, że robi to samo co
    ' DATA.RÓŻNICA ale za to podpowiada argumenty :)
    '----------------------------------------------
    Dim RokPocz As Long, MiesPocz As Long, DzienPocz As Long
    Dim RokKon As Long, MiesKon As Long, DzienKon As Long
 
    If DataKon <= DataPocz Then
        PelneLata = 0
        Exit Function
    End If
 
    RokPocz = Year(DataPocz)
    RokKon = Year(DataKon)
    MiesPocz = Month(DataPocz)
    MiesKon = Month(DataKon)
    DzienPocz = Day(DataPocz)
    DzienKon = Day(DataKon)
 
    PelneLata = RokKon - RokPocz
    If MiesPocz > MiesKon Then
        PelneLata = PelneLata - 1
        Exit Function
    End If
    If MiesKon = MiesPocz Then
        If DzienPocz > DzienKon Then PelneLata = PelneLata - 1
    End If
End Function

Liczba dni w miesiącu

Kategorie: Edytor VBA,Office VBA,Uniwersalne VBA — Dariusz Kolasa o 14:28, 6 Mar 2012

Mała rzecz a cieszy 🙂 Jeszcze jedno zastosowanie bezcennej funkcji DateSerial

Function LiczbaDniMiesiaca(Rok As Long, Miesiac As Long) As Long
    LiczbaDniMiesiaca = Day(DateSerial(Rok, Miesiac + 1, 0))
End Function

Nazwa pliku z pełnej ścieżki

Kategorie: Excel VBA,Office VBA,Uniwersalne VBA — Dariusz Kolasa o 14:35, 6 Lis 2011

Czasem potrzebujemy samej nazwy pliku a mamy w zmiennej pełną ścieżkę. Oczywiście sprawa jest prosta ale lepiej mieć pod ręką stosowną funkcję:

Function NazwaPlikuZeSciezki(Sciezka As String) As String
    Dim OstatniUkosnik As Long
    OstatniUkosnik = InStrRev(Sciezka, "\")
    NazwaPlikuZeSciezki = Mid(Sciezka, OstatniUkosnik + 1)
End Function

Jak z VBA wywołać okno dialogowe do pobrania nazwy pliku od użytkownika

Kategorie: Excel VBA,Office VBA,Uniwersalne VBA — Dariusz Kolasa o 12:39, 21 Lip 2011

Pod Excelem można to zrobić na dwa sposoby:
Korzystając z biblioteki Office (funkcja bardzo podobna do WskazFolder):

Function WskazPlik(TytulOkna As String, TytulPrzycisku As String) As String
    Dim Okno As FileDialog
    Dim Wybrane As String
    Set Okno = Application.FileDialog(msoFileDialogFilePicker)
    Okno.Title = TytulOkna
    Okno.ButtonName = TytulPrzycisku
    If Okno.Show = -1 Then
        WskazPlik = Okno.SelectedItems(1)
    End If
End Function

Lub korzystając z metody Excela:

Function WskazPlik_MetodaExcel() As String
    WskazPlik_MetodaExcel = Excel.Application.GetOpenFilename()
End Function

Pierwsza funkcja jest nieco bardziej skomplikowana ale bardziej uniwersalna, druga jest skrajnie prosta i nie wymaga niczego poza Excelem. Oczywiście obie można rozbudować o możliwość filtrowania plików…

W komentarzach znajduje się dyskusja na temat konstrukcji wiążącej With..End With

Zabezpieczenia makr, poziomy zabezpieczeń

Kategorie: Excel VBA,Office VBA,Ogólne,Podstawy VBA — Dariusz Kolasa o 15:58, 4 Lip 2010

Na szczęście makrowirusy nie są ostatnio bardzo popularne, ale proszę zawsze uważać, lepiej dmuchać na zimne. Poziom zabezpieczeń ustawiamy z menu – Narzędzia – Makro – Zabezpieczenia.

Nie wolno ustawiać niskiego poziomu zabezpieczeń, ponieważ wtedy każdy uruchomiony plik zawierający makro, będzie mógł je bez pytania uruchomić. W większości przypadków należy użyć średniego poziomu zabezpieczeń, który gwarantuje zadanie pytania czy włączyć makra, przy uruchamianiu pliku. Proszę się dobrze zastanowić przed odpowiedzią na to pytanie!

Nigdy nie należy włączać makr w obcym nieznanym pliku!

Zabezpieczenia wysokie i bardzo wysokie stosujemy tylko gdy mamy wykupiony certyfikat bezpieczeństwa do projektu VBA co się raczej rzadko zdarza

Wcześniejsze wpisy »