Excel i MySQL   strona główna:
A po co ten Excel ;-)
 
Tematyką MySQL zainteresowałem się dosłownie wczoraj. Wyszło jak zwykle z tematu na excelforum.pl + trochu mojej ciekawości  
co do wszelkich nowości (przynajmniej dla mnie nowości :-P). Bo, tak teoretycznie, skoro trochu kumam ADO i SQL to przecież  
jakoś pójdzie. No i jakoś poszło :-) Ciekawych połączenia Excela z MySQL zapraszam do lekturki.  
 
Function fConnString  
Function CzyJestTabelaADO  
Sub CloseConObject i Sub CloseRSObject  
Sub CreateTableSQL_ADO  
Sub DropTableSQL_ADO  
Sub InsertIntoTableSQL_ADO  
Sub SelectFromMySQL  
 
 
Na początek trzeba było zainstalować MySQL. Mi wystarczył zestaw...  
mysql-essential-5.0.51b-win32.msi   Download MySQL
mysql-gui-tools-5.0-r12-win32.msi  
mysql-connector-odbc-3.51.14-win32.msi   
 
Jak już instalacje mamy za sobą odpalamy Excela / Edytor VBA i... :-)  
Pierwszym problemem jest utworzenie "ciągu połączenia" ConnectionString. Pomocna będzie strona connectionstrings.com, z której   Connection strings for MySQL
można ściągnąć przykłady ciągów połączeń do dosłownie wszystkiego ;-) Nas teraz interesuje część poświęcona MySQL (link z prawej)  
Na liście odnajdujemy MySQL Connector/ODBC 3.51 a mnie teraz interesuje Local database  
więc przykład ConnectionString jakiego potrzebuję to:   
Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3;  
jeżeli jednak wasz MySQL jest np.: zainstalowany na serwerze gdzie będzie miało dostęp do niego szereg użyszkodników lub wymaga  
szeregu innych specyficznych ustawień ( o których natenczas nie mam zielonego pojęcia) wykorzystajcie inny przykład dostępny  
na ww stronie.  
    Ja tego ciągu będę używał do każdego polecenia więc napisałem funkcyjkę która go zwraca. Dzięki temu łatwiej również reagować  
na zmiany dot. np.: nazwy użytkownika, bazy... Nie muszę grzebać po każdej procedurze żeby coś zmienić. Wszystko jest w jednym  
miejscu.  
 
Public Function fConnString() As String  
    'http://www.connectionstrings.com/mysql  
    'MySQL Connector/ODBC 3.51  
    'Local database  
 
    Const strSerwerName As String = "localhost"  
    Const strDatabase As String = "MySQL"  
    Const strUserName As String = "root"  
    Const strPass As String = "haslo123"  
      
    fConnString = "Driver={MySQL ODBC 3.51 Driver};" & _  
                  "Server=" & strSerwerName & ";" & _  
                  "Database= " & strDatabase & ";" & _  
                  "User=" & strUserName & ";" & _  
                  "Password=" & strPass & ";" & _  
                  "Option=3;"  
End Function  
 
root to domyślna nazwa użytkownika. Innego na razie nie zakładałem. (temat na aktualizacje :-P)  
 
 
    Kolejna funkcja która wydaje mi się potrzebna to CzyJestTabelaADO. Ma ona za zadanie sprawdzić czy tabela o nazwie wskaza-  
nej w arg. strTblName jest już utworzona w bazie danych.  
 
Public Function CzyJestTabelaADO(objConnection As Object, _  
                                 strTblName As String) As Boolean  
    On Error GoTo CzyJestTabelaADO_Error  
 
    Const adSchemaTables = 20  
    Dim objRecordset As Object  
 
    Set objRecordset = objConnection.OpenSchema(adSchemaTables)  
    With objRecordset  
        Do Until .EOF  
            If UCase(.Fields("TABLE_TYPE").Value) = "TABLE" Then  
                If UCase(.Fields("TABLE_NAME").Value) = UCase(strTblName) Then  
                    CzyJestTabelaADO = True  
                    Exit Do  
                End If  
            End If  
            .MoveNext  
        Loop  
    End With  
 
CzyJestTabelaADO_Exit:  
    On Error Resume Next  
    CloseRSObject objRecordset  
    Exit Function  
 
