WCM Forum - daten aus excel nach access (mdb) exportieren

WCM Forum (http://www.wcm.at/forum/index.php)

- Programmierung (http://www.wcm.at/forum/forumdisplay.php?f=17)

- - daten aus excel nach access (mdb) exportieren (http://www.wcm.at/forum/showthread.php?t=138566)

Chrisi99

05.07.2004 10:38

daten aus excel nach access (mdb) exportieren

weiß jemand wie man Daten (per VBA) aus einer access-datenbank in excel öffnen und nach dem bearbeiten wieder in die mdb-datebank zurückspeichern kann? (also eigentlich brauch ich die implementierung von sql in vba :) )

mfg

wbendl

05.07.2004 11:02

Hi!

Werd mal ein Bißchen konkreter!

mfg

WB

Chrisi99

05.07.2004 11:08

ich möchte eine mdb datenbank in excel aufrufen (abfragen etc) dann bearbeiten können und wieder _zurückspeichern_

mfg

wbendl

05.07.2004 11:38

Hi!

Also mit VBA ist das auf jeden Fall zu realisieren. Wahrscheinlich ist es aber keine optimale Lösung.

Wenn du Änderungen wieder in die DB speichern willst, mußt du entweder "wissen" was geändert wurde, oder die ganzen Daten neu schreiben.

Bevor du diesen Aufwand treibst, solltest du es mit Bordmitteln versuchen.
Neuere Versionen von Excel können *.mdb öffnen. Vielleicht geht da schon alles, was für dich notwendig ist.

Wenn das nicht klappt, bleibt noch immer der Weg über VBA.
Dabei könnte ich dir helfen.

Ich hab mal nachgefragt: Zurückpeichern funktioniert angeblich nicht.
(Ich hab nur Excel 97, kann's also nicht probieren)
Damit bleibt zum Speicher wohl nur VBA.

mfg

WB

Chrisi99

05.07.2004 11:52

hab den code jetzt schon fast beisammen, aber irgendwo klemmts:

Code:

Option Base 1

Global db As Database, rc As Recordset, rc1 As Recordset, rec As Recordset, rc2 As Recordset

Global aktuellesnodes As Variant



Sub sql()

Dim w1 As Worksheet

Set w1 = Worksheets("Sheet1")

w1.Range("C3:K30").ClearContents

a = ActiveCell.Row

Nr = ActiveSheet.Cells(a, 29).Value

Set db = DBEngine.Workspaces(0).OpenDatabase("J:\PROJEKT\PIS\test\test.mdb")

sql1 = "SELECT Tabelle1.PNR, Tabelle1.NAME, Tabelle1.VNAM, Tabelle1.KST FROM Tabelle1 WHERE (((Tabelle1.PNR)=[NR]))"

Set rc1 = db.OpenRecordset(sql1)

On Error Resume Next

rc1.MoveFirst

ss = 3

i = rc1.RecordCount

w1.Select

While Not rc1.EOF

For anz = 1 To i

w1.Cells(3, ss).Value = rc1!fnr

w1.Cells(4, ss).Value = rc1!Pnr

w1.Cells(5, ss).Value = rc1!Name

w1.Cells(6, ss).Value = rc1!VNAM

ss = ss + 1

rc1.MoveNext

Next anz

Wend

End Sub

in der roten Zeile fehlt laut Debugger ein Parameter.. nur welcher ?

mfg

wbendl

05.07.2004 12:18

Hi!

Schon lange her, daß ich DAO verwendet habe. Versuch mal, den Parameter für den Typ mitzugeben. Wenn der fehlt, wird dbOpenTable
verwendet. Das funktioniert vielleicht mit der SQL-Anweisung nicht.

Probier mal
Set rc1 = db.OpenRecordset(sql1, dbOpenDynaset)

Falls deine Version ADO unterstützt, solltest du das verwenden.

mfg

WB

Chrisi99

05.07.2004 12:33

leider kommt die gleiche meldung
hättest du einen aedequanten ado-code parat ?

wbendl

05.07.2004 13:02

Ich schau mal nach dem Code.

Zwischenfrage: Sind die notwendigen Verweise gesetzt?

mfg

WB

Chrisi99

05.07.2004 13:16

Zitat:

Original geschrieben von wbendl
Ich schau mal nach dem Code.

Zwischenfrage: Sind die notwendigen Verweise gesetzt?

mfg

WB

ja, alle diese meldungen hab ich beseitigen können :D

kann es sein, dass meine select anweisung nicht ganz richtig ist?

"Die Select-Anweisung schließt ein reserviertes wort ein...."

mfg

wbendl

05.07.2004 13:34

Hier ein Beispiel für ADO:

Verweis auf Microsoft ActiveX Data Objects x.x Libary muß gesetzt sein.
strProv muß eventuell angepaßt werden.
Ansonsten müßte es so funktionieren.

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim strProv As String
Dim strDB As String

Dim strSQL As String

strProv = "Microsoft.Jet.OLEDB.4.0"
strDB = "D:\XXXX.mdb"
strSQL = "SELECT * FROM XXXX"

cnn.Provider = strProv
cnn.Open strDB, "Admin"
rst.Open strSQL, cnn

Wenn die Feldnamen eindeutig sind, kannst du den Tabellen-Namen weglassen. Ein Paar Klammern sin wohl auch überflüssig.
Die WHERE-Klausel scheint mir nicht OK zu sein.
Zum Testen des DB-Zugriffes nimm ein ganz einfaches SQL-Statement.

mfg

WB

Chrisi99

05.07.2004 13:44

hm, der code reicht ihm wieder nicht :(

Laufzeitfehler "Für mindestens einen erforderlichen Parameter wurde kein Wert übergeben"

bei: rst.Open strSQL, cnn

wbendl

05.07.2004 14:19

Dann hat du einen Fehler im SQL-Statement. Falscher Feldname z. B.

Das Beispiel funktioniert unter Excel 2002

Ich verabschiede mich jetz bis morgen Abend.

mfg

WB

Chrisi99

05.07.2004 14:26

Zitat:

Original geschrieben von wbendl
Dann hat du einen Fehler im SQL-Statement. Falscher Feldname z. B.

Das Beispiel funktioniert unter Excel 2002

Ich verabschiede mich jetz bis morgen Abend.

mfg

WB

habs zum laufen gebracht :)

jetzt beibt nur eine Frage: kann man der where-klausel eine Variabe übergeben (und vor allem wie )

mfg

renew

05.07.2004 15:30

Zitat:

Original geschrieben von Chrisi99
habs zum laufen gebracht :)

jetzt beibt nur eine Frage: kann man der where-klausel eine Variabe übergeben (und vor allem wie )

mfg

natürlich!

Du musst dir einfach den Abfrage String zusammenbasteln - in deinem Fall dann so:

sql1 = "SELECT Tabelle1.PNR, Tabelle1.NAME, Tabelle1.VNAM, Tabelle1.KST FROM Tabelle1 WHERE (((Tabelle1.PNR)= " & Variable & "))"

Chrisi99

06.07.2004 10:55

Zitat:

Original geschrieben von LLR
natürlich!

Du musst dir einfach den Abfrage String zusammenbasteln - in deinem Fall dann so:

sql1 = "SELECT Tabelle1.PNR, Tabelle1.NAME, Tabelle1.VNAM, Tabelle1.KST FROM Tabelle1 WHERE (((Tabelle1.PNR)= " & Variable & "))"

leider kann ich bei dieser variante keine Wildcards (*) benutzen, scheinbar hat
Set rc1 = db.OpenRecordset(sql1)
Proleme damit (Syntaxfehler, fehlender Operator)

eine Idee?

mfg

renew

06.07.2004 15:11

Wie Wildcards? Was hatn Tabelle1.PNR für einen Datentyp? String?
Und bei dem willst Wildcards benutzen - oder? (ich rate mal, falls es anders ist, schreib einfach ;))

Also wenn ich mit meiner Annahme recht haebn, dann sollte die Abfrage so ausschaun:

sql1 = "SELECT Tabelle1.PNR, Tabelle1.NAME, Tabelle1.VNAM, Tabelle1.KST FROM Tabelle1 WHERE (((Tabelle1.PNR) LIKE '*" & Variable & "*'))"

Chrisi99

06.07.2004 15:49

Zitat:

Original geschrieben von LLR
Wie Wildcards? Was hatn Tabelle1.PNR für einen Datentyp? String?
Und bei dem willst Wildcards benutzen - oder? (ich rate mal, falls es anders ist, schreib einfach ;))

