Differences

This shows you the differences between two versions of the page.

--- vba:excel:adodbsql [09.10.2015 12:52:55]
yaslaw
+++ vba:excel:adodbsql [06.05.2019 11:08:01] (current)
yaslaw
@@ Line 1: / Line 1: @@
+<const>
+    version=1.6.0
+    vdate=22.01.2018
+    fname=lib_adodb_for_xls.bas
+    ns=%NAMESPACE%
+    fpath=/vba/excel
+</const>
 ====== [Excel][VBA][SQL] SQL in Excel einsetzen ======
+Datenverarbeitung in Excel mittels SQL.
+==Version %%version%% - %%vdate%%==
+{{%%fname%%|Download %%fname%% (V-%%version%%)}}
 Excel ist nett, aber auch unübersichtlich. Vor allem wenn man mit Datenmengen und VBA arbeiten will.
-Um Daten anzureichern, in grösseren Mengen zu verarbeiten etc. sehe ich immer wieder Script die Zellenadressen zusammensetzen und dadurch unlesbar sin. Wer weiss beim lesen schon, was die A4 ist?
+Um Daten anzureichern, in grösseren Mengen zu verarbeiten etc. sehe ich immer wieder Script die Zellenadressen zusammensetzen und dadurch unlesbar sind. Wer weiss beim lesen schon, was die A4 ist?
-Nungut, ich komme von der Datenbankseite. DOrt arbeite ich mit SQL um die Datan abzufragen, zusammenzusetzen etc.. Zum Glück für mich, ist das alles auch in Excel VBA möglich.
+Nungut, ich komme von der Datenbankseite. Dort arbeite ich mit SQL um die Datan abzufragen, zusammenzusetzen etc.. Zum Glück für mich, ist das alles auch in Excel VBA möglich.
 ADODB hiesst das Losungswort.
 \\ Ich erkläre hier nicht alle Details. Das WWW gibt gut auskunft. Nein, ich zeige wie ich es anwende
+Den Code zu den Funktionen gibts am Schluss
+===== Definitionen =====
+==== axConnParams ====
+Sammlung von steuerparametern. Es werden, ev. in Zukunft noch mehr.
+<code vb>Public Enum axConnParams
+    axcNone = 0
+    axcReconnect = 2 ^ 0    'Ein Reconnect wird erzwungen
+    axcNoHeader = 2 ^ 1     'Die erste Zeile ist keine Kopfzeile. Die Felder werden mit f1, f2...fx angesprochen
+End Enum</code>
+==== axWriteParams ====
+Paramters zum Schreiben der Daten
+<code vb>'/**
+Public Enum axWriteParams
+    axwNone = 2 ^ 0             'Keine umwandlung
+    axwHeaderRedable = 2 ^ 1    'Titel mit Unterlinien werden aufgetrennt "HAUS_NUMMER" -> "Haus Nummer"
+End Enum</code>
 ===== Initialisieren =====
 ==== Property connection ====
@@ Line 12: / Line 45: @@
 <code vb>'/**
 ' * Handle dhe adodb-connection to the current workbook
-' * @param  Boolean	FLag, ob die Connectionneu aufgebaut werden soll
+' * @param  axConnParams    Paramters für die Connection
+' * @param  String          Pfad zur QuellDatei. Standard ist der Pfad des ausführenden Workbooks
 ' * @return ADODB.Conection
 ' */
-Private Property Get connection(ByVal iReconnect As boolean = False) As Object
+Private Property Get connection(Optional ByVal iConnParams As axConnParams = axcNone, Optional ByVal iFilePath As String = Empty) As Object</code>
-    Static pConn As Object
-    If pConn Is Nothing Or iReconnect Then
-        Set pConn = CreateObject("ADODB.Connection")
-        pConn.Provider = "Microsoft.Jet.OLEDB.4.0"
-        pConn.ConnectionString = "Data Source='" & ThisWorkbook.FullName & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
-    End If
-    If Not (pConn.State And adStateOpen) = adStateOpen Then
-         pConn.Open
-    End If
-    Set connection = pConn
-End Property</code>
 ==== Function openRs() ====
@@ Line 32: / Line 55: @@
 <code vb>'/**
 ' * Open a adodb recordset from the current workbook
-' * @param  String      SQL-String
+' * @param  String          SQL-String
-' * @param  Boolean	FLag, ob die Connectionneu aufgebaut werden soll
+' * @param  axConnParams    Paramters für die Connection
 ' * @return ADODB.Recordset
 ' */