CzyJestTabelaADO_Error:  
    MsgBox "Błąd Nr - " & Err.Number & vbCrLf & vbCrLf & _  
           Err.Description, vbExclamation, "VBAProject - CzyJesTabADO"  
    Resume CzyJestTabelaADO_Exit  
 
End Function  
 
    Ww funkcja jak i wszystkie procedury wykonujące zadania na bazie danych będą w w ramach obsługi błędów zamykać i usuwać  
tworzone obiekty ADODB.Connection i ADODB.Recordset. Procedury te to...  
 
Public Sub CloseConObject(Cnn As Object)  
    If Not (Cnn Is Nothing) Then  
        If Cnn.State = adStateOpen Then Cnn.Close  
        Set Cnn = Nothing  
    End If  
End Sub  
 
Public Sub CloseRSObject(Rs As Object)  
    If Not (Rs Is Nothing) Then  
        With Rs  
            If CBool(.State And adStateOpen) Then  
                If .EditMode <> adEditNone Then .CancelUpdate  
                .Close  
            End If  
        End With  
        Set Rs = Nothing  
    End If  
End Sub  
 
    Brakuje nam jeszcze deklaracji stałych zdefiniowanych w bibliotece ADO używanych w procedurach. Mało która procedura będzie  
potrzebowała jakichś specyficznych stałych dlatego wszystkie wykorzystywane stałe zadeklarujemy na poziomie modułu (część de-  
klaracji modułu) żeby były dostępne dla wszystkich procedur.  
    Ważnym jest żeby deklaracja tych stałych była w części deklaracji modułu - na samej górze, powyżej funkcji i procedur. Żeby to   
zapewnić proponowałbym na wszystko co powyżej utworzyć oddzielny moduł standardowy.  
 
    W prezentowanym przykładzie wykorzystuję następujące stałe:  
 
Option Explicit  
Const adUseClient = 3  
Const adModeRead = 1  
Const adModeWrite = 2  
Const adCmdText = 1  
Const adExecuteNoRecords = 128  
Const adStateOpen = 1  
'------------------DataTypeEnum Values--------------------  
'http://www.w3schools.com/ado/met_comm_createparameter.asp  
Const adVarChar = 200  
Const adInteger = 3  
'---------------------------------------------------------  
Const adParamInput = 1  
Const adEditNone = 0  
 
Wydzielona część stałych dotyczy typów danych. Stałe dla wszystkich typów danych i ich wartości są dostępne na ww stronie.  
 
    Stwórzmy więc tabelę w naszej bazie za pomocą SQL wykorzystując obiekty ADO.  
 
Sub CreateTableSQL_ADO()  
    On Error GoTo CreateTableSQL_ADO_Error  
 
    Dim objConnection As Object 'ADODB.Connection  
    Dim strSQL As String  
    Dim bErr As Boolean  
      
    Const strTblName As String = "tblTabela"  
      
    Set objConnection = CreateObject("ADODB.Connection")  
    With objConnection  
        .CursorLocation = adUseClient  
        .Open fConnString  
    End With  
      
    strSQL = "CREATE TABLE " & strTblName & " " & _   CREATE TABLE examples
                "(" & _  
                   "ID INTEGER, " & _  
                   "pole1 VARCHAR(10), " & _  
                   "pole2 VARCHAR(10)" & _  
                ");"  
                  
    If Not CzyJestTabelaADO(objConnection, strTblName) Then  
        objConnection.Execute CommandText:=strSQL, _  
                              Options:=adExecuteNoRecords  
    End If  
            
    MsgBox "Tabela Utworzona :-)", vbInformation  
            
CreateTableSQL_ADO_Exit:  
    On Error Resume Next  
    CloseConObject objConnection  
    Exit Sub  
 
CreateTableSQL_ADO_Error:  
    MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & _  
            Err.Description, vbExclamation, "VBAProject - ExeSQLCom"  
    Resume CreateTableSQL_ADO_Exit  
 
End Sub  
 
Metoda Execute obiektu ADODB.Connection posiada arg.Options stałą adExecuteNoRecords. Przekazujemy nim informację że zadaniem  
procedury nie jest utworzenie Recordsetu (zestawy danych wynikowych). Ma to za zadanie zoptymalizować czas wykonania zadania.  
 
    To teraz ją usuńmy :-)  
 
