Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Access Excel Daten Import | Herbers Excel-Forum


Betrifft: Access Excel Daten Import von: Boris
Geschrieben am: 01.02.2010 14:30:22

Hallo,

ich verwende ein VBA-Makro, um Daten aus Access in Excel zu schreiben, gemäß folgendem Code:

Do While Not AccessRs.EOF
wksExcel.Cells(Row, 1).CopyFromRecordset AccessRs
Row = Row + 1
Loop

Ist nun in Access eine Spalte als Text 30 definiert, werden bei Einträgen die kürzer sind die fehlenden Zeichen aufgefüllt. In Excel erhalte ich als Ergebnis dann den eigentlichen Wert + x Leerzeichen.

Lässt sich das Kopieren dieser hinten angefügten Leerzeichen irgendwie unterbinden?

Importiere ich dieselben Daten über MS_Query, erhalte ich die Werte ohne aufgefüllte Leerzeichen.

Viele Grüße

  

Betrifft: AW: Access Excel Daten Import von: Luschi
Geschrieben am: 01.02.2010 19:12:34

Hallo Boris,

ich verstehe Deine Schleife nicht, da dies schon reicht: wksExcel.Cells(Row, 1).CopyFromRecordset AccessRs
Denn damit werden alle Datensätze mit allen Zeilen und Spalte in die Exceltabelle geschreiben, beginnend bei Zelle wksExcel.Cells(Row, 1). Die Schleife wird also nicht gebraucht.

Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: Access Excel Daten Import von: Boris
Geschrieben am: 02.02.2010 05:03:04

Hi Luschi,

guter Punkt, hehe, danke... lag wohl an meinem Halbwissen, ist jetzt aber vollkommen klar.
Weißt Du zufällig noch, wie ich die Leerzeichen wegbekomme?

Viele Grüße


  

Betrifft: AW: Access Excel Daten Import von: Luschi
Geschrieben am: 02.02.2010 13:52:32

Hallo Boris,

bei mir kommen da keine zusätzlichen Leerzeichen.
Zeiche doch mal ein bischen mehr Vba-Code.
Machst Du das mit DAO oder ADO?

Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: Access Excel Daten Import von: Boris
Geschrieben am: 02.02.2010 14:43:58

Ich denke mal ADO!? Siehe Code, das Skript wird aufgerufen, sobald ich in B2 per Dropdown-Menu eine neue strID auswähle:

(leider weiß ich nicht, wie man im Editor Codeanfang/-ende setzt)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$2" Then Exit Sub
    Dim strID As String
    Application.ScreenUpdating = False
    strID = "'" & Target.Value & "'"
    Import strID
    Application.ScreenUpdating = True
End Sub
Sub Import(ByVal strID As String)
Dim Row As Integer, Col As Integer
Dim lngCalcStatus As Integer
Dim wksExcel As Worksheet
Dim strProject As String, strDBFile As String
Dim strBereich As String
Dim AllSheets As Variant, CurrentSheet As Variant

Dim AccessCnn As New ADODB.Connection, AccessRs As New ADODB.Recordset, AccessSQL As String

AllSheets = Array(x, y, z) ' hier stehen die einzelnen Sheets

strProject = ThisWorkbook.Worksheets(1).Range("B6").Value
strDBFile = ThisWorkbook.Path & "\..\1 Report DB\" & strProject & " Report DB.mdb"

'Kalkulationseinstellungen abfragen und auf manuell setzen
lngCalcStatus = Application.Calculation
If lngCalcStatus <> -4135 Then Application.Calculation = xlCalculationManual

'Access-Verbindung aufbauen und DB-File öffnen
AccessCnn.Provider = "Microsoft.Jet.OLEDB.4.0"
AccessCnn.Open strDBFile

For Each CurrentSheet In AllSheets
Set wksExcel = ThisWorkbook.Worksheets(CurrentSheet)

strBereich = "A15:" & Split(wksExcel.UsedRange.Address, ":")(1)
wksExcel.Range(strBereich).Clear

AccessSQL = "Select * FROM " & CurrentSheet & " WHERE ID = " & strID
AccessRs.Open AccessSQL, AccessCnn, adOpenDynamic, adLockOptimistic

Row = 15
wksExcel.Cells(Row, 1).CopyFromRecordset AccessRs
Set AccessRs = Nothing
Next CurrentSheet