Also wenn ich mit meiner Annahme recht haebn, dann sollte die Abfrage so ausschaun:

sql1 = "SELECT Tabelle1.PNR, Tabelle1.NAME, Tabelle1.VNAM, Tabelle1.KST FROM Tabelle1 WHERE (((Tabelle1.PNR) LIKE '*" & Variable & "*'))"

nein , es sind mitarbeitsnummern und da möchte ich mir alle Mitarbeiter anzeigen lassen (oder geht von-bis ?? das wäre natürlich am praktischten :) )

mfg

wbendl

06.07.2004 18:46

Hi!

Jetzt bin ich wieder in Amt und würden.

Wenn du alle DS willst, brauchst du eigentlich keine WHERE-Klausel

Alle DS:
sql1 = "SELECT PNR, NAME, VNAM, KST FROM Tabelle1"

Ein DS
sql1 = "SELECT PNR, NAME, VNAM, KST FROM Tabelle1 WHERE PNR = " & NumerischeVariable

DS aus einem bestimmten Bereich:
sql1 = "SELECT PNR, NAME, VNAM, KST FROM Tabelle1 WHERE PNR BETWEEN " & NumerischeVariable1 & " AND " & NumerischeVariable2

Noch ein Hinweis:
Wenn in der WHERE-Klausel eine Variable vom Typ String verwendet wird, muß sie in einfache Hochkommas (') gesetzt werden.

mfg

WB

Chrisi99

07.07.2004 07:33

ich habe jetzt zum ändern folgenden (praktischen ) code

Code:

Sub ändern()



Dim oAcc As New Access.Application

oAcc.OpenCurrentDatabase ("J:\PROJEKT\PIS\test\test.mdb")

oAcc.Visible = False 'fürs entwickeln angenehm

Dim db As DAO.Database

Dim rs As DAO.Recordset





Set db = oAcc.CurrentDb

Set rs = db.OpenRecordset("Autos", dbOpenDynaset)





    'einen Datensatz suchen

    rs.FindFirst "PNR=" & var & ""

    If rs.NoMatch Then

    'Nix gefunden

    Else

    'Datensatz ändern

    rs.Edit

    rs!Marke = car

    rs.Update

    End If

    rs.Close

    Set db = Nothing



[gekürzt]

kann ich dieses Konstrukt auch zum Auslesen verwenden? (damit ich ein einheitliches System habe)?

so gaaaanz langsam bekomm ich einen Plan von der Geschichte... aber nur langsam :D

mfg

wbendl

07.07.2004 08:55

Hi!

Bei dieser Version startest Access. Das ist aber unnötiger Aufwand.

Ich würde dir empfehlen, auch hier die ADO-Variante zu verwenden.
Ist einfacher.

Du mußt nur einen Cursor angeben, der das editieren der Daten erlaubt.

Einen einzelnen DS gleich im SQL-Statement auswählen.
Für mehrere DS die WHERE-Klausel weglassen, und das Recordset filtern.

mfg

WB

Chrisi99

07.07.2004 09:52

danke wbendl für die umfangreiche hilfe bisher!!

eine Frage hab ich jetzt noch ;)

