1. Liebe Forumsgemeinde,

    aufgrund der Bestimmungen, die sich aus der DSGVO ergeben, müssten umfangreiche Anpassungen am Forum vorgenommen werden, die sich für uns nicht wirtschaftlich abbilden lassen. Daher haben wir uns entschlossen, das Forum in seiner aktuellen Form zu archivieren und online bereit zu stellen, jedoch keine Neuanmeldungen oder neuen Kommentare mehr zuzulassen. So ist sichergestellt, dass das gesammelte Wissen nicht verloren geht, und wir die Seite dennoch DSGVO-konform zur Verfügung stellen können.
    Dies wird in den nächsten Tagen umgesetzt.

    Ich danke allen, die sich in den letzten Jahren für Hilfesuchende und auch für das Forum selbst engagiert haben. Ich bin weiterhin für euch erreichbar unter tti(bei)pcwelt.de.
    Dismiss Notice

Excel-Tabelle in bestehende Access-Tabelle

Discussion in 'Office-Programme' started by Steven82, Oct 9, 2007.

Thread Status:
Not open for further replies.
  1. Steven82

    Steven82 ROM

    Hallo Leute,

    ich bin erst neu hier im Forum und habe mal eine Frage!
    Ich habe in Access 2003 ein Formular erstellt mit dem ich bestimmte Excel-Dateien aus einem Verzeichnis in eine Access-Datenbank einlesen möchte! Ich erstelle eine Tabelle in Access, was auch klappt! Ich habe euch mal den Code des Formulars beigefügt!

    Code:
    Option Compare Database
Option Explicit

Dim PFAD As String
Dim Tabelle As String
Dim I As Long

Private Sub Auswahl_importieren_Click()

Dim arr As Variant
Dim iCounter As Integer
Dim strVerzeichnisName As String

PFAD = Me!Verzeichnis

If IsNull(PFAD) Or PFAD = "" Then
    MsgBox "Sie haben kein Verzeichnis ausgewählt, das Programm wird beendet!"
    DoCmd.CancelEvent
    Exit Sub
End If
    
Tabelle = InputBox(prompt:="Bitte geben Sie einen Namen für die Access-Tabelle " & _
"ein! Achten Sie hierbei auf die Internen-Tabellen, welche Namen schon vergeben sind!")
If IsNull(Tabelle) Or Tabelle = "" Then
    MsgBox "Sie haben keine Namen eingegeben, das Programm wird beendet!"
    DoCmd.CancelEvent
    Exit Sub
End If

'Tabelle wird erstellt
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute ("CREATE TABLE " & Tabelle & _
" ([Leitungsnummer] string, [Leitung Betriebstemp °C] string, [Abspannabschnitt] string, [von Mast] string, [Bauwerks-Nr] string, [bis Mast] string, [Bauwerk-Nr] string, [Objektart] string, [Höhe] string, [Station im Spannfeld] string, [Abstand vom linken Mast] string, [seitl Abstand (+ rechts) (- links)] string, [Berechn-temp °C] string, [Leiterseil] string, [Zugspannung N/mm^2] string, [Ist-Abstand] string, [VDE / EN Abstand] string, [Differenz Ist - Soll] string, [Art des Abstandes] string, [Datenauswertung durch Firma] string, [Bemerkung] string, [Erstellungsdatum] string )")
      
arr = Array("*Berechnungsnachweis.xls", _
"*Berechnungsnachweis*0.xls", "*Berechnungsnachweis*C.xls", "*Berechn*°.xls")
  
For iCounter = LBound(arr) To UBound(arr)
       With Application.FileSearch
       .NewSearch
       .LookIn = PFAD
       .SearchSubFolders = True
       .FileName = arr(iCounter)
       .Execute
       'If .Execute() > 0 Then
           'MsgBox "Es wurde(n) " & .FoundFiles.Count & " Datei(en) gefunden."
                   
           For I = 1 To .FoundFiles.Count

Insert into Tabelle

