Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vba Variable in SQL Abfrage integrieren
22.08.2023 09:11:43
Chrisi
Hallo zusammen,

ich programmiere gerade ein Makro welches einen Datensatz von dem letzten 50 Tagen aus einer Oracle (Metis) Datenbank in mein ExcelSheet importiert und diese Daten anschließend filtert und in die gewünschten Blätter (je nachdem aus welchem Tabellenblatt man das Makro startet) kopiert.

Derzeit sind die Kriterien nach denen er Filter Hardcode(fix hinterlegt - siehe Code) ich möchte jetzt aber das im Einstellungsblatt des jeweiligen Tabellenblatts in einem gewissen Bereich neue Filterkriterien hineingeschrieben werden können, das Makro diese erkennt, auf eine Variable schreibt und mit diesen Variablen dann die SQL-Abfrage macht.

Ist jetzt vllt noch bisschen schwer vorzustellen aber schaut euch einfach die BeispielDatei und den Code an und dann sollte man sich auskennen.

Es gibt einmal das Blatt "CL_Chip_Sinterfreigabe" dazu gehört das Einstellungsblatt "EinstellungenSinterfreigabe"
Dann gibt es "CL_Chip_Q_Prüfung" dazu gehört das Einstellungsblatt "EinstellungenQ_Prüfung"
Und dann gibt es noch "CL_Modul_Q_Prüfung" dazu gehört "EinstellungenModulQPrüfung"

Die Bereiche in welche neue Filterkriterien eingefügt werden können sind bei EinstellungenSinterfreigabe & EinstellungenQ_Prüfung die gleichen (B7:F33) und bei EinstellungenModulQPrüfung ist die Range(B7:F38)

Die Kriterien welche schon dort drinnen stehen sollen auch dort bleiben.

Ablauf:

1) Button wird gedrückt
2) Makro geht je nachdem in welchem Blatt man das Makro startet auf die jeweiligen Einstellungsseite, schreibt die ".Values" auf die Variablen und sucht mit diesen Variablen dann in der SQL-Abfrage die passenden Daten
3) Gefundene Daten werden in "ZW" (zwischenablage) kopiert und anschließend in das richtige Blatt eingefügt

Code für CL_Chip_Sinterfreigabe:

Option Explicit
Dim oracle As Object
Dim sql_result As Variant
Dim wsOutput As Worksheet
Dim sql_cmd As String
Sub analyse_chip_sinterfreigabe()

Dim juengsteDatum As Date
Dim LRow As Integer
Dim LRow1 As Integer
Dim LRow5 As Integer
Dim LRow6 As Integer
Dim Zeile2 As Integer
Dim Zeile3 As Integer
Dim LRow10 As Integer
Dim LRow11 As Integer
Dim LRow12 As Integer
Dim LRow13 As Integer
Dim LRow7 As Integer
Dim BezeichnungB7 As String
Dim MatnummerC7 As String
Dim VorgangD7 As Integer


Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Filterkriterium "juengsteDatum" bestimmen
juengsteDatum = Date - 30

BezeichnungB7 = Sheets("EinstellungenSinterfreigabe").Range("B7").Value
MsgBox (BezeichnungB7)


'Datenbankimport von Metis in Excelsheet "ZW"
Set oracle = Ora_connect("metis", "auskunft", "auskunft")
sql_cmd = "select a.losnr, k.sachnummer, k.benennung, a.rzeitbis from vs.aposll a join vs.kopfll k on (a.losnr = k.losnr) Where ""BENENNUNG"" = '" & BezeichnungB7 & "' And ""RZEITBIS"" > TO_DATE('" & juengsteDatum & "', 'dd.mm.yyyy hh24:mi:ss') ORDER BY a.rzeitbis ASC"""
sql_result = sql_request(oracle, sql_cmd)
Set wsOutput = ThisWorkbook.Sheets("ZW")
wsOutput.UsedRange.ClearContents
Call sql2table(sql_result, ThisWorkbook.Name, wsOutput.Name, 1, 2, 1, False, True)