mit der SQL-Anweisung wird ein Bestimmter Zellenbereich (Ax:Zy) mit Monatsumsätzen gefüllt. Über den Chartwizard

Code:

Charts.Add

    ActiveChart.ChartType = xlLine

    ActiveChart.SetSourceData Source:=Sheets("Tabelle1").Range("A1:B17"), PlotBy:=xlColumns

    ActiveChart.Location Where:=xlLocationAsObject, Name:="Tabelle1"

    With ActiveChart

        .HasTitle = True

        .ChartTitle.Characters.Text = "Monatsübersicht"

        .Axes(xlCategory, xlPrimary).HasTitle = False

        .Axes(xlValue, xlPrimary).HasTitle = False

    End With

wird dann aus diesem Bereich ein Diagramm erstellt.
Die Anzahl der Verkäufer kann sich aber ändern (wenn nach Kostestelle abgefragt wird). deshalb würde ich gerne den Tabellenbereich dynamisch (automatisch) vergrößern können.
füe die Letzte Zeile gibt es ja die Funktion

Code:

Range("A65536").End(xlUp).Offset(1, 0).Select

lr = ActiveWindow.RangeSelection.Address

und die letzte Spalte wird bei der Ausgabe durch eine Zählschleife auf der numerischen Variable "ss" festgelegt.

wie kann ich das jetzt in meine
Range("A1:B17")
einbauen?

