This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
vba:excel:adodbsql [02.11.2016 09:55:22] yaslaw |
vba:excel:adodbsql [06.05.2019 11:08:01] (current) yaslaw |
||
---|---|---|---|
Line 1: | Line 1: | ||
<const> | <const> | ||
- | version=1.2.1 | + | version=1.6.0 |
- | vdate=02.11.2016 | + | vdate=22.01.2018 |
fname=lib_adodb_for_xls.bas | fname=lib_adodb_for_xls.bas | ||
ns=%NAMESPACE% | ns=%NAMESPACE% | ||
Line 30: | Line 30: | ||
axcReconnect = 2 ^ 0 'Ein Reconnect wird erzwungen | axcReconnect = 2 ^ 0 'Ein Reconnect wird erzwungen | ||
axcNoHeader = 2 ^ 1 'Die erste Zeile ist keine Kopfzeile. Die Felder werden mit f1, f2...fx angesprochen | 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> | End Enum</code> | ||
Line 38: | Line 46: | ||
' * Handle dhe adodb-connection to the current workbook | ' * Handle dhe adodb-connection to the current workbook | ||
' * @param axConnParams Paramters für die Connection | ' * @param axConnParams Paramters für die Connection | ||
+ | ' * @param String Pfad zur QuellDatei. Standard ist der Pfad des ausführenden Workbooks | ||
' * @return ADODB.Conection | ' * @return ADODB.Conection | ||
' */ | ' */ | ||
- | Private Property Get connection(Optional ByVal iConnParams As axConnParams = axcNone) As Object</code> | + | Private Property Get connection(Optional ByVal iConnParams As axConnParams = axcNone, Optional ByVal iFilePath As String = Empty) As Object</code> |
==== Function openRs() ==== | ==== Function openRs() ==== | ||
Line 56: | Line 65: | ||
<code vb>'/** | <code vb>'/** | ||
' * Write the Header of a adodb.recordset | ' * Write the Header of a adodb.recordset | ||
- | ' * @param Range First cell | + | ' * @param Worksheet/Range/Address |
' * @param Recordset | ' * @param Recordset | ||
+ | ' * @param axWriteParams | ||
' */ | ' */ | ||
- | Public Sub writeHeader(ByRef ioStartCell As Range, ByRef ioRs As Object) | + | Public Sub writeHeader(ByRef iRange As Variant, ByRef ioRs As Object, Optional ByVal iWriteParams As axWriteParams = axwNone) |
</code> | </code> | ||
Line 67: | Line 77: | ||
<code vb>'/** | <code vb>'/** | ||
' * Schreibt ins Excel inkl Header | ' * Schreibt ins Excel inkl Header | ||
- | ' * @param Range First cell | + | ' * @param Worksheet/Range/Address |
' * @param Recordset | ' * @param Recordset | ||
+ | ' * @param axWriteParams | ||
' */ | ' */ | ||
- | Public Sub writeFullData(ByRef ioStartCell As Range, ByRef ioRs As Object) | + | Public Sub writeFullData(ByRef iRange As Variant, ByRef ioRs As Object, Optional ByVal iWriteParams As axWriteParams = axwNone) |
</code> | </code> | ||
Line 141: | Line 152: | ||
End Sub</code> | 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 | ||
+ | ------------------------------- | ||
+ | 1 | T3 | AX3456Z | ||
+ | 2 | 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 | ||
+ | ------------------------------- | ||
+ | 1 | T3 | AX3456Z | ||
+ | 2 | 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 | ||
+ | ------------------------------- | ||
+ | 1 | T3 | AX3456Z | ||
+ | 2 | T3 | AX4564R</code> | ||
===== Code ===== | ===== Code ===== | ||
Und hier noch der Code zu den oben bechriebenen Funktionen | Und hier noch der Code zu den oben bechriebenen Funktionen | ||
<source '%%fpath%%/%%fname%%' vb> | <source '%%fpath%%/%%fname%%' vb> |