'Spaltengröße anpassen & formatierung
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Range("A1:D1").Interior.ColorIndex = 6
Range("D2").Select


'datensatz kopieren und einfügen
Sheets("ZW").UsedRange.Copy
Sheets("CL_Chip_Sinterfreigabe").Activate
Range("A7").Select
If ActiveCell = "" Then
ActiveSheet.Paste
'duplikate entfernen
Sheets("CL_Chip_Sinterfreigabe").Select
LRow6 = Cells(Rows.Count, 1).End(xlUp).Row
Range("A6:D" & LRow6).Select
ActiveSheet.Range("A6:D" & LRow6).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes
Range("D7").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With
For Zeile2 = Range("A65536").End(xlUp).Row To 7 Step -1
If Cells(Zeile2, 1).Interior.ColorIndex = 6 Then
Rows(Zeile2).Delete
End If
Next Zeile2
Else
LRow1 = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & LRow1 + 1).Select
ActiveSheet.Paste
'duplikate entfernen
Sheets("CL_Chip_Sinterfreigabe").Select
LRow6 = Cells(Rows.Count, 1).End(xlUp).Row
Range("A6:D" & LRow6).Select
ActiveSheet.Range("A6:D" & LRow6).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes
Range("D7").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With
For Zeile3 = Range("A65536").End(xlUp).Row To 7 Step -1
If Cells(Zeile3, 1).Interior.ColorIndex = 6 Then
Rows(Zeile3).Delete
End If
Next Zeile3
End If

'DropdownMenü erstellen
LRow10 = Cells(Rows.Count, 1).End(xlUp).Row
Range("E7:E" & LRow10).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=EinstellungenSinterfreigabe!$A$1:$A$3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

'DropdownMenü farblich formatieren
'Offen
Range("E7").Select
LRow11 = Cells(Rows.Count, 1).End(xlUp).Row
Range("E7:E" & LRow11).Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"=EinstellungenSinterfreigabe!$A$1", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'Abgeschlossen
Range("E7").Select
LRow12 = Cells(Rows.Count, 1).End(xlUp).Row
Range("E7:E" & LRow12).Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"=EinstellungenSinterfreigabe!$A$2", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'In Arbeit
Range("E7").Select
LRow13 = Cells(Rows.Count, 1).End(xlUp).Row
Range("E7:E" & LRow13).Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"=EinstellungenSinterfreigabe!$A$3", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'datum sorteiren
LRow7 = Cells(Rows.Count, 1).End(xlUp).Row
Sheets("CL_Chip_Sinterfreigabe").Select
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("CL_Chip_Sinterfreigabe").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CL_Chip_Sinterfreigabe").Sort.SortFields.Add2 Key _
:=Range("D7:D" & LRow7), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("CL_Chip_Sinterfreigabe").Sort
.SetRange Range("A7:D" & LRow7)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("D:D").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With

Sheets("CL_Chip_Sinterfreigabe").Activate
Range("E7").Select
MsgBox ("Datenimport erfolgreich abgeschlossen")
End Sub

BeispielDatei:

https://www.herber.de/bbs/user/162427.xlsm

Ich brauche im Prinzip nur jemanden der mir sagen kann, wie ich diese Variablen in meine SQL-Abfrage einbauen muss, den Rest schaffe ich selbst


2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Beitrag macht
22.08.2023 10:12:47
MCO
Hallo Chrisi!

