Access Excel Daten Import | Herbers Excel-Forum

Forumthread: Access Excel Daten Import

Access Excel Daten Import

01.02.2010 14:30:22

Boris

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

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Access Excel Daten Import

01.02.2010 19:12:34

Luschi

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

AW: Access Excel Daten Import

02.02.2010 05:03:04

Boris

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

AW: Access Excel Daten Import

02.02.2010 13:52:32

Luschi

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

AW: Access Excel Daten Import

02.02.2010 14:43:58

Boris

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

AW: Access Excel Daten Import

03.02.2010 11:01:07

Luschi

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

AW: Access Excel Daten Import

03.02.2010 11:54:04

Boris

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:
Userbild

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...

AW: Access Excel Daten Import

03.02.2010 12:35:41

Luschi

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

AW: Access Excel Daten Import

03.02.2010 12:53:29

Luschi

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

AW: Access Excel Daten Import

04.02.2010 09:00:09

Boris

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...

Beliebteste Forumthreads (12 Monate)

Access Excel Daten Import | Herbers Excel-Forum

Forumthread: Access Excel Daten Import

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread