PDA

Vollständige Version anzeigen : Exceltabellen mit SQL Abfragen


ThomasKlueber
18.07.2006, 12:48
Hallo VBA Fans, ;)
ich hoffe ich finde jemanden, der mir helfen kann. :rolleyes:
Ich habe Exceltabellen die ich per Makro und SQL (ADO Objekte) auslese.
DAs auslesen an sich funktioniert eigentlich ganz toll. Wenn ich jedoch SQL Funktionen dazunehme bekomme ich immer den Fehler !
Automatisierungsfehler ! 80040e21 :mad:
Ich komme aber nicht drauf was ich falsch mache. :( Ich habe es mit der WHERE Klausel probiert und mit der HAVING Klausel immer das gleiche :boah: Ergebnis. Wie gesagt lasse ich nur die Daten abfragen ohne COUNT(*) As XX dann klappt das wie geschmiert.
Würde mich freuen, wenn mir einer helfen kann. :)
Gruß Thomas

Dim ws As Worksheet
Dim con As New ADODB.Connection
Dim rs_KA As New ADODB.Recordset
Dim txtSelect, txtFrom, txtWhere, txtSQL, txtOrder, txtHaving, VKKlass As String
Dim comm As New Command
Dim fld As Field
Dim i, Zeile As Integer


Set ws = ThisWorkbook.Worksheets("Tabelle1")

con.Mode = adModeReadWrite 'Leider habe ich hierfür noch keine Doku
con.CursorLocation = adUseClient ' Hilfe schweigt sich auch aus

'Die Abfrage über OLEDB (Jet-Modul) bringt besser Abfragemöglichkeiten per SQL
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=T:\Nachkalkulation\2006\Q2\nk-kum-Q2-2006-06.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No;"";"

Set comm.ActiveConnection = con

'SQl String für Querry zusammenbauen
txtSelect = "SELECT Count(*) AS AnzData, NK.KA, NK.KA_Art, NK.Art, NK.Bew, NK.NGP, NK.GM, NK.HK, NK.KID, " & _
"NK.KuName, NK.KuNr, NK.lastFaktWert, NK.Stat, NK.VKB, NK.VKK1 "
txtFrom = "FROM `T:\Nachkalkulation\2006\Q2\nk-kum-Q2-2006-06`.`NK_NB$` NK "
'Datum wie in ACCESS Abfrage angeben ({d 2006-01-01} für ODBC funzt über OLE nicht)
' txtWhere = "WHERE (NK.Art = 9 Or NK.Art = 99 Or NK.Art = 6) AND " & _
' "(NK.Bew = 9 Or NK.Bew = 99 Or NK.Bew = 2) AND " & _
' "(NK.lastFaktWert >= #01/01/2006# And " & _
' "NK.lastFaktWert <= #31/12/2006#) "
txtWhere = ""
txtOrder = "GROUP BY NK.VKK1 "
txtHaving = "HAVING ((NK.Art = 9 Or NK.Art = 99 Or NK.Art = 6) AND " & _
"(NK.Bew = 9 Or NK.Bew = 99 Or NK.Bew = 2) AND " & _
"(NK.lastFaktWert >= #01/01/2006# And " & _
"NK.lastFaktWert <= #31/12/2006#));"
txtSQL = txtSelect & txtFrom & txtWhere & txtOrder & txtHaving
Debug.Print txtSQL
comm.CommandText = txtSQL
rs_KA.CursorType = adOpenForwardOnly
rs_KA.Open comm
'Zeile = Selection.Row

'Braucht man nur um Feldnamen auszugegeben (Testzwecke)
For Each f In rs_KA.Fields
i = i + 1
ws.Cells(1, i).Value = f.Name
Next

'Gibt alle Datensätze wie ausgelesen aus
ws.[a2].CopyFromRecordset rs_KA

rs_KA.Close
con.Close

End Sub

ThomasKlueber
20.07.2006, 08:35
Hallo Excelfreunde,

