PDA

Vollständige Version anzeigen : Aus Access Exceldaten mit Überschriften per VBA übernehmen


Zenturion
31.05.2012, 19:17
Hallo an die VBA Programmierer,

ich befasse mich seid 2 Monaten mit VBA und blicke so langsam durch.
Folgendes Problem habe ich und hoffe ihr könnt einen Anfänger wie mich Stützen wäre dankbar.

Ich habe eine Access2000 Datenbank mit den Namen db2.mdb mit
der Tabelle CQTS_REF_Data und in
Excel Sap.xls/ Tabelle1 sind die Quelldaten drin.
Ich kann aus Access die Daten aus Excel herholen der Code dafür ist so!


Option Compare Database
rem Verlinkung in Access wurde DAO 3.6 gewählt
Sub StapelExcelTeilImport()
' Verweis auf Microsoft DAO setzen
' Ab AC97
Dim sDatei As String, sPfad As String, AktDatei As String
Rem Access Tabellennamen
Const ZielTab = "CQTS_REF_Data"
Dim xlApp As Object ' Excel.Application
Dim rs As DAO.Recordset
Rem Zeillaufwerk
sPfad = "c:\"
Rem Zeildateinamen
sDatei = "sap.xls"
Rem setzen Accesstabelle mit Zeiltabelle
Set rs = CurrentDb.OpenRecordset(ZielTab)
' Excel vorbereiten
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
AktDatei = Dir(sPfad & sDatei)
Do Until AktDatei = ""
'Debug.Print AktDatei
' Import

'Exceldatei readonly öffnen
xlApp.Workbooks.Open sPfad & AktDatei, , True
rs.AddNew 'neuen Exceldatensatz
rs!C1_ACTUAL_DATE = xlApp.ActiveSheet.Cells(2, 1) ' E6
rs!C1_COMMUNICATED_DATE = xlApp.ActiveSheet.Cells(2, 2) ' E8
' ...
' ...
rs.Update
xlApp.ActiveWorkbook.Close
AktDatei = Dir
Loop
xlApp.Quit
Set xlApp = Nothing
End Sub

Ich würde gerne es abändern wo es die 20 Spalten mit oder ohne die Spaltenüberschriften von Excel und bis zum letzten Datensatz übernimmt doppelte Datensätze wegen inkonsistenz vermeiden und in Access geschriebn wird.
Beim nächsten Datenimport aus Access wieder das gleiche nur es soll ab der letzten leeren Zeile in Access reingeschriebn werden.

Bitte könnt ihr mir dabei helfen danke !

Albert;)

EmiliaM
31.05.2012, 20:51
Hallo Albert,

solche Importe macht man am besten, indem man die Excel-Tabelle in die Access-DB verknüpft. Wenn Pfad und Dateiname sich nicht ändern, kannst du das per Hand machen, es geht ähnlich, wie mit Access-Tabellen.

Danach kannst die Daten mittels Abfragen rüberschaufeln. Entweder direkt z.B. mit einer gruppierten SELECT, um Duplikate zu vermeiden, oder über einer zwischengeschalteten temporären Access-Tabelle, falls die Gruppierung zu kompliziert wäre oder noch sonstige Konvertierungen, Berechnungen o.ä. nötig sind.

Das hätte auch den Vorteil, dass es viel schneller wäre, als Loop über ein Recordset.

Beim nächsten Datenimport aus Access wieder das gleiche nur es soll ab der letzten leeren Zeile in Access reingeschriebn werden.

Nun ja - die leere Zeile, die du in Access siehst, ist gar keine, sie existiert nur auf dem Bildschirm. In einer Access-Tabelle gibt es keine "letzte leere Zeile" - es sei denn, du legst einen Datensatz mit lauter leeren Feldern an.

Und eine INSERT Anweisung, wie übrigens auch rs.AddNew, legt immer einen neuen Datensatz an.

Wenn du etwa die Reihenfolge meinst, also neue Sätze immer am Ende, dann musst du selber dafür mit expliziter Sortierung nach Datum o.ä. sorgen. Access zeigt zwar den Tabelleninhalt "sortiert" an, aber die Datensätze haben intern keinerlei Reihenfolge, da gibt es keine Gewähr.

Zenturion
31.05.2012, 21:31
Danke mein Freund dein Rat würde ich gerne ausprobieren, kannst du es mir die Schritte kurz Stichpunkt artig beschreiben.
Der Hintergrund der Sache ist das ich Täglich aus SAP eine Exceldatei generiere.
Dieses soll immer gleiche Spalten anzahl mit den gleichen Überschriften sein.
Der Inhalt ändert sich Natürlich.
Ich muss nur noch das ganze per Knopfdruck über Access die Daten aus Excel ins Access kopieren.
Das mit deinem Tipp gibt es hier einen Nachteil wie gesagt es muss Sicher sein.
Es darf kein Datendoppelungen geben oder Daten die vorhanden ist überschreiben.
Das ganze schreibe ich in 2000 in der Arbeit habe ich 2007.

Danke !

Zenturion
31.05.2012, 21:35
Ach ja noch was mit der Methode die du meinst mit dem kann ich nur auf einer Tabelle in Access beschreiben und nicht verteilen oder ?

