PDA

Vollständige Version anzeigen : Variablengesteuerte SQL Abfrage aus Excel VBA starten und in Zellen ausgeben


skyfe
17.04.2009, 18:49
Hallo Experten,

ich will mittels Excel VBA auf eine Access DB zugreifen (read only). Aus dieser DB möchte ich eine Abfrage starten und das Ergebnis in das Excel Worksheet schreiben. Die Abfrage soll spezifisch mit einem Parameter aus z.B. einer Excel Zelle starten.

Bsp.:

DB Abfrage:

2 Tabellen


Excel:

Ich will in eine Zelle z.B. "2009" eingeben. Das VBA Makro soll mir nun nur folgende Abfrage starten und ins Worksheet ausgeben:

SELECT tab1.Segment, tab1.Region, tab1.[2004], tab2.[2004], tab1.[2005], tab2.[2005], tab1.[2006], tab2.[2006], tab1.[2007], tab2.[2007], tab1.[2008], tab2.[2008], tab1.[2009], tab2.[2009]
FROM tab1 INNER JOIN tab2 ON (tab1.Segment = tab2.Segment) AND (tab1.Region = tab2.Region);

Gebe ich jetzt in der Parameterzelle "2007" ein, dann soll nur diese Abfrage starten:

SELECT tab1.Segment, tab1.Region, tab1.[2004], tab2.[2004], tab1.[2005], tab2.[2005], tab1.[2006], tab2.[2006]
FROM tab1 INNER JOIN tab2 ON (tab1.Segment = tab2.Segment) AND (tab1.Region = tab2.Region);


Wie kann ich das flexibel gestalten und realisieren?

Danke!

NoNet
17.04.2009, 21:33
Hallo skyfe,

Du kannst den SQL-String in einerSchleife dynamisch zusammensetzen - hier ein Beispiel für die Jahreszahl (z.B. 2009) in Zelle A1 :

<table align="center" border="1" cellpadding="4" cellspacing="1" width="98%"style="border-collapse:collapse;font-family:Arial;font-size:12;"><tbody><tr><td style="background-color:#EBEBEB;"><b>VBA-Code:</b></td></tr><tr><td style="background-color:#FFFFFF;font-family:Courier New;font-size:12;"><FONT COLOR=#0000FF>Sub</FONT>&nbsp;SQLAbfrage()
&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Dim</FONT>&nbsp;strSQL&nbsp;<FONT COLOR=#0000FF>As String</FONT>,&nbsp;intT&nbsp;<FONT COLOR=#0000FF>As Integer</FONT>

&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#008000>'SELECT&nbsp;tab1.Segment,&nbsp;tab1.Region,&nbsp;tab1.[2004],&nbsp;tab2.[2004],</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#008000>'tab1.[2005],&nbsp;tab2.[2005],&nbsp;tab1.[2006],&nbsp;tab2.[2006],&nbsp;tab1.[2007],</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#008000>'tab2.[2007],&nbsp;tab1.[2008],&nbsp;tab2.[2008],&nbsp;tab1.[2009],&nbsp;tab2.[2009]</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#008000>'FROM&nbsp;tab1&nbsp;INNER&nbsp;JOIN&nbsp;tab2&nbsp;ON&nbsp;(tab1.Segment&nbsp;=&nbsp;tab2.Segment)&nbsp;AND&nbsp;'</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#008000>'(tab1.Region&nbsp;=&nbsp;tab2.Region);</FONT>

&nbsp;&nbsp;&nbsp;&nbsp;strSQL&nbsp;=&nbsp;"SELECT&nbsp;tab1.Segment,&nbsp;tab1.Region"
&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>For</FONT>&nbsp;intT&nbsp;=&nbsp;2004&nbsp;<FONT COLOR=#0000FF>To</FONT>&nbsp;[A1].Value&nbsp;<FONT COLOR=#008000>'In&nbsp;A1&nbsp;steht&nbsp;das&nbsp;Jahr,&nbsp;z.B.&nbsp;2009</FONT>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strSQL&nbsp;=&nbsp;strSQL&nbsp;&amp;&nbsp;",&nbsp;tab1.["&nbsp;&amp;&nbsp;intT&nbsp;&amp;&nbsp;"],&nbsp;tab2.["&nbsp;&amp;&nbsp;intT&nbsp;&amp;&nbsp;"]"
&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#0000FF>Next</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;strSQL&nbsp;=&nbsp;strSQL&nbsp;&amp;&nbsp;"&nbsp;FROM&nbsp;tab1&nbsp;INNER&nbsp;JOIN&nbsp;tab2&nbsp;ON&nbsp;"
&nbsp;&nbsp;&nbsp;&nbsp;strSQL&nbsp;=&nbsp;strSQL&nbsp;&amp;&nbsp;"(tab1.Segment&nbsp;=&nbsp;tab2.Segment)&nbsp;AND&nbsp;"
&nbsp;&nbsp;&nbsp;&nbsp;strSQL&nbsp;=&nbsp;strSQL&nbsp;&amp;&nbsp;"(tab1.Region&nbsp;=&nbsp;tab2.Region);"