' Datei importieren und wenn "Umgewandelt" nicht vorhanden gehe zur nächsten Datei
On Error Resume Next
DoCmd.TransferSpreadsheet , , Tabelle, .FoundFiles(I), False, "Umgewandelt$"
If Err.Number <> 0 Then Err.Clear
On Error GoTo fehler 'und wieder die übliche Fehlerprozedur
            
       Next I
       
       'Else
            'MsgBox "Es wurden keine Excel-Dateien gefunden."

    'End If

    End With

    Next iCounter

MsgBox "Alle Excel-Dateien im Verzeichnis: " & PFAD & " eingelesen!"

'Fehlerprozedur
Exit_Proc:
  Exit Sub
fehler:
  MsgBox Err.Number & Err.Description
  Resume Exit_Proc
    
End Sub


Private Sub Verzeichnisauswahl_Click()
 
    Dim strVerzeichnisName As String
 
    If IsNull(Me!Verzeichnis) Then
        Me!Verzeichnis = ""
    End If
 
    strVerzeichnisName = VerzeichnisSuchen _
        ("Wählen Sie bitte das Verzeichnis aus!", Me!Verzeichnis)
 
    If ((Not IsNull(strVerzeichnisName)) And (strVerzeichnisName <> "")) Then
        Me!Verzeichnis = strVerzeichnisName
    End If
 
 
End Sub

Private Sub Ende_Click()

    DoCmd.Close
    