habe das Problem selber lösen können. :mrcool: Trial and Error :upps: ist zwar umständlich führt aber auch zum Ziel. Das Problem der Abfrage war schlicht, dass in der Select Anweisung nur Datenfelder aufgeführt sein dürfen, welche dann auch mit den Aggregatfunktionen beaerbeitet werden. (Irgendwie auch logisch)
Viele werden sich fragen welchen Vorteil hat man bei solch einer Vorgehensweise ? Nun bisher habe ich die Daten per Matrixformeln ausgewertet, da die Berechnungen an mehreren Bedingungen geknüpft sind.
Nun jeden Monat kommen ca 1000 Datensätze dazu, so dass jedesmal mit suchen&ersetzen die Bezüge richtig gestellt werden müssen(Teilweise in bis zu 15 verschiedenen Ergbenisblättern). Bei jeder Änderung wurden die Berechnungen aktualisiert und das kostet natürlich Zeit. Dann mußten die Daten auch noch in verschiedene Tabellen kopiert werden um wieder andere Berechnungen durch zu führen also recht mühsam und Fehleranfällig das ganze. Jetzt kann ich die Daten in einem riesen Tempo aus einer Tabelle saugen gleich berechnen lassen und erhalte als Ergebnis nur das was ich brauche. Und ich kann die Anzahl der Bedingungen nach oben schrauben, denn mit Summe(Wenn(...)) sind ja nur 7 Kriterien möglich. :(
Trotzdem Danke an alle, welche sich vielleicht bemüht haben mir helfen zu wollen aber vielleicht auch nicht die Zeit dazu hatten das Problem zu lösen. :rolleyes:
Naja SQL scheint auch nicht viele Excelfreunde zu interessieren. Vielleicht hat dieser Beitrag dazu gedient ein paar Möglichkeiten aufzuzeigen wie man größere Datenmengen auch mit Excel gut verwalten kann ohne dann gleich auf ACCESS umsteigen zu müssen. Denn Excel hat trotzdem viele Vorzüge in der Visualisierung von Daten und hat einen größeren Verbreitungsgrad als ACCESS.
So für die welche an der Lösung interessiert sind hier noch der Korrekte SQL String Aufbau.
txtSelect = "SELECT NK.VKK1, Sum(NK.NGP) As [SuNGP], Sum(NK.HK) As [SuHK], Sum(NK.GM) As [SuGM], " & _
"Sum(NK.GM - NK.IBVK) As [SuGM II], Sum(NK.IBVK) As [IB], Sum(NK.MTKost) As [MT], Sum(NK.SuFK) As [SuLK], " & _
"Sum(NK.SuFL) As [SuFL], Sum(NK.SuFK + NK.SuFL) As [SuFeK] "
txtFrom = "FROM `T:\Nachkalkulation\2006\Q2\nk-kum-Q2-2006-06`.`NK_NB$` NK "
'Datum wie in ACCESS Abfrage angeben ({d 2006-01-01} für ODBC funzt über OLE nicht)
txtWhere = "WHERE (NK.Stat = 1) AND " & _
"(NK.VKK1 <> 'RA' And NK.VKK1 <> 'SO' And NK.VKK1 <> 'Z') AND " & _
"(NK.Art = 6 Or NK.Art = 9 Or NK.Art = 99) AND " & _
"(NK.Bew = 2 Or NK.Bew = 9 Or NK.Bew = 99) AND " & _
"(NK.lastFaktWert >= #01/01/2006# And " & _
"NK.lastFaktWert <= #31/12/2006#) "
'txtWhere = ""
txtOrder = "GROUP BY NK.VKK1; "
'Die Having Klausel greift auf die Gruppierten Ergebnisse zu !! ALso Nachselektion
txtHaving = ""
txtSQL = txtSelect & txtFrom & txtWhere & txtOrder

Der fertige SQL String sieht dann so aus:
SELECT NK.VKK1, Sum(NK.NGP) As [SuNGP], Sum(NK.HK) As [SuHK], Sum(NK.GM) As [SuGM],
Sum(NK.GM - NK.IBVK) As [SuGM II], Sum(NK.IBVK) As [IB], Sum(NK.MTKost) As [MT],
Sum(NK.SuFK) As [SuLK], Sum(NK.SuFL) As [SuFL], Sum(NK.SuFK + NK.SuFL) As [SuFeK]
FROM `T:\Nachkalkulation\2006\Q2\nk-kum-Q2-2006-06`.`NK_NB$` NK
WHERE (NK.Stat = 1) AND (NK.VKK1 <> 'RA' And NK.VKK1 <> 'SO' And NK.VKK1 <> 'Z') AND
(NK.Art = 6 Or NK.Art = 9 Or NK.Art = 99) AND
(NK.Bew = 2 Or NK.Bew = 9 Or NK.Bew = 99) AND
(NK.lastFaktWert >= #01/01/2006# And NK.lastFaktWert <= #31/12/2006#)

Grüße an alle Excelfan von Thomas :)