es müsste dann ca so aussehen: Range("A1:ss[umgewandelt in Buchstaben]lr[umgewandelt in zahl]")
leider bekomm ich das nicht hin, und es scheint auch keine vorgefertigte Lösung zu geben (zumindest hab ich in google nichts entdeckt....

mfg

wbendl

07.07.2004 11:04

Eine Zelle oder einen Bereich kann man auch nur mit Zahlen definieren. Damit kann man numerische Variable direkt einsetzen.

Beispiel:
Range(Cells(Zeile1, Spalte1), Cells(Zeile2, Spalte2))

mfg

WB

Chrisi99

07.07.2004 11:38

Zitat:

Original geschrieben von wbendl
Eine Zelle oder einen Bereich kann man auch nur mit Zahlen definieren. Damit kann man numerische Variable direkt einsetzen.

Beispiel:
Range(Cells(Zeile1, Spalte1), Cells(Zeile2, Spalte2))

mfg

WB

so hab ich es auch schon versucht, leider kam da gleich eine neue lustige Fehlermeldung :(

ohne die "Cells(,)" geht es aber....

noch etwas ist mir eingefalle.
kann ich am ende einer Monatsabrechnung (aus der access-db) eine =SUMME(A1:A13) anhängen, und das für jedes monat extra (also in die Schleife einbauen die mir die Monatsbeträge ausspuckt)

mfg

wbendl

07.07.2004 12:00

Zu Cells:
Eventuell muß die Tabelle angegeben oder ausgewählt werden.
Ich hab diese Methode jedenfalls früher verwendet.

Zur Summe:
Wie die Formel in eine Zelle kommt, kannst du dir mit dem Makro-Recorder ansehen.
Du könntest die Summe aber auch über SQL aus der DB holen.

Es wäre übrigens möglich, die ganze Tabelle über eine SQL-Anweisung zu erstellen. Schau dir mal in Access eine Kreuztabellen-Abfrage an.

Ich hab einmal ein Tool für die Berechnung einer Gesamtwertung aus mehreren Radrennen gebastelt. Die Version mit der DB war ca. um den Faktor 100 schneller als die Version mit Excel und VBA.

mfg

WB

renew

07.07.2004 13:27

Zitat:

Dass geht!?

Cool, und ich hab mir, das letzte mal wie ich sowas gebraucht hab, extra eine Funktion gebastelt die von "A" weg mit ASCII Werte rechnen konnte und dann wieder auf die Buchstaben umgewandelt hat.... :heul: ;)

Chrisi99

07.07.2004 14:21

Zitat:

Original geschrieben von wbendl
Zu Cells:
Eventuell muß die Tabelle angegeben oder ausgewählt werden.
Ich hab diese Methode jedenfalls früher verwendet.

WB

es kommt der Fehler:

"Die Methode 'Cells' für das Objekt '_Global' ist fehlgeschlagen"

wie meinst du das aktivieren/Auswählen der Tabelle?

Sheets("Tabelle1").Activate
?

mfg

wbendl

07.07.2004 20:45

Poste mal den ganzen Code. Dann probier ich das selber.

Übrigens welche Version von Excel verwendest du?

mfg

WB

Chrisi99

07.07.2004 20:58

achtung lange Codewurst

Office 2002

Code:

Option Base 1

Global db As Database, rc As Recordset, rc1 As Recordset, rec As Recordset, rc2 As Recordset

Global aktuellesnodes As Variant

Global ss As Integer



Sub sql_umsatz()

Dim var As String

Dim w1 As Worksheet

Set w1 = Worksheets("monatsumsatz")

w1.Range("E3:K30").ClearContents

a = ActiveCell.Row

NR = ActiveSheet.Cells(a, 29).Value

Set db = DBEngine.Workspaces(0).OpenDatabase("J:\PROJEKT\PIS\test\test.mdb")

sql1 = "SELECT Mitarbeiter.PNR, Umsatz.NAME, Umsatz.VNAME,_"

Umsatz.[Umsatz/Jänner], Umsatz.[Umsatz/Februar], Umsatz.[Umsatz/März],_

Umsatz.[Umsatz/April], Umsatz.[Umsatz/Mai], Umsatz.[Umsatz/Juni],_

Umsatz.[Umsatz/Juli], Umsatz.[Umsatz/August], Umsatz.[Umsatz/September],_

Umsatz.[Umsatz/Oktober], Umsatz.[Umsatz/Novmeber], Umsatz.[Umsatz/Dezember]_

FROM Mitarbeiter INNER JOIN Umsatz ON Mitarbeiter.PNR=Umsatz.PNR"



Set rc1 = db.OpenRecordset(sql1)



On Error Resume Next

rc1.MoveFirst

ss = 2

i = rc1.RecordCount

w1.Select

While Not rc1.EOF

For anz = 1 To i

w1.Cells(3, ss).Value = rc1!PNR

w1.Cells(4, ss).Value = rc1!Name

w1.Cells(5, ss).Value = rc1!VNAME

w1.Cells(6, ss).Value = rc1!KST

w1.Cells(7, ss).Value = rc1![Umsatz/Jänner]

w1.Cells(8, ss).Value = rc1![Umsatz/Februar]

w1.Cells(9, ss).Value = rc1![Umsatz/März]

w1.Cells(10, ss).Value = rc1![Umsatz/April]

w1.Cells(11, ss).Value = rc1![Umsatz/Mai]

w1.Cells(12, ss).Value = rc1![Umsatz/Juni]

w1.Cells(13, ss).Value = rc1![Umsatz/Juli]

w1.Cells(14, ss).Value = rc1![Umsatz/August]

w1.Cells(15, ss).Value = rc1![Umsatz/September]

w1.Cells(16, ss).Value = rc1![Umsatz/Oktober]

w1.Cells(17, ss).Value = rc1![Umsatz/Novmeber]

w1.Cells(18, ss).Value = rc1![Umsatz/Dezember]

ss = ss + 1 'Zählvariable für die Schleife

rc1.MoveNext

Next anz

Wend

Call Chart

End Sub

Sub Chart()

'gibt die Tabelle aus

Dim lr As String

Dim lc As String





 Range("A65536").End(xlUp).Offset(1, 0).Select

 

 lr = ActiveWindow.RangeSelection.Address

 lc = ss

 

    'Range("A5:D18").Select

    Charts.Add

    ActiveChart.ChartType = xlLine

    ActiveChart.SetSourceData Source:=Sheets("monatsumsatz").Range("A7:B17"), PlotBy:=xlColumns 'Diese Zeile sollte automatisch

    'an das ende angepasst werden, wenn man zb nur die Daten von Januar bis März ausgeben lässt

    ActiveChart.Location Where:=xlLocationAsObject, Name:="monatsumsatz"

    With ActiveChart

        .HasTitle = True

        .ChartTitle.Characters.Text = "Monatsübersicht"

        .Axes(xlCategory, xlPrimary).HasTitle = False

        .Axes(xlValue, xlPrimary).HasTitle = False

    End With

End Sub

ps: kann man diese "Verweise" einmal über zB eine Globale Definition einbinden?? das nervt schon sehr das immer wieder anzuwählen!

mfg und Danke für die Hilfe (1000 mal) :)