End Sub
    Wie kann ich nun die Daten aus Excel in die bestehende Access-Tabelle einlesen?
    Das Einlesen aus Excel in Access geht ja mit
    [CODEDoCmd.TransferSpreadsheet , , Tabelle, .FoundFiles(I), False, "Umgewandelt$"][/CODE]

    Ich habe auch schon die Suche benutzt und bin auf die Anfügeabfrage gestoßen, jedoch weiß ich nicht wie ich es bei mir machen muss!
    Könnt Ihr mir bitte für mein Beispiel helfen! :bitte:

    Vielen Dank schon Mal für Eure Hilfe!

    Steven
     
    Steven82, Oct 9, 2007
    #1
  2. brum

    brum Kbyte

    Du wirst hier ansetzen müssen. Hier wird eine neue Tabelle erstellt. Leider habe ich keine Erfahrung und kann Dir nicht sagen, wie's geht...

    Gruss
    Bruno
     
    brum, Oct 9, 2007
    #2
  3. Hascheff

    Hascheff Moderator

    Hallo Steven82,
    mit solchem Code hatte ich noch nicht zu tun, ich hoffe, dass ich alles richtig verstanden habe.

    Der Abschnitt
    Code:
    Tabelle = InputBox(prompt:="Bitte geben Sie einen Namen für die Access-Tabelle " & _
"ein! Achten Sie hierbei auf die Internen-Tabellen, welche Namen schon vergeben sind!")
    legt den Tabellennamen fest. Wenn du hier einen existierenden Namen eingibst und den Abschnitt
    Code:
    'Tabelle wird erstellt
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute ("CREATE TABLE " & Tabelle & _
" ([Leitungsnummer] string, [Leitung Betriebstemp °C] string, [Abspannabschnitt] string, [von Mast] string, [Bauwerks-Nr] string, [bis Mast] string, [Bauwerk-Nr] string, [Objektart] string, [Höhe] string, [Station im Spannfeld] string, [Abstand vom linken Mast] string, [seitl Abstand (+ rechts) (- links)] string, [Berechn-temp °C] string, [Leiterseil] string, [Zugspannung N/mm^2] string, [Ist-Abstand] string, [VDE / EN Abstand] string, [Differenz Ist - Soll] string, [Art des Abstandes] string, [Datenauswertung durch Firma] string, [Bemerkung] string, [Erstellungsdatum] string )")
    auskommentierst, weil ja keine neue Tabelle erstellt werden muss, sollte der Befehl
    Code:
    DoCmd.TransferSpreadsheet , , Tabelle, .FoundFiles(I), False, "Umgewandelt$"
    die Daten nicht in eine eben erst erstellte, sondern schon lange vorhandene Tabelle anfügen.

    Lange Rede, kurzer Sinn: DoCmd.TransferSpreadsheet setzt nach meinem Verständnis voraus, dass eine Tabelle existiert und führt sozusagen eine Anfügeabfrage aus.

    Ich hab bloß noch nicht verstanden, was
    Code:
    Insert into Tabelle
    bedeutet.

    Und: Wenn die Tabelle schon existiert und immer unverändert bleibt, macht die InputBox keinen Sinn, dann kannst du gleich Tabelle = "BlaBla" schreiben.
     
    Hascheff, Oct 9, 2007
    #3
  4. Steven82

    Steven82 ROM

    Hallo Hascheff,

    soory dass der Code nicht so leicht zu verstehen ist, wegen den wenigen Beschreibungen. :sorry:
    Ich will den Code benutzen, um aus einem bestimmten Verzeichnis (welches der Nutzer auswählen kann) alle darin enthaltenen Excel-Dateien (mit bestimmten Namen)
    Code:
    arr = Array("*Berechnungsnachweis.xls", _
"*Berechnungsnachweis*0.xls", "*Berechnungsnachweis*C.xls", "*Berechn*°.xls")
    in eine Access-Tabelle einzulesen.
    Ich hatte dies erst mit
    Code:
    DoCmd.TransferSpreadsheet , , Tabelle, .FoundFiles(I), True, "Umgewandelt$"
    gelöst!
    Wobei eine Tabelle in Access angelgt wird mit den Daten aus den Excel-Dateien. Jedoch sind nicht alle Daten in die Access-Tabelle übertragen worden, weil die Excel-Tabellen nicht richtig Formatiert sind! (Manchmal Zahlen als Text eingegeben usw.)
    Deshalb benötige ich eine Access-Tabelle in der die Feldtypen als Text formatiert weden! Darum lege ich mit
    Code:
    'Tabelle wird erstellt
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute ("CREATE TABLE " & Tabelle & _
" ([Leitungsnummer] string, [Leitung Betriebstemp °C] string, [Abspannabschnitt] string, [von Mast] string, [Bauwerks-Nr] string, [bis Mast] string, [Bauwerk-Nr] string, [Objektart] string, [Höhe] string, [Station im Spannfeld] string, [Abstand vom linken Mast] string, [seitl Abstand (+ rechts) (- links)] string, [Berechn-temp °C] string, [Leiterseil] string, [Zugspannung N/mm^2] string, [Ist-Abstand] string, [VDE / EN Abstand] string, [Differenz Ist - Soll] string, [Art des Abstandes] string, [Datenauswertung durch Firma] string, [Bemerkung] string, [Erstellungsdatum] string )")
    eine Tabelle in Access an!

    Wie kann ich nun die Exel-Dateien in diese gerade erstellte Tabelle einfügen?
    Hatte in der Suche im Internet den Code
    Code:
    Insert into
    gefunden und gedacht damit irgendwie die Daten zu übertragen. Jedoch weiß ich nicht wie!
    Also wie bekomme ich die Daten in die Excel-Tabelle?
     
    Steven82, Oct 9, 2007
    #4
  5. Hascheff

    Hascheff Moderator

    Du meinst sicher "in die Access-Tabelle".
    Da hab ich dich falsch verstanden. Ich dachte, der Code funktioniert so weit, nur dass du keine neue Tabelle erstellen willst, sondern an eine bestehende die Daten anfügen willst.
    Das Problem lässt sich zwar auch nach dem Import in Access lösen, aber auch vorher in Excel. Da es in Excel mehrere Tabellen sind, die Namen vielleicht auch immer wieder anders, ist die Lösung in Access wohl sinnvoller.
    Ich würde die Tabelle einfach Temp nennen. Nach dem Import werden die Daten der Tabelle Temp dann durch eine Abfrage aufbereitet (also z.B. mit WERT oder FORMAT wird aus Text wieder eine Zahl gemacht) und diese Abfrage wird gleich als Anfügeabfrage für die eigentliche Zieltabelle gestaltet und im Code aufgerufen.

    Aber eins nach dem anderen. Zuerst muss der Import klappen.
    Hast du wenigstens in Excel immer die gleichen Spalten?
    Passiert es aus sachlichen Gründen oder durch Fehleingaben, dass Zahlen als Text eingegeben werden? Wollen wir erst mal versuchen, die Fehleingaben/Benutzerfehler zu verhindern?
    "Insert into" ist SQL-Code, im VBA-Code hat das meines Wissens nichts zu suchen. Lösch die Zeile, dann könnte der Code prinzipiell funktionieren.
     
    Hascheff, Oct 9, 2007
    #5
  6. Steven82

    Steven82 ROM

    Hallo Hascheff,

    Ja ich meine natürlich Access-Tabelle! Der Code funktioniert auch so weit, der Nutzer kann ein Verzeichnis auswählen und alle Excel-Dateien werden in eine Access-Tabelle eingelesen. Dies geschieht mit folgendem Code!

    Code:
    Option Compare Database