&nbsp;&nbsp;&nbsp;&nbsp;<FONT COLOR=#008000>'Hier&nbsp;die&nbsp;weitere&nbsp;Bearbeitung&nbsp;des&nbsp;strSQL-Strings:</FONT>
&nbsp;&nbsp;&nbsp;&nbsp;MsgBox&nbsp;strSQL
<FONT COLOR=#0000FF>End Sub</FONT>
</td></tr><tr><td style="background-color:#EBEBEB;font-size:10;">Code eingefügt mit <b><a href="http://rtsoftwaredevelopment.de" target="_blank">Syntaxhighlighter 4.15</a></b></td></tr></tbody></table>

Den SQL-String kannst Du nun in Deiner Abfrage weiterverwenden.

ebs17
17.04.2009, 22:16
Den SQL-String kannst Du nun in Deiner Abfrage weiterverwenden.
Eine Abfrage ist eine SQL-Anweisung.

Das Ergebnis der dynamisch erstellten SQL-Anweisung kannst Du so in Dein Arbeitsblatt übernehmen:
' Verweis auf MS DAO 3.6 Object Library setzen
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\test\Eine.mdb")
Set rs = db.OpenRecordset(strSQL) ' strSQL wie oben ermittelt
ActiveSheet.Range("C3").CopyFromRecordset rs ' C3 Zielzelle links oben
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Anmerkung: Feldbezeichnungen 2004, 2005, 2006, ... in einer Datenbanktabelle weisen auf ein falsches Datenmodell hin (Stichwort Normalisierung (http://www.donkarl.com/AEK/AEKDownloads/AEK7_Normalisierung.zip)). Falsch schon z.B. deshalb, weil man wie oben angefragt solche "Verdrehungen" anstellen muss.
Real sollten Tabellen und die resultierende Abfrage anders aussehen.

skyfe
18.04.2009, 10:44
Hallo,

@NoNet: Danke für dein Codebeispiel, funktioniert bestens!

@ebs17: Da hast du sicher Recht mit der Normalisierung, aber das Thema ist bei mir schon sehr lange her (damals in der Schule) :-)

Danke!

ebs17
18.04.2009, 11:53
aber das Thema ist bei mir schon sehr lange her (damals in der Schule)
Das nenne ich dann ein selbstausgestelltes Armutszeugnis. Es heißt doch: "Lernen, lernen und nochmals lernen" (Lenin), nicht "Vegessen, vergessen, ..."

gook
11.01.2014, 08:14
Hallo zusammen,

habe mir gerade das Thema durchgelsen und dachte ich könnte vieleicht noch folgende Frage anfügen:

ich importiere aus Excel Daten in ein ERP System über eine ODBC Schnittstelle.
Es handelt sich um Artikelbezeichungen in verschiedenen Sprachen.
Da ich den SQL Befehl mit verschieden Variablen zusammensetzte verwenden ich folgenden Syntax:

Code:
INSERT INTO ArtText (ArtNr,SprKz,ArtBez1,ArtBez2,ME) VALUES ('" & Cells(i, 10).Value & "','E',""" & Cells(i, 22).Value & """,""" & Cells(i, 25).Value & """,'" & mengeneinheitEN & "')"


Ich habe viele ' in meinem String deswegen habe ich die 3 """ verwendet. Leider habe ich aber nun auch viele " in meinem String und bekomme nun diverse Fehlermedlungen.

Was kann ich hier tun um wirklich den kompletten String mit ALLEn Sonderzeichen " ' usw. zu importieren?

Vielen Dank schonmal

rushifell
11.01.2014, 09:12
verwende die einfachen tuetelchen innerhalb deines sql-statements, verwende chr(34) fuer die verwendung der doppelten ohne sie als text eingeben zu müssen oder definiere eine konstante mit chr(34) und verwende diese anstelle.

ebs17
11.01.2014, 09:27
Eine Anfügeabfrage, die nur einen Datensatz anfügt (wie bei Verwendung von VALUES) und ein Insert per Recordset sind etwa in Aufwand und Geschwindigkeit gleichwertig. Bei der zweiten Variante muss man aber nicht einen zusammenhängenden String bauen und hat somit nicht das Doublequote-Problem.
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT ArtNr, SprKz, ArtBez1, ArtBez2, ME FROM ArtText"
.ActiveConnection = DeineConnection
.Open
.AddNew
.Fields("ArtNr") = Cells(i, 10).Value
.Fields("SprKz") = "E"
.Fields("ArtBez1") = Cells(i, 22).Value
.Fields("ArtBez2") = Cells(i, 25).Value
.Fields("ArtNr") = mengeneinheitEN
.Update
.Close
End With
Set rs = Nothing

rushifell
11.01.2014, 09:31
ich dachte, es ging allgemein um das "zurechtbasteln" von sql-statements. mein fehler.

ebs17
11.01.2014, 09:55
Nein, nein, Du hattest schon richtig gedacht. Das Problem ist nur, wenn die Werte aus den Excelzellen selber Quotes und Doublequotes enthalten, dann kann das ganz schnell dazu führen, dass der Gesamtstring für die SQL-Anweisung auseinanderfliegt. Das Gegensteuern mit den von Dir genannten Maßnahmen - ein Replace für eine Verdopplung von solchen Zeichen käme noch hinzu - ist möglich, aber etwas unübersichtlich.
Daher brachte ich eine Alternative ins Spiel, die man da problemfreier anwenden kann. Man denke dabei auch an die datentypgerechte Formatierung der Werte, die die Anfügeabfrage benötigt, die jetzt auch nicht von jedem beherrscht wird.

rushifell
11.01.2014, 10:45
habs kapiert. danke.