-Public Function openRs(ByVal iSql As String, ByVal iReconnect As boolean = False) As Object
+Public Function openRs(ByVal iSql As String, Optional ByVal iConnParams As axConnParams = axcNone) As Object</code>
-    Dim rst As Object: Set rst = CreateObject("ADODB.Recordset")
-    Dim cmd As Object: Set cmd = CreateObject("ADODB.Command")
-    Set cmd.ActiveConnection = connection(iReconnect)
-    cmd.CommandType = adCmdText
-    cmd.CommandText = iSql
-    rst.CursorLocation = adUseClient
-    rst.CursorType = adOpenDynamic
-    rst.LockType = adLockOptimistic
-    'open the connection
-    rst.Open cmd
-    'disconnect the recordset
+==== Function writeHeader() ====
-    Set rst.ActiveConnection = Nothing
+Schreibt den Header in eine Zeile
+<code vb>'/**
+' * Write the Header of a adodb.recordset
+' * @param  Worksheet/Range/Address
+' * @param  Recordset
+' * @param  axWriteParams
+' */
+Public Sub writeHeader(ByRef iRange As Variant, ByRef ioRs As Object, Optional ByVal iWriteParams As axWriteParams = axwNone)
+</code>
-    'cleanup
+==== Function writeFullData() ====
-    If cmd.State = adStateOpen Then
+Mit dem einfachen Worksheet.CopyFromRecordset() werden nur die Daten geschrieben. Die folgende Funktion schreibt mittels writeHeader() zuerst die Kopfzeile und dann die Daten
-        Set cmd = Nothing
-    End If
+<code vb>'/**
-    Set openRs = rst
+' * Schreibt ins Excel inkl Header
-End Function</code>
+' * @param  Worksheet/Range/Address
+' * @param  Recordset
+' * @param  axWriteParams
+' */
+Public Sub writeFullData(ByRef iRange As Variant, ByRef ioRs As Object, Optional ByVal iWriteParams As axWriteParams = axwNone)
+</code>
 ===== Anwenden von openRs() =====
-ADoch wie greift man mittels SQL jetzt auf die Daten zu?
+Doch wie greift man mittels SQL jetzt auf die Daten zu?
 Es gibt mehrere Varianten um die Daten jetzt als Source in einem SQL anzusprechen
 ==== Quellen ====
 === Ganzes Worksheet als Tabelle ===
-Häufig habe ich von Exports aus anderen Systemen gleich ein Worksheet, das genau einer Tabelle entspricht. Die erste Zeile hat die Titel, dei folgenden Zeilen sind die Daten.
+Häufig habe ich von Exports aus anderen Systemen gleich ein Worksheet, das genau einer Tabelle entspricht. Die erste Zeile hat die Titel, die folgenden Zeilen sind die Daten.
 Ich kann mit dem SQL gleich auf diese Daten zugriefen. Die Titel sind dann auch im SQL die Namen der Spalten.
@@ Line 112: / Line 134: @@
   * Feld-Nr: ''F#''
+==== Beispiel ====
+Ich lese die Daten aus dem Sheet ADDRESS und schreibe eine sie in das Sheet TARGET
+> Meine Library braucht zwar keine Referenz. Im Beispiel habe ich jedoch den Recordset mit Early Binding gemacht und muss darum eine Referenz auf ''Microsoft ActiveX Data Objects 6.1 Library'' setzen. Ansonsten den rs als Object definieren
+<code vb>Public Sub sqlTest()
+    Dim rs As ADODB.Recordset
+    Dim ws As Worksheet
+    Set rs = openRs("SELECT [id], [vorname] & ' ' & [nachname] AS [name] " &_
+                    "FROM [address$] " & _
+                    "WHERE id BETWEEN 2 AND 3")
+    Set ws = ActiveWorkbook.Sheets("TARGET")
+    writeFullData ws.Range("A1"), rs
+    rs.Close
+End Sub</code>
+=== Handhabung der Header ===
+Bei writeFullData() und beim writeHeader() kann man den Paramter iWriteParams auf axwHeaderRedable setzen. Ich zeige hier mal schnell noch den Unterschied.
+Die Tabelle Hat die 2 Felder
+<code>ID | SECURITY_TPE | SECURITY_ID
+-------------------------------
+| T3           | AX3456Z
+| T3           | AX4564R</code>
+Mit dem Standard: iWriteParams = axwNone
+Die Titelzeile belibt unverändert
+<code vb>writeFullData Sheets("trg").Range("A1"), openRs("select * from [src$]")</code>
+<code>ID | SECURITY_TPE | SECURITY_ID
+-------------------------------
+| T3           | AX3456Z
+| T3           | AX4564R</code>
+Mit axwHeaderRedable wird die Titelzeile in eine lesbarere Form gebracht
+<code vb>writeFullData Sheets("trg").Range("A1"), openRs("select * from [src$]"), axwHeaderRedable</code>
+<code>Id | Security Tpe | Security Id
+-------------------------------
+| T3           | AX3456Z
+| T3           | AX4564R</code>
+===== Code =====
+Und hier noch der Code zu den oben bechriebenen Funktionen
+<source '%%fpath%%/%%fname%%' vb>

Yaslaw.Info

User Tools

Site Tools

Differences

Page Tools