Office: (Office 2007) Excel-Tabellen mit SQL auswerten

Das Problem: In unserem Betrieb wollen die Leute von mir einfache
Tabellenauswertungen auf Datenbankbasis. Die Tabellen sind auch sehr kurz so, daß sich ein Access-"Monstrum" nicht lohnt. Deshalb: Gibt es eine Möglichkeit Excel-Tabellen innerhalb von Excel mit SQL auszuwerten.

Tabelle1 (eventuell zusammen mit Tabelle2) werden nach Tabelle3 durch
SQL ausgewertet?

Am besten noch durch ein Makro aufzunehmen.

Ich möchte keine Tabelle importieren, die MS-Query Option ist mir bekannt.

Gruß und Dank
xx

PS. Ich kann ein bißchen Makrosprache.

:)

 

Moin, xx,

Excel hat doch einen Makro-Rekorder... *wink.gif*

Was willst Du auswerten? Ich hätte auch zuerst auf MSQuery getippt - oder aber direkt oer Anweisung:

Code:

Code:

Beispiel und Code nach Tommy Bak/VBAExpress...

 

Hallo xx,

man kann das auch wie von dir zunächst angedacht per MS Query lösen.
Hier ein Beispiel : Eine Mappe enthält im Blatt "Tabelle1" Daten (u.a. die Felder "Ort" und "Menge"). Mit dem nachfolgenden Makro wird in "Tabelle2" per MS Query auf die Daten der "Tabelle1" zugegriffen :

VBA-Code:

Sub SQLAbfrageIntern()     'Beispiel für interne SQL-Abfrage innerhalb einer Arbeitsmappe     'z.B. Abfrage auf 'Tabelle2', die ihre Daten aus 'Tabelle1' bezieht     '08.07.2008, NoNet - www.excelei.de     Dim strBlattname As String     Dim strSQL As String 'Variable für den SQL-Abfragestring     strBlattname = "Tabelle1" 'Name des Blattes, dessen Daten abgefragt werden sollen     strSQL = "SELECT `" & strBlattname & "$`.Ort " & _              ", `" & strBlattname & "$`.Menge " & _              "FROM " & "`" & ActiveWorkbook.Path & "\" & _             Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - InStr(StrReverse(ActiveWorkbook.Name), ".")) & _             "`.`" & strBlattname & "$` `" & strBlattname & "$`"     Sheets("Tabelle2").Activate 'Das Zielblatt muss aktiviert werden !     With ActiveSheet.QueryTables.Add( _         Connection:="ODBC;DSN=Excel Files;DBQ=" & ActiveWorkbook.FullName & ";DefaultDir=" & _             ActiveWorkbook.Path & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;", _             Destination:=Range("A1"))         .CommandText = strSQL         .Name = "Interne SQL-Abfrage von Excel Files"         .FieldNames = True         .RowNumbers = False         .FillAdjacentFormulas = False         .PreserveFormatting = True         .RefreshOnFileOpen = False         .BackgroundQuery = True         .RefreshStyle = xlInsertDeleteCells         .SavePassword = False         .SaveData = True         .AdjustColumnWidth = True         .RefreshPeriod = 0         .PreserveColumnInfo = True         .Refresh BackgroundQuery:=False     End With End Sub

Code eingefügt mit Syntaxhighlighter 4.15

Hinweis : Da MS Query den vollständig qualifizierten Dateinamen benötigt, muss die Mappe bereits gespeichert sein, bevor das Makro funktioniert, da ich den Namen der Mappe mit ActiveWorkbook.Name bzw. ActiveWorkbook.Path referenziere !
Den SQL-Code kannst Du natürlich nach eigenen Bedürfnissen und Fähigkeiten anpassen !

 

Besten Dank an alle, die mir geantwortet haben.

Leider bin ich zu dämlich für Eure Lösungen.

Aber der Tipp, daß es mit Query auch geht, war goldrichtig.

Man muß nur unter:

Daten
Externe Daten importieren
Neue Abfrage erstellen...

die eigene Tabelle wählen und unter Optionen "Systemtabelle" einblenden. Dann kann man dieselben Tabellen einfügen, die in der gerade geladenen Arbeitsmappe sind. Und kann diese dann gemütlich mit MS-Query auswerten.

Gruß
xx

 

Hallo E.,

genau DAS macht mein VBA-Code (Du fragtest doch nach einer VBA-Lösung) !
Daher steht als Zusatz am Tabellenname auch das $-Zeichen (versteckte Systemtabelle der eigenen Mappe) im VBA-Code (z.B. strBlattname & "$`.Menge "!
Wenn Du das per Makrorekorder aufzeichnest, wird allerdings der Mappennamen und der Tabellenname sowie der SQL-Code fest im VBA-Code "verdrahtet" - daher habe ich das Ganze entflechtet und soweit dynamisiert, dass es sich immer auf die AKTUELLE Mappe und das AKTUELLE Tabellenblatt (bzw. "Tabelle2") bezieht. Letztendlich basiert mein Lösungsvorschlag aber exakt auf dem Knowhow, was Du nun selbst herausgefunden hast *wink.gif*

 

hallo zusammen,

ich weiß, dass ich einen sehr alten thread ausgrabe, aber vielleicht kann mir ja jemand helfen... *Smilie

ich habe "tabelle1", "tabelle2" und die sql-variable angepasst, aber ich bekomme nur einen "allgemeinen ODBC-error (Laufzeitfehler 1004)".

wie troubleshootet man denn soetwas?

Code:

der rest ist wie oben.. vielleicht noch wichtig: ich benutze excel 2007..

danke schonmal an alle fürs lesen *Smilie

-narf

 

Hallo narf2,

eine Alternative per ADO:

 

wow, vielen dank für den code, IngGi.. *Smilie

klappt aber noch nicht ganz. wenn ich mein sql-statement wie oben nehme und vorher die variable strBlattname mit "Daten" initialisiere, bekomme ich einen fehler, dass er das objekt "daten" nicht finden könne.

der bleibt dann bei der zeile oRS.open stehen. "Daten" heißt mein tabellenblatt.. oder wie müsste ich mein sql-statement formen?? (sinngemäß erstmal: select * from Daten)

 

Exceltabellen müssen mit einem Dollarzeichen abgeschlossen werden. Bezeichnungen mit Leer- und Sonderzeichen müssen in SQL mit eckigen Klammern umschlossen werden, also etwa ...
Code:

 

super, vielen dank ebs17 *Smilie jetzt funktionierts, auch wenn er beim ersten lauf noch einen fehler gebracht hat und beim unveränderten zweiten keinen mehr *Smilie

vielleicht noch eine klitzekleine frage... kann man irgendwie auch spaltenNUMMERN selektieren statt sie mit der spaltenüberschrift aus zeile 1 anzusprechen?
also z.b. "select #spalte1#,#spalte2# from Daten where #spalte37# = 0"
hab in den überschriften teilweise scheußliche sonderzeichen drinnen und zumindest eines davon scheint den vba-editor so zu verwirren, dass er a) das zeichen nicht mehr anzeigt und b) anschließend in ein feld VOR dem cursor schreibt. mir wäre aber das sonderzeichen egal, wenn das mit der spaltennr klappen würde *Smilie

 