Option Explicit

Dim PFAD As String
Dim Tabelle As String
Dim I As Long

Private Sub Auswahl_importieren_Click()

Dim arr As Variant
Dim iCounter As Integer
Dim strVerzeichnisName As String

PFAD = Me!Verzeichnis

If IsNull(PFAD) Or PFAD = "" Then
    MsgBox "Sie haben kein Verzeichnis ausgewählt, das Programm wird beendet!"
    DoCmd.CancelEvent
    Exit Sub
End If
    
Tabelle = InputBox(prompt:="Bitte geben Sie einen Namen für die Access-Tabelle " & _
"ein! Achten Sie hierbei auf die Internen-Tabellen, welche Namen schon vergeben sind!")
If IsNull(Tabelle) Or Tabelle = "" Then
    MsgBox "Sie haben keine Namen eingegeben, das Programm wird beendet!"
    DoCmd.CancelEvent
    Exit Sub
End If
     
arr = Array("*Berechnungsnachweis.xls", _
"*Berechnungsnachweis*0.xls", "*Berechnungsnachweis*C.xls", "*Berechn*°.xls")
  
For iCounter = LBound(arr) To UBound(arr)
       With Application.FileSearch
       .NewSearch
       .LookIn = PFAD
       .SearchSubFolders = True
       .FileName = arr(iCounter)
       .Execute
       'If .Execute() > 0 Then
           'MsgBox "Es wurde(n) " & .FoundFiles.Count & " Datei(en) gefunden."
                   
           For I = 1 To .FoundFiles.Count

' Datei importieren und wenn "Umgewandelt" nicht vorhanden gehe zur nächsten Datei
On Error Resume Next
DoCmd.TransferSpreadsheet , , Tabelle, .FoundFiles(I), False, "Umgewandelt$"
If Err.Number <> 0 Then Err.Clear
On Error GoTo fehler 'und wieder die übliche Fehlerprozedur
            
       Next I
       
       'Else
            'MsgBox "Es wurden keine Excel-Dateien gefunden."

    'End If

    End With

    Next iCounter

MsgBox "Alle Excel-Dateien im Verzeichnis: " & PFAD & " eingelesen!"

'Fehlerprozedur
Exit_Proc:
  Exit Sub
fehler:
  MsgBox Err.Number & Err.Description
  Resume Exit_Proc
    
End Sub


Private Sub Verzeichnisauswahl_Click()
 
    Dim strVerzeichnisName As String
 
    If IsNull(Me!Verzeichnis) Then
        Me!Verzeichnis = ""
    End If
 
    strVerzeichnisName = VerzeichnisSuchen _
        ("Wählen Sie bitte das Verzeichnis aus!", Me!Verzeichnis)
 
    If ((Not IsNull(strVerzeichnisName)) And (strVerzeichnisName <> "")) Then
        Me!Verzeichnis = strVerzeichnisName
    End If
 
 