Set AccessRs = Nothing
Set AccessCnn = Nothing
Application.Calculation = lngCalcStatus

End Sub Zusätzlich zum Leerzeichenproblem würde ich gerne noch wissen, wie ich in Excel in Zeile 14 die Header des Rekordsets schreibe. Geht das auch per CopyFromRecordset oder brauche ich dafür eine "for col = 1 to accessrs.fields.count -> cells (14, col) = accessrs.fields(col -1).name"-Schleife?

Viele Grüße


  

Betrifft: AW: Access Excel Daten Import von: Luschi
Geschrieben am: 03.02.2010 11:01:07

Hallo Boris,

bei mir tritt das Problem der zusätzlichen Leerzeichen nicht auf.
Habe Dir mal ein Beispiel gemacht, wie ich eine Access-Abfrage in eine Excel-Tabelle einlese.
Entpacke die zip-Datei und belasse die mdb- und xls-Datei in einem gemeinsamen Ordner.
Auf dem 2. Tabellenblatt (TB) ist ein DAO-Lösung und im 3. TB eine ADODB-Lösung
Beachte bei ADODB auf den 'New'-Parameter im 'Dim'-Befehl (ist wichtig).
Auf dem 1. TB gibt es ein Beispiel zur Erstellung einer neuen mdb-Datei mittels ADOX
und dem Schreiben der Daten dieses TB's in die Access-Datei.

https://www.herber.de/bbs/user/67716.zip

Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: Access Excel Daten Import von: Boris
Geschrieben am: 03.02.2010 11:54:04

Hallo Luschi,

vielen Dank. Und was ist nun besser? DAO oder ADO? Im wesentlichen entspricht mein ADO-Code Deinem, wenn ich es richtig gesehen habe, oder siehst du grundsätzliche Unterschiede?

Hier noch ein paar Fragen:

1) 2 Variablen kann man problemlos hintereinander definieren (Dim AccessCnn As New ADODB.Connection, AccessRs As New ADODB.Recordset), oder?

2) Was macht Set AccRs = Nothing und AccRs.Close? Wenn ich wie bei mir eine Schleife durchlaufe, reicht es dann, wenn ich am Ende innerhalb der Schleife AccRs = Nothing setze und erst am Ende des Subs das Recordset und die Connection close?

3) Zurück zum eigentlichen Problem: In Deinem Feld Namen in der Export.mdb stehen immer nur so viele Zeichen wie geschrieben. In meinen Access-Tabellen sind fehlende Zeichen mit Leerzeichen aufgefüllt, dies kann ich auch nicht ändern, da Access von einem anderen Program beschrieben wird. Siehe schwarze Markierung links:



Kann man die Werte irgendwie GLÄTTEN? Vielleicht schon bei der Select-Anweisung? Wie schon vorher gesagt: importiere ich die selben Werte über MS-Query, werden die Leerzeichen automatisch entfernt.

Danke und viele Grüße...


  

Betrifft: AW: Access Excel Daten Import von: Luschi
Geschrieben am: 03.02.2010 12:35:41

Hallo Boris,

- ADO oder DAO,
Mit Office 2000 hat Microsoft ADODB eingeführt, um besseren Zugriff auf die Daten des MS-SQL-Servers zu erreichen und DAO sollte der Vergangenheit angehören. Auch ich glaubte an diese Idee und stieg auf ADO um. Wenn man allerdings nur innerhalb von Access programmiert, merkt man, daß man mit ADO mehr Vba-Code schreiben muß als mit DAO. Mit Office 2007 hat MS aber den DAO-Zugriff aufgebohrt und erweitert, so daß ich es jetzt wie folgt mache:
reine Access-Programmierung: DAO
Excel-Access bzw. Access-Excel: ADO
Zugriff auf SQL-Server: ADO, da es im Inet dazu mehr Beispiele gibt als zu DAO

- Set AccRs = Nothing und AccRs.Close
In AccRs stehen die Daten der eröffneten Abfrage und diese sollte man auch wieder schließen mit AccRs.Close
Immer wieder höre ich die Meinung das 'Set AccRs = Nothing' nicht erforderlich sei, da mit ,Abschluß der
Prozedur dieser Befehl automatisch vom Programm ausgeführt wird. Ich halte nicht viel von dieser Meinung, da sich Standard-Prozesse auch mal ändern können und dann wundert man sich, warum es auf einmal Fehlermeldungen gibt. Ich räume meine Vba-Prozedur auf, bevor sie verlassen wird. Dazu gehört, das man definierte Objektvariablen wieder entfernt.