Derzeit weis ich noch nicht ob ich das aus Exceldatensätze auf meherer Accesstzabellen verteilen sollte!

Danke deinen Feedback.

ebs17
31.05.2012, 22:05
SAP-Daten kommen in der Regel reichlich vor. Um so mehr sollte man der schnelleren Abfragelösung den Vorzug geben gegenüber einer Einzelübergabe per Schleife.
Bezüglich Verteilung der Daten auf mehrere Datenbanktabellen könntest Du Dich hieran orientieren: Importtabelle in m:n-Beziehung auflösen (http://www.dbwiki.net/wiki/Datei:AccSampleDivideTable.zip)

EmiliaM
01.06.2012, 13:22
Ich muss nur noch das ganze per Knopfdruck über Access die Daten aus Excel ins Access kopieren.

Du kannst den angepaßten Code aus der Beispieldatenbank hinter einen Button hinterlegen.

Das mit deinem Tipp gibt es hier einen Nachteil wie gesagt es muss Sicher sein.
Es darf kein Datendoppelungen geben oder Daten die vorhanden ist überschreiben.

Ich meine, getestete Abfragen sind um einiges sicherer als Code. Im Code kannst dich immer vertun, vor allem bei der Duplikatsuche. Hier ist die wichtige Frage: Wie definierst du eine Datendoppelung?

Hoffentlich hast du ein Feld oder eine Kombination von Feldern, die eindeutig sein muß? Wenn du das in der Abfrage berücksichtigst, ist es sicherer, als das im Code zu prüfen - von der Geschwindigkeit ganz zu schweigen.

Bez. Überschreiben: Eine INSERT Abfrage - auf deutsch Anfügeabfrage überschreibt gar nix, nie und nimmer. Sie schreibt nur neue Datensätze, was sonst in der Tabelle vorhanden ist, ist ihr Wurscht. Sie kann allerdings stolpern, wenn man in eindeutige Felder was einfügen will, was schon vorhanden ist.

Zum Verknüpfen der Excel-Tabelle:
- Datei /Externe Daten /Tabelle verknüpfen
- Datentyp unten auf Excel einstellen, Datei suchen
- den Assistenten durchklicken.

Zenturion
01.06.2012, 21:28
Servus einstweilen vielen dank für deine Hilfe!

Bei dieser Variante wenn ich das mal austesten möchte habe ich eine kleine Veränderung gemacht und es klappt wunderbar mit der Verhinderung der Datenverdoppelung mit der On error resume habe ich den Fehlermeldung übergangen gerade sowas passiert es wenn ich doppelte Datenin Excel habe.
In der Accesstabelle habe ich eine Spalte mit Index eingefügt und dieses auf Autowert gesetzt damit es die Datensätze schön ordentlich aufzählt aber manchmal passiert etwas unerwünschtes in access. Index wird mit 1 dann 2 dann 3 dann passiert etwas komisches es setzt 11 oder was anders warum kappiere nicht, wenn du da eine Idee hast wäre Super!

Der Code hier im ganzen nochmal nochmal!
was könnte man noch tun damit der Index in Access richtig inkrementiert,


Sub StapelExcelTeilImport()
Dim zeile As Long
For zeile = 2 To 5
' Verweis auf Microsoft DAO setzen
' Ab AC97
Dim sDatei As String, sPfad As String, AktDatei As String
Rem Access Tabellennamen
Const ZielTab = "CQTS_REF_Data"
Dim xlApp As Object ' Excel.Application
Dim rs As DAO.Recordset
Rem Zeillaufwerk
sPfad = "c:\"
Rem Zeildateinamen
sDatei = "sap.xls"
Rem setzen Accesstabelle mit Zeiltabelle
Set rs = CurrentDb.OpenRecordset(ZielTab)
' Excel vorbereiten
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
AktDatei = Dir(sPfad & sDatei)
Do Until AktDatei = ""
'Debug.Print AktDatei
' Import

'Exceldatei readonly öffnen
xlApp.Workbooks.Open sPfad & AktDatei, , True
rs.AddNew 'neuen Exceldatensatz
rs!C1_ACTUAL_DATE = xlApp.ActiveSheet.Cells(zeile, 1) ' Zeile aus Excel Spalte 1
rs!C1_COMMUNICATED_DATE = xlApp.ActiveSheet.Cells(zeile, 24) ' Zeile aus Excel Spalte 2
On Error Resume Next ' Wenn daten doppelt vorkommen dann umgehe
Err.Clear
' ...
' ...
rs.Update
xlApp.ActiveWorkbook.Close
AktDatei = Dir

Loop

Thomas Möller
02.06.2012, 09:39
Hallo!

Der Code hier im ganzen nochmal nochmal!

Eine kleine technische Anmerkung:
Kannst Du VBA-Code künftig bitte zwischen Code-Tags einfügen. Der Code ist dann einfach besser lesbar.

TIA

Zenturion
02.06.2012, 11:37
Thomas servus,

ja werd ich als Anfänger deinen Rat nachkommen.
Thomas kannst du bitte auf meine letzte Frage einen Tipp geben also in meinem Code an die richtige Stelle so einfügen etwas was nachdem Kopieren von Excel nach Access und so das die Index schön aufzählt und nicht durcheinander.
Bitte gib mir in der Hinsicht eine Hilfe.

Du hast vollkommen recht das mit der Struktruierung bei mir ist noch Fraglich werde mich bemühen.

Danke!

Albert

Zenturion
02.06.2012, 11:47
Hi Servus,

hab deinen Feedback nochmal durchgelesen!
Mein Problem nochmal ist:

Ich habe eine Tabelle in Access und mit Feldern das eine ist auf Autowert gesetzt und mit dem Namen ID in der Eigenschaft habe ich Inkrement eingestellt.
Aber nach Kopieren von Excel nach Access passiert mit selten das der ID in Access nicht richtig zählt wie z.B. beim ersten Kopiervorgang sind alle Daten in Access gut gelandet und wenn ich im Excel eine Datensatz ändere danach kopiere ins Access passiert etwas was ich nicht haben möchte.
So sieht es aus nach Import, an der letzten Zeile sollte ID mit 4 weitergehen und nicht 11.

ID C1_ACTUAL_DATE C1_COMMUNICATED_DATE
1 11.09.2011 11.10.2011
2 20.09.2011 11.08.2011
3 11.11.2011 09.10.2011
11 10.10.2011 08.10.2011

Bitte gebt mir einen verzweifelten Kerl einen Tipp!

Danke, Danke!!!

Thomas Möller
02.06.2012, 12:03
Hallo Albert,

Du hast vollkommen recht das mit der Struktruierung bei mir ist noch Fraglich werde mich bemühen.

Du kannst mal bitte Deinen Code etwas aufräumen. Verschieb mal alle Deklarationen an den Anfang der Prozedur. Dann wirst Du erkennen, dass in dem geposteten Code eine Schleife (For zeile = 2 To 5) zwar geöffnet wird, das zugehörige Next-Statement aber fehlt.

Vielleicht kannst Du den überarbeiteten Code dann hier noch einmal (in Code-Tags) posten.

CU

Zenturion
02.06.2012, 14:38
Ok ich versuche es mal
heute Abend noch.


Bis denne!

Zenturion
03.06.2012, 21:01
Hi wieder mal, hoffe gehe nicht auf die Nerven.

Das mit der Import und Abfrage und insert usw. kannst du mir bitte das ganze so einen Anfänger schritt für schritt wenn es dir keine Umstände bereitet erklären.

Bitte hilf mir wie ich es bei 2007 so nach deiner Tipp machen kann.
Ich würde es gerne auch mal so probieren.
Bei dieser Variante auch wie man Datenverdoppelungen verhindern kann.


Danke!

EmiliaM
04.06.2012, 02:18
Hallo Albert,

Excel verknüpfen in A2007:

- Ribbon "Externe Daten" -> Excel anklicken
- Assistent startet
- über "Durchsuchen" die Excel-Datei suchen, markieren, dann unten "Erstellen Sie eine Verknüpfung..." markieren, OK klicken
- gucken, ob richtiges Arbeitsblatt markiert ist, "Weiter" klicken
- gucken, ob Kästchen "Erste Zeile enthält Überschriften" richtig ist, "Weiter" klicken
- Tabelle ggf. benamsen, z.B. als tblSAP, abklicken.

Beispiel INSERT-Abfrage, wenn die 1. Zeile keine
Überschriften hatte (F1 u. F2 wären die Namen der Spalten in der verknüpften Excel-Tabelle):


INSERT INTO CQTS_REF_Data (C1_ACTUAL_DATE, C1_COMMUNICATED_DATE)
SELECT DISTINCT F1, F2 FROM tblSAP SAP
WHERE NOT EXISTS
(SELECT Null FROM CQTS_REF_Data CQ
WHERE CQ.C1_ACTUAL_DATE = SAP.F1 AND CQ.C1_COMMUNICATED_DATE = SAP.F2)


Soll heißen: füge alle Datensätze aus tblSAP in CQTS_REF_Data ein, die eine dort noch nicht vorhandene Kombination von C1_ACTUAL_DATE und C1_COMMUNICATED_DATE enthalten.

Bez. Lücken in der Nummerierung beim Autowert: Die werden im normalen Betrieb immer wieder vorkommen, wenn z.B. Datensätze gelöscht werden. Die ID der gelöschten Datensätze werden nicht neu vergeben (mit einer ausnahme, aber es ist schon sehr spät). Das ist auch kein problem, Autowerte sind nicht für die Benutzer gedacht und müssen nicht fortlaufend sein. Sie dienen dazu, Datensätze intern eindeutig zu kennzeichnen und die Datenbank im Innersten zusammen zu halten, und haben für den Benutzer sonst keine Bedeutung, sie sollten auch nicht in Formularen sichtbar sein.

Zenturion
04.06.2012, 20:25
Vilen Dank für die Ausfürhliche Hilfe Super von dir.
Ich probiere es aus dann melde ich mich wieder.

Danke
Danke

Albert