End Sub

Private Sub Ende_Click()

    DoCmd.Close
    
End Sub
    Doch wie gesagt entstehen manchmal Importfehler und es werden nicht alle Daten übertragen!

    Deshalb will ich ja eine neue leere Tabelle in Access erstellen (nur mit den Spaltenbeschriftungen und der Formatierung als Text).
    Dies mache ich mit folgendem Code:
    Code:
    'Tabelle wird erstellt
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute ("CREATE TABLE " & Tabelle & _
" ([Leitungsnummer] string, [Leitung Betriebstemp °C] string, [Abspannabschnitt] string, [von Mast] string, [Bauwerks-Nr] string, [bis Mast] string, [Bauwerk-Nr] string, [Objektart] string, [Höhe] string, [Station im Spannfeld] string, [Abstand vom linken Mast] string, [seitl Abstand (+ rechts) (- links)] string, [Berechn-temp °C] string, [Leiterseil] string, [Zugspannung N/mm^2] string, [Ist-Abstand] string, [VDE / EN Abstand] string, [Differenz Ist - Soll] string, [Art des Abstandes] string, [Datenauswertung durch Firma] string, [Bemerkung] string, [Erstellungsdatum] string )")
    Das funktioniert auch!

    Meine Frage ist nun, wie bekomme ich nun die Excel-Dateien in diese erstellte Tabelle rein?

    Die Tabellen in Excel haben alle den gleichen Aufbau! Die Formatierungsfehler passieren teilweise mit Absicht und teilweise unbeabsichtigt! Jedoch kann ich die Excel-Dateien nicht alle ändern, da es sehr sehr viele sind!
     
    Steven82, Oct 10, 2007
    #6
  7. Hascheff

    Hascheff Moderator

    Hallo Steven82,

    ich würde vorschlagen, dass wir parallel an mehreren Fronten arbeiten, sonst zieht sich die Problemlösung zu lange hin. Ausgangslage ist die Existenz eines Ordners (auch mit Unterordnern möglich), in dem sich die Excel-Tabellen befinden. Dieser Ordner ist in Me!Verzeichnis eingetragen. Die Daten sollen in eine Access-Tabelle, ich nenne sie mal Zieltabelle.

    Die Aufgabe gliedert sich in vier Schritte:

    I. Import aus den Excel-Tabellen in eine Access-Zwischentabelle, ich nenne sie Temp.

    II. Aufbereitung der Daten der Tabelle Temp: Konvertierung oder was sonst noch so anfällt. Es könnten z.B. Dubletten ausgefiltert werden oder die Datensätze sortiert werden. Dies geschieht durch einen SQL-Befehl, der eine Auswahlabfrage darstellt.

    III. Überführung der Datensätze aus Temp in Zieltabelle. Dies geschieht durch einen SQL-Befehl, der eine Anfügeabfrage ausführt.

    IV. Löschen der Datensätze in der Tabelle Temp, damit die Tabelle für den nächsten Import bereit ist. Dies geschieht durch einen SQL-Befehl, der eine Löschabfrage ausführt.

    II und III können zusammengelegt werden, wenn wir fertig sind.
    Alle Aufgaben werden am Ende durch einen Klick auf die Schaltfläche "Auswahl_importieren" ausgelöst. Während der Erarbeitung des Codes würde ich jede Aufgabe durch eine eigene Schaltfläche lösen, damit man zwischen den Schritten kontrollieren kann.

    Die Tabelle Temp selbst muss nicht gelöscht, also auch nicht durch
    Code:
    'Tabelle wird erstellt
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute ("CREATE TABLE Temp " & _
" ([Leitungsnummer] string, [Leitung Betriebstemp °C] string, [Abspannabschnitt] string, [von Mast] string, [Bauwerks-Nr] string, [bis Mast] string, [Bauwerk-Nr] string, [Objektart] string, [Höhe] string, [Station im Spannfeld] string, [Abstand vom linken Mast] string, [seitl Abstand (+ rechts) (- links)] string, [Berechn-temp °C] string, [Leiterseil] string, [Zugspannung N/mm^2] string, [Ist-Abstand] string, [VDE / EN Abstand] string, [Differenz Ist - Soll] string, [Art des Abstandes] string, [Datenauswertung durch Firma] string, [Bemerkung] string, [Erstellungsdatum] string )")
    erstellt werden. Führe den Code einmal aus (Beachte die Änderung hinter "CREATE TABLE"!), um die Tabelle zu erstellen oder erstelle sie von Hand.
    Alles, was hinter "cnn.Execute" in Klammern steht, ist ein SQL-Befehl. So ähnlich musst du dir auch die SQL-Befehle zu den Aufgaben II bis IV vorstellen. SQL-Code wird ja in Access erstellt, indem zunächst eine Abfrage erstellt wird. (Ich geh mal davon aus, dass du schon Abfragen erstellt hast. (auch Anfüge- und Löschabfragen?)) Erst wenn die Abfrage funktioniert, schaltet man in die SQL-Ansicht um und kopiert (vereinfacht gesagt) den SQL-Code in die Klammern von cnn.Execute.

    (cnn.Execute hab ich noch nicht benutzt, ich habe stattdessen DoCmd.RunSQL verwendet. aber bleib ruhig bei cnn.Execute, da es ja schon funktioniert hat. Ist auch eleganter.)

    Erstelle für II bis IV je eine Schaltfläche und schreib den Code
    Code:
    'Kommentar
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
cnn.Execute ("")
    hinein. (Erst testen, wenn zwischen "" was steht.)

    Soweit die Vorrede, nun wird es konkret:
    _______________________________________

    zu I.
    Das haben wir ja fast fertig. Da der Name Temp für die Access-Tabelle feststeht, kann der Code etwas vereinfacht werden:
    Der Abschnitt
    Code:
    Tabelle = InputBox(prompt:="Bitte geben Sie einen Namen für die Access-Tabelle " & _
"ein! Achten Sie hierbei auf die Internen-Tabellen, welche Namen schon vergeben sind!")
If IsNull(Tabelle) Or Tabelle = "" Then
    MsgBox "Sie haben keine Namen eingegeben, das Programm wird beendet!"
    DoCmd.CancelEvent
    Exit Sub