wbendl

07.07.2004 22:05

Hab ich mich doch richtig erinnert. Die Tabelle muß vorher ausgewählt werden. (Würde ich als Bug bezeichnen)

Name des Diagramm-Blattes zur späteren Verwendung speichern:
dia = ActiveChart.Name

Tabellen-Blatt auswählen:
Sheets("Tabelle1").Select

Dann sollte es so funktionieren:
Diagramm-Blatt mit Namen ansprechen.
Sheets(dia).SetSourceData Source:=Range(Cells(7, 1), Cells(17, 2)), PlotBy:=xlColumns

Blätter mit Namen ansprechen ist immer gut. Wer weiß mitten im Code schon, was jetzt ActiveSheet ist?

Mit Probieren ist leider nichts. Also alle Angaben ungeprüft.

Was meinst du bezüglich der Verweise?

Noch ein paar Tips, für die Zukunft:
Globale Deklarationen sollte man so sparsam wie möglich verwenden. Bei dir sind wahrscheinlich keine notwendig. Um Variable mehreren Prozeduren zur Verfügung zu stellen, verwendet man Parameter.

Das Schlüsselwort Global sollte man auch nicht verwenden. Es genügt Dim im Deklarations-Abschnitt.

Außerdem möchte ich dir nochmal ADO ans Herz legen. Da würde ich dann eventuell die Connection Modulweit deklarieren.

mfg

WB

Alle Zeitangaben in WEZ +2. Es ist jetzt 03:37 Uhr.