Sub DropTableSQL_ADO()  
    On Error GoTo DropTableSQL_ADO_Error  
 
    Dim objConnection As Object 'ADODB.Connection  
    Dim strSQL As String  
      
    Const strTblName As String = "tblTabela"  
      
    Set objConnection = CreateObject("ADODB.Connection")  
    With objConnection  
        .CursorLocation = adUseClient  
        .Open fConnString  
    End With  
      
    strSQL = "DROP TABLE " & strTblName & ";"  
      
    If CzyJestTabelaADO(objConnection, strTblName) Then  
        objConnection.Execute CommandText:=strSQL, _  
                              Options:=adExecuteNoRecords  
    End If  
      
    MsgBox "Tabela USUNIĘTA :-)", vbInformation  
      
DropTableSQL_ADO_Exit:  
    On Error Resume Next  
    CloseConObject objConnection  
    Exit Sub  
 
DropTableSQL_ADO_Error:  
    MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & _  
            Err.Description, vbExclamation, "VBAProject - ExeSQLCom"  
    Resume DropTableSQL_ADO_Exit  
End Sub  
 
    Te procedury były zasadniczo dość proste. Kolejna tj wypełniająca tabelę będzie trudniejsza.  
 
Sub InsertIntoTableSQL_ADO()  
    On Error GoTo InsertIntoTableSQL_ADO_Error  
 
    Dim objConnection As Object 'ADODB.Connection  
    Dim objCommand As Object 'ADODB.Command  
    Dim bTrans As Boolean  
    Dim strSQL_CreateTable As String  
    Dim strSQL As String  
    Dim i As Long, tblParam(0 To 2) As Variant  
      
    Const strTblName As String = "tblTabela"  
      
    Set objConnection = CreateObject("ADODB.Connection")  
    With objConnection  
        .CursorLocation = adUseClient  
        .Mode = adModeWrite  
        .Open fConnString  
        .BeginTrans  
        bTrans = True  
    End With  
      
    If Not CzyJestTabelaADO(objConnection, strTblName) Then  
        strSQL_CreateTable = "CREATE TABLE " & strTblName & " " & _  
                                "(" & _  
                                   "ID INTEGER, " & _  
                                   "pole1 VARCHAR(10), " & _  
                                   "pole2 VARCHAR(10)" & _  
                                ");"  
        objConnection.Execute CommandText:=strSQL, _  
                              Options:=adExecuteNoRecords  
    End If  
      
    Set objCommand = CreateObject("ADODB.Command")  
    With objCommand  
        .ActiveConnection = objConnection  
        .CommandType = adCmdText  
          
        .CommandText = "INSERT INTO `" & strTblName & "` (`ID`, `pole1`, `pole2`) " & _  
                       "VALUES (?,?,?);"  
        .Prepared = True  
        .Parameters.Append .CreateParameter(Name:="F1", _  
                                            Type:=adInteger, _  
                                            Direction:=adParamInput)  
        .Parameters.Append .CreateParameter(Name:="F2", _  
                                            Type:=adVarChar, _  
                                            Direction:=adParamInput, _  
                                            Size:=10)  
        .Parameters.Append .CreateParameter(Name:="F3", _  
                                            Type:=adVarChar, _  
                                            Direction:=adParamInput, _  
                                            Size:=10)  
 
        For i = 1 To 15  
            tblParam(0) = i  
            tblParam(1) = "test" & i  
            tblParam(2) = "test1" & Format(i, "00")  
              
            .Execute Parameters:=tblParam, Options:=adExecuteNoRecords  
        Next  
 
    End With  
      
    MsgBox "INSERT INTO przeszło :-)", vbInformation  
      
    objConnection.CommitTrans  
    bTrans = False  
 
InsertIntoTableSQL_ADO_Exit:  
    On Error Resume Next  
    Set objCommand = Nothing  
    CloseConObject objConnection  
    Exit Sub  
 
InsertIntoTableSQL_ADO_Error:  
    If bTrans Then  
        objConnection.RollbackTrans  
    End If  
    MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & _  
            Err.Description, vbExclamation, "VBAProject - InsIntMDBADO"  
    Resume InsertIntoTableSQL_ADO_Exit  
 