An sich ja nett, dass du fragst, aber den Code so unformatiert hier hinzuwerfen und zu behaupten "VBA - Level gut", obwohl der offensichtlich überwiegend aufgezeichnet wurde, ist echt frech. Beschäftige dich mit dem Thema "Code vereinfachen", "lesbarer machen", etc

  • Die Parameter aus einer Zelle in VBA-code zu bekommen kriegst du hin.

  • Die Parameterbenennung in deinem SQL kennen wir nicht.

  • Texte zusammenstückeln, die einer Parametervorgabe in deiner SQL-Abfrage entsprechen, können wir also leider nicht helfen. Nicht zuletzt, weil wir keinen Zugriff haben.


  • Ich bin da raus.

    Gruß, MCO

    Anzeige
    AW: Beitrag macht
    23.08.2023 10:31:29
    snb
    Vermeide : 'Select' und 'Activate in VBA
    Vermeide überflüssige Variablen.

    sql_cmd = "select a.losnr, k.sachnummer, k.benennung, a.rzeitbis from vs.aposll a join vs.kopfll k on (a.losnr = k.losnr) Where `BENENNUNG` = '" & Tabelle12.Cells(7, 2) & "' And RZEITBIS > TO_DATE('" & Format(Date - 30, "dd.mm.yyyy hh24:mi:ss") & "' ORDER BY a.rzeitbis ASC"

    48 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige
    Anzeige

    Links zu Excel-Dialogen

    Anzeige

    Infobox zum Thema

    EXCEL - VBA Variable in SQL Abfrage integrieren


    Inhaltsverzeichnis


    Die Fragestellung


    Du möchtest in VBA eine Variable in eine SQL-Abfrage integrieren.


    Erläuterung des Problems {#erläuterung-des-problems}


    Beim Arbeiten mit Datenbanken über VBA ist es oft notwendig, dynamische SQL-Abfragen zu erstellen, die Variablen enthalten. Dies kann erforderlich sein, um benutzerspezifische Daten zu filtern oder um Abfragen an bestimmte Bedingungen anzupassen.


    Lösung des Problems {#lösung-des-problems}


    Um eine VBA-Variable in eine SQL-Abfrage zu integrieren, musst du die SQL-Abfrage als String in VBA erstellen und die Variable an der entsprechenden Stelle einfügen. Hier ist ein Beispiel, wie du das machen kannst:

    Sub RunSQLQueryWithVariable()
        Dim sqlQuery As String
        Dim myVariable As String
        Dim dbConnection As Object ' ADODB.Connection
        Dim rs As Object ' ADODB.Recordset
    
        ' Variable definieren
        myVariable = "MeinWert"
    
        ' SQL-Abfrage als String erstellen, wobei die Variable integriert wird
        sqlQuery = "SELECT * FROM MeineTabelle WHERE MeineSpalte = '" & myVariable & "'"
    
        ' Datenbankverbindung aufbauen (Beispiel für eine Access-Datenbank)
        Set dbConnection = CreateObject("ADODB.Connection")
        dbConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Pfad\Zur\Datenbank.accdb;"
    
        ' SQL-Abfrage ausführen
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open sqlQuery, dbConnection
    
        ' Ergebnisse verarbeiten...
        ' (Hier würde dein Code stehen, um mit den Ergebnissen zu arbeiten)
    
        ' Aufräumen
        rs.Close
        dbConnection.Close
        Set rs = Nothing
        Set dbConnection = Nothing
    End Sub

    In diesem Beispiel wird eine einfache SQL-Abfrage erstellt, die eine Variable myVariable enthält. Die Variable wird in die SQL-Abfrage eingefügt, indem sie in den SQL-String eingebettet wird.


    Anwendungsbeispiele aus der Praxis


    • Benutzerdefinierte Berichte: Erstellen von Berichten basierend auf Benutzereingaben.
    • Datenaktualisierung: Aktualisieren von Datenbanktabellen mit Werten aus einer Excel-Tabelle.

    Tipps


    • Achte darauf, die Variable korrekt zu konvertieren und zu escapen, um SQL-Injection zu verhindern.
    • Wenn du numerische Werte in die SQL-Abfrage einfügst, stelle sicher, dass du keine Anführungszeichen um die Variable setzt.

    Verwandte Themenbereiche


    • SQL in VBA
    • Datenbankprogrammierung
    • ADODB in VBA

    Zusammenfassung


    Die Integration einer VBA-Variable in eine SQL-Abfrage ermöglicht es dir, dynamische und benutzerdefinierte Datenbankabfragen zu erstellen. Es ist wichtig, die Variablen sicher zu handhaben und die Abfrage korrekt zu formatieren, um Fehler und Sicherheitsrisiken zu vermeiden.

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige