Uebergabe Parameter aus Excel in ein SQL-Abfrage
Peter Eichler
SQL meldet, dass bei Abfragen welche nicht graphisch dargestellt
werden können (z.b. weil verschachtelt), keine Parameter übergeben
werden können. Hier brauch ich Hilfe:
Ich verwende aus Excel heraus eine stored procedure, die Daten über
einen Zeitraum abfragt. Das Anfangs- und End-Datum möchte ich als
Parameter aus einer Excel-Datei lesen.
Der Aufruf lautet heute:
exec test.dbo.sp_abfrage
@VONDATUM = '01.01.00',
@BISDATUM = '01.01.00'
Hier muss ich nun anstelle der festen Eingabe der Parameter diese aus
der Excel-Tabelle lesen. Wie das gehen?
Vielen Dank für eure Hilfe
pe
Alexander Freudenberg
"Peter Eichler" <peic...@focus-consulting.de> schrieb im Newsbeitrag
news:3b14de4f...@news.komtel.net...
> Ich verwende aus Excel heraus eine stored procedure, die Daten über
> einen Zeitraum abfragt. Das Anfangs- und End-Datum möchte ich als
> Parameter aus einer Excel-Datei lesen.
>
> Der Aufruf lautet heute:
>
> exec test.dbo.sp_abfrage
> @VONDATUM = '01.01.00',
> @BISDATUM = '01.01.00'
>
> Hier muss ich nun anstelle der festen Eingabe der Parameter diese aus
> der Excel-Tabelle lesen. Wie das gehen?
Die Werte können am besten über range("A1").value in Variablen gelesen
werden und dann als SQL-String verknüpft werden. Anschließend kann man
eine ADO-Verbindung verwenden, um das Resultat zurückzugeben.
Hier mal ein Beispielcode:
Function GetSumTime(dBeginn, dEnde, sAuftragsnummer, sProjekt,
sUnterprojekt)
Dim sSQL As String
Dim AccessConnect As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
sSQL = "'" & Format(dBeginn, "dd.mm.yyyy") & "','" & Format(dEnde,
"dd.mm.yyyy") & "','" & sAuftragsnummer & "','" & sProjekt & "','" &
sUnterprojekt & "'"
sSQL = "exec sp_select_Summe_Zeitaufwand " & sSQL
Set cn = New ADODB.Connection
With cn
.Provider = "MSDataShape"
.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA
SOURCE=xxxxxxxxxx;DATABASE=xxxxxx;UID=xxxxxxxxxx;PWD=xxxxxxxx;"
.CursorLocation = adUseServer
.Open
Set rs = .Execute(sSQL)
GetSumTime = rs.Fields(0).Value
Set rs = Nothing
.Close
End With
Set cn = Nothing
End Function
Für die xxxx muss man noch die entsprechenden Informationen angeben.
AF.
Peter Eichler
vielen dank für die wirklich umfangreiche antwort!
ich habe jedoch noch ein problem, denn ich erhalte aus der stored
procedure eine recht große datenmenge zurück (20000 zeilen mit 30
spalten). lass mich das problem nochmal etwas anders darstellen:
in ein excel-blatt hole ich mir über "externe daten" per sql-script
eine große datenmenge zurück. die sql-abfrage ist verschachtelt und
kann darum in ms-querry nicht graphisch dargestellt werden, weshalb
ich die möglichkeit parameter zu übergeben (mit "?") nicht nutzen
kann.
der von dir dargestellte weg ermöglicht dies, jedoch eben nicht über
externe daten, was mir bei der datenmenge erforderlich scheint.
da ich ernsthafte schwierigkeiten mit der lösung dieses problemes
habe, bitte ich nochmals um HILFE.
vielen dank
pe
Alexander Freudenberg
> ich habe jedoch noch ein problem, denn ich erhalte aus der stored
> procedure eine recht große datenmenge zurück (20000 zeilen mit 30
> spalten). lass mich das problem nochmal etwas anders darstellen:
Eigentlich ist dann Excel auch nicht so richtig geeignet; lässt sich bei
dieser Größe nicht Access sinnvoller einsetzen?
> der von dir dargestellte weg ermöglicht dies, jedoch eben nicht über
> externe daten, was mir bei der datenmenge erforderlich scheint.
Die Zeile Set rs = .Execute(sSQL) liefert in rs einen kompletten
Recordset zurück. Dieser muss nun nur noch in die entsprechenden
Excelzellen überführt werden (MoveFirst, MoveNext usw.). Allerdings
könnte das tatsächlich etwas dauern. Müsste man mal probieren.
Versuche ansonsten mal, die Erstellung des externen Datenzugriffs
aufzuzeichnen. Man könnte mittels Modifikation der Eigenschaft
.CommandText des Objektes ActiveSheet.QueryTables(1) eine entsprechende
Parameteränderung bewerkstelligen. Wenn man will, könnte man diese
Änderung an ein Ändern-Ereignis hängen.
So war die ursprüngliche Zuweisung bei der Erstellung der
Beispiel-Datenquelle:
.CommandText = Array("SELECT Tab.x, Tab.y" & Chr(13) & "" & Chr(10) &
"FROM `C:\TEMP\Mappe1`.Tab Tab")
Und so könnte man es ändern:
Sub test2()
ActiveSheet.QueryTables(1).CommandText = Array("SELECT Tab.x, Tab.y" &
Chr(13) & "" & Chr(10) & "FROM `C:\TEMP\Mappe1`.Tab Tab WHERE Tab.x <
4")
ActiveSheet.QueryTables(1).Refresh
End Sub
AF.
Peter Eichler
vielen Dank! jetzt habe ich endlich eine lösung!
übrigens: du scheinst echt was drauf zu haben! Danke ;-)
pe