- 2 Variablen kann man problemlos hintereinander definieren
Das ist selbstverständlich möglich. Man kann sogar am Zeilenende mit , _ die Definition von Variablen fortsetzen, ohne den Dim-Befehl zu wiederholen

Das mit den zusätlichen Zeichen in der Access-Tabelle ist natürlich doof.
Ich würde trotzdem .CopyFromRecordset benutzen und die Daten einzulesen und im Nachgang die Leerzeichen aus der Exceltabelle entfernen; bastle gerade an einem Beispiel.

Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: Access Excel Daten Import von: Luschi
Geschrieben am: 03.02.2010 12:53:29

Hallo Boris,

so wirst Du die Leerzeichen los (bezogen auf meine Beispieldatei for ADO:

Sub importFromAccess_ADO()
    Dim accConn As New adodb.Connection
    Dim accDB As String
    Dim accRS As New adodb.Recordset
    Dim excelRg As Range
    Dim accAbfrage As String
    Dim j As Long, lZeile As Long
    
    accDB = ThisWorkbook.Path & "\" & "Export.mdb"
    'erstellt eine SQL-Abfrage, die 6 Datenbankfelder berücksichtigt und nach FELD1 sortiert
    accAbfrage = "Select Datum, Kunde, Artikel, Anzahl, Preis, Total from [Export to Access]  _
order by Datum;"
    
    'Access-Datenbank-Verbindung
    accConn.Provider = "Microsoft.Jet.OLEDB.4.0"
    accConn.Open accDB
    
    accRS.Open accAbfrage, accConn, adOpenDynamic, adLockOptimistic
    
    With ActiveSheet
         'alles löschen in der aktiven Tabelle
         .Rows("1:" & .Cells(.Rows.Count, 2).End(xlUp).Row).Delete
         Set excelRg = .Range("B2")
    End With
    
    If accRS.EOF = True Then
       MsgBox "kein Datensatz gefunden"
       ActiveSheet.Range("B2").Select
    Else
       accRS.MoveFirst
    
       With ActiveSheet
          .Cells(1, 1).Value = "lfdNr."
          For j = 2 To 7
            .Cells(1, j).Value = accRS.Fields(j - 2).Name
          Next j

          excelRg.CopyFromRecordset accRS
          lZeile = .Cells(.Rows.Count, 2).End(xlUp).Row
          For j = 2 To lZeile
               .Cells(j, 1).Value = "'" & (j - 1) & "."
          Next j
       End With
    
    End If
    
    accRS.Close
    accConn.Close
    Set accRS = Nothing
    Set accConn = Nothing
    Set excelRg = Nothing
    Call loescheLZ(ActiveSheet, "C", 2, lZeile)
    Call loescheLZ(ActiveSheet, "D", 2, lZeile)
End Sub

Sub loescheLZ(ws As Worksheet, xSPalte As String, startZeile As Long, zielZeile As Long)
    Dim rg1 As Range, rg2 As Range
    
    Set rg1 = ws.Range(ws.Cells(startZeile, xSPalte), ws.Cells(zielZeile, xSPalte))
    For Each rg2 In rg1
        rg2.Value = Trim(rg2.Value)
    Next rg2
     
    Set rg1 = Nothing
    Set rg2 = Nothing
End Sub
Gruß von Luschi
aus klein-Paris


  

Betrifft: AW: Access Excel Daten Import von: Boris
Geschrieben am: 04.02.2010 09:00:09

Hallo Luschi,

zu ADO/DAO: dann verwende ich ADO, mein bestehendes Skript funktioniert ja ganz gut soweit...

Und das mit den Leerzeichen ist wirklich ziemlich dämlich. Ich denke, dass ich dein "loescheLZ-Sub" leider nicht verwenden kann, da es sich um mehr als 10 Tabellen mit teilweise 50 Spalten und 4000 Zeilen handelt und das Skript so schnell wie möglich sein muss. Im Moment benötigt es ca. 1 Sekunde.

Gibt es da wirklich keine andere Möglichkeit?

Aber auf jeden Fall schonmal vielen Dank für deine Antworten. Hast mir sehr geholfen...


Beiträge aus den Excel-Beispielen zum Thema "Access Excel Daten Import"