End If
    kann weggelassen werden. Stattdessen änderst du den Befehl "DoCmd.TransferSpreadsheet" so ab:
    Code:
    DoCmd.TransferSpreadsheet , , "Temp", .FoundFiles(I), False, "Umgewandelt$"
    Ich hoffe, das nun keine Importfehler mehr passieren.
    Unabhängig davon machst du dich an die Aufgaben II bis IV, sobald Datensätze in Temp sind.
    _______________________________________

    zu II.
    Erstelle eine Auswahlabfrage, die alle Felder von Temp enthält.
    Für die zu konvertierenden Felder benutzt du den Assistenten. Mal als Beispiel: Ein Feld "Menge" wird so von Text in Zahl konvertiert:
    Code:
    Menge: Wert([Temp!Menge])
    Ich nenn die Abfrage mal "Konvertierung". (Der Name tut nichts zur Sache, wenn der Code fertig ist, werden alle Abfragen gelöscht.)
    _______________________________________

    zu III.
    Erstelle eine Anfügeabfrage (anfügen an "Zieltabelle"), die alle Felder von Konvertierung enthält.
    _______________________________________

    zu IV.
    Erstelle eine Löschabfrage, die alle Datensätze von Temp löscht.
    _______________________________________

    Die Abfragen kannst du alle testen.

    PS: Ich hab alles nicht getestet. Kann schon sein, dass was nicht funktioniert oder unvollständig beschrieben ist. Frag ruhig!
     
    Hascheff, Oct 10, 2007
    #7
Thread Status:
Not open for further replies.

Share This Page