End Sub  
 
    Zadanie polega na przekazaniu do tabeli strTblName zestawu danych. Wykorzystamy do tego klauzulę INSERT INTO jednak trzeba  
będzie powtórzyć ją w pętli określoną ilość razy.  
Zadanie to wymaga parametryzacji zapytania i przekazywania kolejnych zestawów danych zapisanych do tablicy tblParam przekazy-  
wanej do metody Execute obiektu ADODB.Command w arg. Parameters. I jeżeli, ktoś słusznie by zauważył, że poprzednie procedury  
można by sprowadzić do jednego narzędzia do którego przekazywalibyśmy tylko ConnectionString i Zapytanie SQL to w tym przypadku  
nie widzę możliwości takiej automatyzacji. Każde zapytanie może mieć inną ilość parametrów o różnych typach danych. Dlatego cała  
procedura jest zupełnie nie uniwersalna. Na razie innego wyjścia nie znajduję ale, jak starczy chęci i czasu, poeksperymentuję próby  
pominięcia parametryzacji i oparcia się tylko o obiekt ADODB.Connection. Temat uważam za otwarty :-)  
    Ważnym jest też żeby cała procedura odbywała się w "ramach Transakcji". Powodem jest możliwość wystąpienia nieoczekiwanego  
błędu który przerwie działanie procedury. Obsługa błędów oczywiści zamknie i usunie co trzeba - baza będzie względnie bezpieczna,   
ale jak sprawdzić jaka część roboty się wykonała a jaka nie :-|. I po to właśnie nam Transakcje. W przypadku błędu wszystko zostanie  
"odkręcone" :-) Nie zostanie utworzona tabela jeżeli jej nie było i nie zostanie przekazany żaden zestaw danych - co likwiduje problem.  
 
    No i wyciągnięcie danych z bazy  
 
Tu wykorzystam gotowe narzędzie, które już prezentowałem w innym przykładzie który dotyczył zapytań na plikach XLS. Tu jest   
równie pomocne i uniwersalne :-) Chodzi o procedurę ExecuteSQLCommand_ADO  do której przekazuję ConnectionString - zwracany  
funkcją fConnString :-), strSQL - zapytanie oparte o klauzulę SELECT, i komórkę do której mają być zwracane dane.   
 
Sub SelectFromMySQL()  
    Dim strSQL As String  
    Dim wks As Excel.Worksheet  
      
    Const strTblName As String = "tblTabela"  
      
    strSQL = "SELECT * " & _  
             "FROM " & strTblName & " " & _  
             "WHERE ID > 5;"  
      
    Set wks = ThisWorkbook.Worksheets("Arkusz1")  
      
    ExecuteSQLCommand_ADO fConnString, strSQL, wks.[A1]  
      
    Set wks = Nothing  
End Sub  
   
 Private Sub ExecuteSQLCommand_ADO(strConnectionString As String, _  
                                   strSQL As String, _  
                                   rngKomCel As Excel.Range)  
     On Error GoTo ExecuteSQLCommand_ADO_Error  
     Dim objConnection As Object, objRecordset As Object  
          
     Set objConnection = CreateObject("ADODB.Connection")  
     With objConnection  
         .CursorLocation = adUseClient  
         .Mode = adModeRead  
         .Open strConnectionString  
         Set objRecordset = .Execute(strSQL, , adCmdText)  
         With objRecordset  
             If Not (.BOF And .EOF) Then rngKomCel.CopyFromRecordset objRecordset  
         End With  
     End With  
       
ExecuteSQLCommand_ADO_Exit:  
     On Error Resume Next  
     CloseRSObject objRecordset  
     CloseConObject objConnection  
     Exit Sub  
       
ExecuteSQLCommand_ADO_Error:  
     MsgBox "Byk nr: - " & Err.Number & vbCrLf & vbCrLf & _  
             Err.Description, vbExclamation, "VBAProject - ExeSQLComADO"  
     Resume ExecuteSQLCommand_ADO_Exit  
       
 End Sub  
 
Na razie tyle w temacie ale na pewno jeszcze do tego siądę, a o efektach na pewno kilka słów napiszę. :-))  
 
Pozdrawiam tych co dobrnęli do tego fragmentu - Brawo!! W Was nadzieja ;-))