Benutzer mit den meisten Antworten
SQL-Abfrage in Excel 2016 Filtern

Frage
-
Hallo Zusammen!
Ich habe folgende SQL-Abfrage in Excel eingefügt:
SELECT artikel.name, t2.artikel, cast(SUM((t1.mengestrukt / 1000) * t1.F_MENGE) as decimal(18,2)) as menge, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME AS AGNAME
FROM rohstoffbedarf_auswahl_datum_verschnitt('01.02.2018','01.01.2019') t1 INNER JOIN (stuelipos t2 INNER JOIN ARTIKEL ON t2.ARTIKEL = ARTIKEL.ARTIKEL INNER JOIN ARTIKELGRUPPE ON ARTIKEL.ARTIKELGRUPPE = ARTIKELGRUPPE.ARTIKELGRUPPE)
ON t1.id = t2.id
WHERE t2.artikel >= '0000' AND t2.artikel >= '3000' AND t2.artikel <= '3999'
GROUP BY t2.artikel, artikel.name, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME
Die funktioniert einwandfrei.
Nun ist meine Frage: ist es möglich, das Fett markierte Datum aus einer Zelle in Excel zu beziehen, um den Filter manuell anzupassen?
Zur info: "FROM rohstoffbedarf_auswahl_datum_verschnitt('01.02.2018','01.01.2019')" ist eine Funktion/Prozedur, welche mit unserem ERP-System verknüpft ist. In der Weboberfläche wird besagtes Datum als Filter eingefügt.
Vielen Dank schon mal im Voraus!!
Antworten
-
Ganz so kompliziert musst du es gar nicht machen:
Über Extras->Verweise binde dir die Microsoft Activex Data Objects dazu.
Dann kannst du mit den Objekten ADO.Connection und ADO.Recordset die zusammengestoppelte Abfrage selber ausführen.Mittels String-Verbindungen baust du das SQL dann zusammen:
Dim xSheet As Worksheet
Dim xVonDate as string
dim xBisDate as string
Set xSheet = ActiveSheet
xVonDate = format(cdate( xSheet.Range("C1")), "tt.MM.yyyy")
xBisDate = format(cdate( xSheet.Range("C2")), "tt.MM.yyyy")
...schnitt('" & xVonDate & "','" & xBisDate& "') t1 ....
Mittels des Connection-Objects verbindest du zur Datenbank.
Per Connection.Execute() erhältst du ein Recordset.
Das Recordset kannst du direkt mittels CopyFromRecordset einem Zellbereich zuordnen:
https://msdn.microsoft.com/de-de/vba/excel-vba/articles/range-copyfromrecordset-method-excel?f=255&MSPPError=-2147217396
- Als Antwort vorgeschlagen Stefan FalzModerator Mittwoch, 7. März 2018 15:39
- Als Antwort markiert Stefan FalzModerator Samstag, 10. März 2018 11:43
-
Hallo bfuerchau,
Danke für die Antwort, das werde ich bei Gelegenheit ausprobieren.
Folgendes hat zu guter letzt für mich funktioniert:
Private Sub bt_anzeigen_Click()
If [C1] <> "" And [C2] = "" Then
[C2] = [C1]
End If
If [C2] <> "" And [C1] = "" Then
[C1] = [C2]
End If
If [H4] = "" And [C2] = "" Then
MsgBox "Es wurden keine Filterkriterien eingegeben. Aktion wird abgebrochen.", vbCritical, "Aktion abgebrochen"
Range("C1").Select
Exit Sub
End If
Dim sql_select As String
Dim sql_from As String
Dim sql_where As String
Dim sql_group As String
Dim sql_order As String
sql_select = "SELECT t2.artikel, artikel.name , cast(SUM((t1.mengestrukt / 1000) * t1.F_MENGE) as decimal(18,2)) as menge, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME AS AGNAME "
sql_from = "FROM rohstoffbedarf_auswahl_datum_verschnitt('" & [C1] & " 00:00:00' , '" & [C2] & " 23:59:59') t1 INNER JOIN (stuelipos t2 INNER JOIN ARTIKEL ON t2.ARTIKEL = ARTIKEL.ARTIKEL INNER JOIN ARTIKELGRUPPE ON ARTIKEL.ARTIKELGRUPPE = ARTIKELGRUPPE.ARTIKELGRUPPE) ON t1.id = t2.id "
sql_where = "WHERE t2.artikel >= '0000' AND t2.artikel >= '3000' AND t2.artikel <= '3999'"
sql_group = "GROUP BY t2.artikel, artikel.name, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME"
sql_order = ""
On Error Resume Next
Rows("3:20000").Select
Selection.ClearContents
Selection.QueryTable.Delete
On Error GoTo 0
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=p2plus;Description=p2plus;UID=Gomilsek;APP=Microsoft Office 2003;WSID=ADMIN005;DATABASE=p2plus;Trusted_Connec" _
), Array("tion=Yes")), Destination:=Range("A3"))
.CommandText = sql_select & sql_from & sql_where & sql_group & sql_order
.Name = "Abfrage von p2plus"
.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
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-21
Range("A3").Select
End Sub
Das ist meine Lösung.
Vielen Dank für eure Denkanstöße!
- Als Antwort vorgeschlagen Stefan FalzModerator Mittwoch, 7. März 2018 15:39
- Als Antwort markiert Stefan FalzModerator Samstag, 10. März 2018 11:43
Alle Antworten
-
Hallo Matthias,
wo und wie genau hast Du diese SQL Abfrage in welcher Form eingefügt?
In VBA per ADO? ...?
Falls ja, kannst Du die Parameterwerte natürlich schon variabel aus einer Zelle auslesen. Dafür zeig aber bitte mal deinen Code, den können wir dann entsprechend anpassen.
Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community -
Habe die Abfrage fix funktionsfähig in VBA umgebastelt. Code ist wie folgt:
Private Sub Workbook_Open()
Worksheets("Tabelle1").Select
Dim sql_select As String
Dim sql_from As String
Dim sql_where As String
Dim sql_group As String
Dim sql_order As String
sql_select = "SELECT t2.artikel, artikel.name , cast(SUM((t1.mengestrukt / 1000) * t1.F_MENGE) as decimal(18,2)) as menge, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME AS AGNAME "
sql_from = "FROM rohstoffbedarf_auswahl_datum_verschnitt('01.01.2018','01.01.2019') t1 INNER JOIN (stuelipos t2 INNER JOIN ARTIKEL ON t2.ARTIKEL = ARTIKEL.ARTIKEL INNER JOIN ARTIKELGRUPPE ON ARTIKEL.ARTIKELGRUPPE = ARTIKELGRUPPE.ARTIKELGRUPPE) ON t1.id = t2.id "
sql_where = "WHERE t2.artikel >= '0000' AND t2.artikel >= '3000' AND t2.artikel <= '3999'"
sql_group = "GROUP BY t2.artikel, artikel.name, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME"
sql_order = ""
On Error Resume Next
Rows("1:20000").Select
Selection.ClearContents
Selection.QueryTable.Delete
On Error GoTo 0
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=p2plus;Description=p2plus;UID=Gomilsek;APP=Microsoft Office 2003;WSID=ADMIN005;DATABASE=p2plus;Trusted_Connec" _
), Array("tion=Yes")), Destination:=Range("A1"))
.CommandText = sql_select & sql_from & sql_where & sql_group & sql_order
.Name = "Abfrage von p2plus"
.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
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-21
Range("A1").Select
Selection.AutoFilter
End Sub
Eingabefelder sollen C1 und C2 im Sheet "Tabelle1" sein.- Bearbeitet Matthias Klebi Dienstag, 6. Februar 2018 09:41
-
Ich habe die Abfrage in Excel über Daten - neue Abfrage - Aus Datenbank - Aus SQL-Server-Datenbank eingefügt
Hallo Matthias,
die Abfrage über MS Query holst, wird per OleDB ausgeführt und da kannst Du das Fragezeichen ? als Platzhalter verwenden und den (die) auf die Werte von Zellen mappen.
Olaf Helper
[ Blog] [ Xing] [ MVP] -
Hallo Olaf,
Danke für deine Antwort.
Das habe ich schon mehrfach getestet. Bevor ich die Parameter überhaupt auswählen kann, kommt ein datetime-Syntax-Fehler.
- Bearbeitet Matthias Klebi Dienstag, 6. Februar 2018 10:07
-
Wie oben gesagt, per MS-Query die Abfragewerte als "?" (ohne Anführungszeichen!) in den SQL aufnehmen.
In Excel speichern.
Über die "Eigenschaften" der Abfrage kommst du nun an die Parameterzuordnung, in der du jedem ? eine Zelle zuweisen kannst.Ist die Quelle z.B. ein SQL-Server sind Parameter auch in der Form "[Parametername]" erlaubt.
Allerdings gehen diese Parameterzuordnungen bei einer Bearbeitunge der Abfrage verloren und müssen dann wiederholt werden.
-
Hallo bfuerchau,
Originalcode:
FROM rohstoffbedarf_auswahl_datum_verschnitt('01.01.2018','01.01.2019')
Mit ? :
FROM rohstoffbedarf_auswahl_datum_verschnitt(?, ?)
folgender Fehler:
DataSource.Error: Microsoft SQL: Zeile 2: Falsche Syntax in der Nähe von '?'.
Details:
DataSourceKind=SQL
DataSourcePath=server-sql;p2plus
Message=Zeile 2: Falsche Syntax in der Nähe von '?'.
Number=170
Class=15oder
FROM rohstoffbedarf_auswahl_datum_verschnitt('?','?')
folgender Fehler:
DataSource.Error: Microsoft SQL: Syntaxfehler beim Konvertieren einer Zeichenfolge in eine datetime-Zeichenfolge.
Details:
DataSourceKind=SQL
DataSourcePath=server-sql;p2plus
Message=Syntaxfehler beim Konvertieren einer Zeichenfolge in eine datetime-Zeichenfolge.
Number=241
Class=16
- Bearbeitet Matthias Klebi Dienstag, 6. Februar 2018 10:18
-
Hier hat der SQL-Server mal wieder besondere Abkürzungen.
Da die Abfrage von einer Prozedur erfolgt sind laut Select-Syntax keine Parameter erlaubt.Allerdings ist der echte Aufruf eigentlichein Call, also sollte folgendes möglich sein:
CALL rohstoffbedarf_auswahl_datum_verschnitt(?, ?)
Da du das Ergebnis allerdings verjoinst, hast du syntaktisch leider keine Möglichkeit hier Parameter zu verwenden.
Nun ist die Frage, was die Prozedur tut und lässt sich dies nicht:a) in den SQL direkt übernehmen
b) in einer View in der Datenbank regeln- Bearbeitet Der Suchende Dienstag, 6. Februar 2018 10:40
-
Hallo bfuerchau,
Die Prozedur sieht wie folgt aus:
CREATE FUNCTION dbo.rohstoffbedarf_auswahl_datum_verschnitt
--/// "Aufloesen" mehrerer Stammstuecklistenstrukturen nach Auswahl
--/// Rueckgabewert ist eine Tabelle
-- /// PATH erlaubt eine strukturgerechte Sortierung
(
@DATUM_VON AS datetime,@DATUM_BIS AS datetime
)
RETURNS @tree table
(
LINIE char(10),
F_MENGE decimal(18,2),
DRUCKEN int,
PRODUKTIONSTAG datetime,
ARTIKEL char(22) collate Latin1_General_CI_AS NOT NULL,
STUELI char(22) collate Latin1_General_CI_AS NOT NULL,
POSITION int NOT NULL,
lvl int NOT NULL,
id int not null,
path varchar(1000) collate Latin1_General_CI_AS NOT NULL,
mengestrukt decimal (18,2),
nicht_aufloesen bit
)
AS
BEGIN
DECLARE @lvl AS int, @path AS varchar(1000)
SELECT @lvl = 1, @path = '.'
INSERT INTO @tree
SELECT Fertigungstage.Linie, Fertigungstage.F_Menge, Fertigungstage.DRUCKEN, Fertigungstage.PRODUKTIONSTAG, isnull(STUELIPOS.ARTIKEL, ' '),
STUELIPOS.STUELI , STUELIPOS.POSITION, @lvl,
STUELIPOS.id,'.' + REPLICATE('0', 10-DATALENGTH(RTRIM(CAST(STUELIPOS.POSITION AS varchar(10))))) + RTRIM(CAST(STUELIPOS.POSITION AS varchar(10)))+ '.',
isnull(STUELIPOS.VERSCHNFAKTOR, 1) * MENGE, Fertigungstage.nicht_aufloesen
--FROM Fertigungstage JOIN STUELIPOS ON Fertigungstage.Produkt_Nr=STUELIPOS.ARTIKEL
FROM Fertigungstage JOIN STUELIPOS ON Fertigungstage.Produkt_Nr=STUELIPOS.STUELI
--/// WHERE STUELI = @ROOT
WHERE Fertigungstage.Produktionstag BETWEEN @DATUM_VON AND @DATUM_BIS
ORDER BY POSITION
WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1
INSERT INTO @tree
SELECT T.LINIE, T.F_Menge, T.DRUCKEN, T.PRODUKTIONSTAG, isnull(E.ARTIKEL, ' '), E.STUELI, E.POSITION, @lvl,
E.id,T.path + REPLICATE('0', 10-DATALENGTH(RTRIM(CAST(E.POSITION AS varchar(10))))) + RTRIM(CAST(E.POSITION AS varchar(10)))+ '.',
((E.MENGE / E.MENGEPMNG) * T.MENGESTRUKT) * isnull(E.VERSCHNFAKTOR, 1), T.nicht_aufloesen
FROM STUELIPOS AS E JOIN @tree AS T ON E.STUELI = T.ARTIKEL AND T.lvl = @lvl - 1
WHERE T.nicht_aufloesen != '1' OR T.nicht_aufloesen is null
ORDER BY E.POSITION
END
RETURN
END
Im Grunde zählt die Prozedur den Rohstoffbedarf für die besagte Datumsgrenze zusammen, und gibt sie aus. Datentyp des Datums in dbo.fertigungstage ist APdate, ein Benutzerdefinierter Datentyp "datetime" mit einer länge von 8.Klar kann ich meine Abfrage in einer View darstellen, nur ist diese dann Fix und das Datum wäre dann nicht mehr variabel oder?
Und wie meinst du das, in SQL direkt übernehmen?
-
Auch das habe ich versucht. Ich habe die Variablen deklariert, allerdings sind diese nur für die intere SQL-Verarbeitung und nicht für excel/extern funkltionsfähig :/
Kann man das nicht irgendwie in VBA konfigurieren? Hier nochmal der VBA-Code für die Abfrage:
Private Sub Workbook_Open()
Worksheets("Tabelle1").Select
Dim sql_select As String
Dim sql_from As String
Dim sql_where As String
Dim sql_group As String
Dim sql_order As String
sql_select = "SELECT t2.artikel, artikel.name , cast(SUM((t1.mengestrukt / 1000) * t1.F_MENGE) as decimal(18,2)) as menge, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME AS AGNAME "
sql_from = "FROM rohstoffbedarf_auswahl_datum_verschnitt('01.01.2018','01.01.2019') t1 INNER JOIN (stuelipos t2 INNER JOIN ARTIKEL ON t2.ARTIKEL = ARTIKEL.ARTIKEL INNER JOIN ARTIKELGRUPPE ON ARTIKEL.ARTIKELGRUPPE = ARTIKELGRUPPE.ARTIKELGRUPPE) ON t1.id = t2.id "
sql_where = "WHERE t2.artikel >= '0000' AND t2.artikel >= '3000' AND t2.artikel <= '3999'"
sql_group = "GROUP BY t2.artikel, artikel.name, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME"
sql_order = ""
On Error Resume Next
Rows("1:20000").Select
Selection.ClearContents
Selection.QueryTable.Delete
On Error GoTo 0
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=p2plus;Description=p2plus;UID=Gomilsek;APP=Microsoft Office 2003;WSID=ADMIN005;DATABASE=p2plus;Trusted_Connec" _
), Array("tion=Yes")), Destination:=Range("A1"))
.CommandText = sql_select & sql_from & sql_where & sql_group & sql_order
.Name = "Abfrage von p2plus"
.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
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-21
Range("A1").Select
Selection.AutoFilter
End Sub
-
Ganz so kompliziert musst du es gar nicht machen:
Über Extras->Verweise binde dir die Microsoft Activex Data Objects dazu.
Dann kannst du mit den Objekten ADO.Connection und ADO.Recordset die zusammengestoppelte Abfrage selber ausführen.Mittels String-Verbindungen baust du das SQL dann zusammen:
Dim xSheet As Worksheet
Dim xVonDate as string
dim xBisDate as string
Set xSheet = ActiveSheet
xVonDate = format(cdate( xSheet.Range("C1")), "tt.MM.yyyy")
xBisDate = format(cdate( xSheet.Range("C2")), "tt.MM.yyyy")
...schnitt('" & xVonDate & "','" & xBisDate& "') t1 ....
Mittels des Connection-Objects verbindest du zur Datenbank.
Per Connection.Execute() erhältst du ein Recordset.
Das Recordset kannst du direkt mittels CopyFromRecordset einem Zellbereich zuordnen:
https://msdn.microsoft.com/de-de/vba/excel-vba/articles/range-copyfromrecordset-method-excel?f=255&MSPPError=-2147217396
- Als Antwort vorgeschlagen Stefan FalzModerator Mittwoch, 7. März 2018 15:39
- Als Antwort markiert Stefan FalzModerator Samstag, 10. März 2018 11:43
-
Hallo bfuerchau,
Danke für die Antwort, das werde ich bei Gelegenheit ausprobieren.
Folgendes hat zu guter letzt für mich funktioniert:
Private Sub bt_anzeigen_Click()
If [C1] <> "" And [C2] = "" Then
[C2] = [C1]
End If
If [C2] <> "" And [C1] = "" Then
[C1] = [C2]
End If
If [H4] = "" And [C2] = "" Then
MsgBox "Es wurden keine Filterkriterien eingegeben. Aktion wird abgebrochen.", vbCritical, "Aktion abgebrochen"
Range("C1").Select
Exit Sub
End If
Dim sql_select As String
Dim sql_from As String
Dim sql_where As String
Dim sql_group As String
Dim sql_order As String
sql_select = "SELECT t2.artikel, artikel.name , cast(SUM((t1.mengestrukt / 1000) * t1.F_MENGE) as decimal(18,2)) as menge, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME AS AGNAME "
sql_from = "FROM rohstoffbedarf_auswahl_datum_verschnitt('" & [C1] & " 00:00:00' , '" & [C2] & " 23:59:59') t1 INNER JOIN (stuelipos t2 INNER JOIN ARTIKEL ON t2.ARTIKEL = ARTIKEL.ARTIKEL INNER JOIN ARTIKELGRUPPE ON ARTIKEL.ARTIKELGRUPPE = ARTIKELGRUPPE.ARTIKELGRUPPE) ON t1.id = t2.id "
sql_where = "WHERE t2.artikel >= '0000' AND t2.artikel >= '3000' AND t2.artikel <= '3999'"
sql_group = "GROUP BY t2.artikel, artikel.name, t2.vbme, ARTIKEL.ARTIKELGRUPPE, ARTIKELGRUPPE.NAME"
sql_order = ""
On Error Resume Next
Rows("3:20000").Select
Selection.ClearContents
Selection.QueryTable.Delete
On Error GoTo 0
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=p2plus;Description=p2plus;UID=Gomilsek;APP=Microsoft Office 2003;WSID=ADMIN005;DATABASE=p2plus;Trusted_Connec" _
), Array("tion=Yes")), Destination:=Range("A3"))
.CommandText = sql_select & sql_from & sql_where & sql_group & sql_order
.Name = "Abfrage von p2plus"
.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
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-21
Range("A3").Select
End Sub
Das ist meine Lösung.
Vielen Dank für eure Denkanstöße!
- Als Antwort vorgeschlagen Stefan FalzModerator Mittwoch, 7. März 2018 15:39
- Als Antwort markiert Stefan FalzModerator Samstag, 10. März 2018 11:43