AW: Datenbankabfrage in Excel Userform mit VBA
08.09.2010 23:21:03
fcs
Hallo Uli,
da wirst du ohne fachlichen Beistand wohl nur schwer zurechtkommen.
1. Vorraussetzung ist, dass die entsprechenden ODBC-Treiber für die Oracle-Datenbank installiert sind.
2. Versuche dann dem Excel-Makrorekorder unter Einbeziehung von MS Querry die Daten aus der Oracle-Datenbank nach Excel zu Importieren. Definiere dabei auch Selektionskriterien soweit erforderlich.
3. Jetzt hast du das Grundgerüst zusammen, aber der Code in Excel sieht recht wild aus und die Flexibilität bzgl. Datenabfrage fehlt noch.
Nachfolgend sind Beispiele unter Excel 2007 für den Zugriff auf eine ACCESS Datenbank - Oracle ist für den Hausgebrauch dann doch etwas teuer.
DIe Beispiele zeigen den aufgezeichneten und durch die Forumssoftware noch ewas zerstückelten Code und die von mir gemachten Anpassungen für bessere Übesicht und Flexibilität bei den Kriterien.
Mein Beispiel erzeugt in Excel 2007 ein ListenObjekt mit Abfrage. Das kann in älteren Versionen noch etwas anders aussehen.
Gruß
Franz
Public sKrit1$, sKrit2$, sKrit3$
Sub Daten_Import_von_ACCESS()
' Makro per Rekorder aufgezeichnet und editiert
' MS Querry-Abfrage in eine MA Access-Datenbank
' Erstellt unter Excel 2007
Dim sSource$, sDatei$, sCom$, sDefaultDir$, wks As Worksheet
sDefaultDir = "C:\Users\Franz\Documents\Acc_db"
sDatei = "C:\Users\Franz\Documents\Acc_db\ADRESSEN.MDB"
sSource = "ODBC;DSN=MS Access Database;"
sSource = sSource & "DBQ=" & sDatei & ";" & sDefaultDir & ";"
sSource = sSource & "DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
'Datenfelder aus Quelltabelle
sCom = "SELECT `Abitur 1974`.Abitur, `Abitur 1974`.Familienname,`Abitur 1974`.Vorname, "
sCom = sCom & "`Abitur 1974`.PLZ, `Abitur 1974`.Ort, `Abitur 1974`.Ortsteil, "
sCom = sCom & "`Abitur 1974`.Strasse, `Abitur 1974`.Land, `Abitur 1974`.Staat "
'Quelltabelle in Datei
sCom = sCom & "FROM `" & sDatei & "`.`Abitur 1974` "
'Abfragekriterien - Die Variablen könnte man dann per Userform füllen
sKrit1 = "[A-K]%" 'Nachnamen beginnend mit A bis K
sKrit2 = "S%" 'Nachnamen beginnend mit A bis K
sKrit3 = "2%" 'PLZ beginned mit 2
sCom = sCom & "`Abitur 1974` WHERE ((`Abitur 1974`.Familienname Like '" & sKrit1 & "') "
sCom = sCom & "OR (`Abitur 1974`.Familienname Like '" & sKrit2 & "')) "
sCom = sCom & "AND (`Abitur 1974`.PLZ Like '" & sKrit3 & "') "
'Sortierung der Ausgabeliste
sCom = sCom & "ORDER BY `Abitur 1974`.Familienname, `Abitur 1974`.Vorname"
Set wks = ActiveSheet 'Zieltabelle für importierte Daten
If wks.ListObjects.Count = 1 Then wks.ListObjects(1).Delete 'Testzeile
With wks.ListObjects.Add(SourceType:=0, Source:=sSource, _
Destination:=wks.Range("$A$1")).QueryTable
.CommandText = sCom
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Daten_Test3"
.Refresh BackgroundQuery:=False
End With
End Sub
Sub Daten_Abfrage_Aktualisieren()
' Makro per Rekorder aufgezeichnet und editiert
' MS Querry-Abfrage in eine MA Access-Datenbank aktualisieren
' Erstellt unter Excel 2007
Dim sDatei$, sCom$, wks As Worksheet
sDatei = "C:\Users\Franz\Documents\Acc_db\ADRESSEN.MDB"
'Datenfelder aus Quelltabelle
sCom = "SELECT `Abitur 1974`.Abitur, `Abitur 1974`.Familienname,`Abitur 1974`.Vorname, "
sCom = sCom & "`Abitur 1974`.PLZ, `Abitur 1974`.Ort, `Abitur 1974`.Ortsteil, "
sCom = sCom & "`Abitur 1974`.Strasse, `Abitur 1974`.Land, `Abitur 1974`.Staat "
'Quelltabelle in Datei
sCom = sCom & "FROM `" & sDatei & "`.`Abitur 1974` "
'Abfragekriterien - Die Variablen könnte man dann per Userform füllen
sKrit1 = "[A-K]%" 'Nachnamen beginnend mit A bis K
sKrit2 = "T%" 'Nachnamen beginnend mit A bis K
sKrit3 = "3%" 'PLZ beginned mit 2
sCom = sCom & "`Abitur 1974` WHERE ((`Abitur 1974`.Familienname Like '" & sKrit1 & "') "
sCom = sCom & "OR (`Abitur 1974`.Familienname Like '" & sKrit2 & "')) "
sCom = sCom & "AND (`Abitur 1974`.PLZ Like '" & sKrit3 & "') "
'Sortierung der Ausgabeliste
sCom = sCom & "ORDER BY `Abitur 1974`.Familienname, `Abitur 1974`.Vorname"
Set wks = ActiveSheet 'Zieltabelle für importierte Daten
With wks.ListObjects(1).QueryTable
.CommandText = sCom
.Refresh BackgroundQuery:=False
End With
End Sub
Sub aaTest()
' aaTest Makro
' MS Querry-Datenimport aus MS ACCESS - Import-Abfrage erstellen
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\USERS\Franz\Documents\Acc_db\ADRESSEN.MDB; _
DefaultDir=C:\USERS\Franz\Documents\Acc_db;DriverId=281" _
), Array(";FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT `Abitur 1974`.Abitur, `Abitur 1974`.Familienname, `Abitur 1974`.Vorname, ` _
Abitur 1974`.PLZ, `Abitur 1974`.Ort, `Abitur 1974`.Ortsteil, `Abitur 1974`.Strasse, `Abitur 1974`.Land, `Abitur 1974`.S" _
, _
"taat" & Chr(13) & "" & Chr(10) & "FROM `C:\USERS\Franz\Documents\Acc_db\ADRESSEN.MDB`.` _
Abitur 1974` `Abitur 1974`" & Chr(13) & "" & Chr(10) & "WHERE (`Abitur 1974`.Familienname Like '[A-K]%') AND (`Abitur 1974`.PLZ Like '2%') OR (`Abitur 1974`.Familienname" _
, _
" Like 'T%') AND (`Abitur 1974`.PLZ Like '2%')" & Chr(13) & "" & Chr(10) & "ORDER BY ` _
Abitur 1974`.Familienname, `Abitur 1974`.Vorname" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Tabelle_Abfrage_von_MS_Access_Database15"
.Refresh BackgroundQuery:=False
End With
End Sub
Sub Makro6()
' Makro6 Makro - Abfrage aktualisieren/neue Kriterien
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
With Selection.ListObject.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Users\Franz\Documents\Acc_db\ADRESSEN.MDB;DriverId= _
281;FIL=MS Access;MaxBufferSize=2048;PageTimeo" _
), Array("ut=5;"))
.CommandText = Array( _
"SELECT `Abitur 1974`.Abitur, `Abitur 1974`.Familienname, `Abitur 1974`.Vorname, ` _
Abitur 1974`.PLZ, `Abitur 1974`.Ort, `Abitur 1974`.Ortsteil, `Abitur 1974`.Strasse, `Abitur 1974`.Land, `Abitur 1974`.S" _
, _
"taat" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\Franz\Documents\Acc_db\ADRESSEN.MDB`.` _
Abitur 1974` `Abitur 1974`" & Chr(13) & "" & Chr(10) & "WHERE (`Abitur 1974`.Familienname Like '[A-K]%') AND (`Abitur 1974`.PLZ Like '2%') OR (`Abitur 1974`.Familienname" _
, _
" Like 'T%') AND (`Abitur 1974`.PLZ Like '3%')" & Chr(13) & "" & Chr(10) & "ORDER BY ` _
Abitur 1974`.Familienname, `Abitur 1974`.Vorname" _
)
.Refresh BackgroundQuery:=False
End With
End Sub