Hallo narf2,

Spaltennummern kannst du nicht verwenden, aber du kannst Aliase vergeben. Nach dem Muster

SELECT [ht&%/%$§%)] AS Firmenname FROM [Daten$] WHERE [SD%$&$/%] = 0

In der Zieltabelle wird dann für die Spalte der Name Firmenname verwendet.

Gruß Ingolf

 

Eine SQL-Anweisung benötigt eindeutige (und lesbare) Bezeichnungen für die Felder (Spalten). Damit wäre auch eine eventuelle Reihenfolge der Spalten egal.

Oben im Code hast Du aber auch einen Hinweis, dass man Feldbezeichnungen ignorieren kann (=> HDR=No). Du könntest also für den Tabellenbereich die Titelzeile nicht erfassen:
Code:

Jetzt würden von der SQL-Anweisung intern Ersatzbezeichnungen verwendet (=> F1, F2, F3, ...).

Kleiner Nachteil an der Geschichte: Hier muss dann auch die untere Bereichsgrenze angegeben und eventuell vorher erst ermittelt werden.
Hat man unterhalb der Tabelle einen freien Bereich, kann man den zur Vereinfachung und zur Verwendung einer konstanten Grenze mitnutzen und würde dann leere Datensätze einfach ausfiltern. Das über ein Feld, das immer ausgefüllt ist:
Code:

 

vielen dank für eure antworten.

ich bin mir jetzt nicht ganz sicher, ob ich das verstanden habe @ ebs17...

ich habe jetzt diese zeile gebastelt (aber auch statt A1 F1 versucht)

Code:

..und bekomme den fehler "für mindestens einen erforderlichen parameter wurde kein wert angegeben"

aber ich habe grade schon ein bisschen experimentiert und zumindest bei spaltenüberschriften ohne sonderzeichen klappt folgendes:

Code:

wenn ich ein sonderzeichen -in dem fall nur eine runde klammer- in der spaltenüberschrift habe, bekomme ich so natürlich einen syntaxerror. aber auch wenn ich den ausdruck in eine eckige klammer packe, heißt es --> " ist kein gültiger ausdruck....

Code:

 

Jetzt bin ich mir ein wenig unsicher, was Du genau machst. Zur Unterstützung lies mal dieses Tutorial: ADO und Excel

Wenn es mit (resultierenden) Feldnamen Probleme gibt, würde ich ein Recordset auf eine Abfrage "SELECT * FROM ..." anlegen und mir dann die Feldnamen aus dem Recordset ausgeben lassen (ein Codebeispiel dazu ist auch im Tutorial). Wenn ich dann weiß, welche Bezeichnungen SQL intern benutzt, kann ich sie auch zielsicher von außen selber benutzen.

 

jetzt versteh ich auch langsam immer weniger - trotz (oder wegen) des tutorials *wink.gif*
du sprichst von den ausgabefunktionen auf seite 3 des tutorials? und wie gebe ich die feldnamen aus?? mir fällts selbst schwierig, fragen zu formulieren, da das alles etwas viel neues auf einmal ist *wink.gif*

noch eine ergänzung zu oben:
diese zeile (s.o.)....
Code:

bringt den gleichen fehler wie
Code:

nämlich: "für mindestens einen erforderlichen parameter wurde kein wert angegeben"

was komisch ist, denn wenn ich keinen bereich angebe, funktioniert die zweite abfrage eigentlich. also liegt es vielleicht an der bereichsangabe?? die excel-tab ist recht groß, daher gehen die spalten auch von A bis BG

was ich in dem oberen beitrag experimentiert habe, war folgendes:
ich schiebe in den query-string einfach noch die überschrift aus dem entsprechenden datenfeld ein:
Code:

wobei Sheets("Daten").Cells(1, 1) nach Firmenname aufgelöst wird -- meine erste spaltenüberschrift (A1 bzw. cells(1,1)).
das würde es mir zumindest ersparen, die sonderzeichen, an denen sich der vba editor verschluckt, im klartext in den vba-code zu schreiben. ob er sich dann nicht trotzdem verschluckt, weiß ich noch nicht. im moment mag er ja noch nicht einmal die runde klammer.