Office: Export in Excel per ADO

Hallo Franzel,

dann heißt deine Tabelle nicht 'Tabelle1', oder der Dateiname ist falsch!

Die zwei '3', sind Paramterer für das Recodset (Cursortype, Locktype).

Du solltest immer deinen gesamten verwendeten Code zeigen, sonst lassen sich etwaige Fehler nur erahnen!

Sonst lade mal eine Beispieldatei hoch und beschreibe, was von wo nach wo geschrieben werden soll.

Warum muss das bei geschlossener Datei erfolgen?

Gruß Sepp

 

Sepp,

you made my Day!! Vielen Dank für deine tolle, schnelle
und sehr kompetente Hilfe!!! *boah

Die Prozedur funktioniert perfekt. Zuvor war noch ein kleiner Fehler mit dem Verzeichnis und die Datei konnte nicht gefunden werden.

Der Zugriff mit ADO auf eine geschlossene Datei wähle ich, weil ich glaube,
dass das perfomanter ist. Wenn ich mich täusche, bitte ich um Aufklärung.

Wenn man jetzt statt einem Datensatz viele Datensätze einer Quell-Datei in die Zieltabelle "Tabelle1" schreiben möchte, wie müßte der Code aussehen?

Folgender Code funktioniert leider nicht.

(letztezeile ist die letzte Zeile der Quell-Tabelle)
Code:

 

Hallo Franzel,

das geht so. (Habe den Code noch etwas umgestellt und ein paar Prüfungen eingebaut!)

' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub updateADO()
Dim objADO As Object
Dim strFile As String, strTable As String, strRange As String
Dim strSQL As String, strCon As String
Dim lngMax As Long, lngIndex As Long, lngLast As Long
Dim varFields As Variant

strFile = "E:\Forum\ado_test.xlsx" 'DateiPfad und name - Anpassen!
strTable = "Tabelle1" 'Tabellenname - Anpassen
strRange = "A1:F10000" 'Bereich - Anpassen!

'erstes Array = Feldnamen (Überschriften)
varFields = Array("Datum", "Wert 1", "Wert 2", "Text")

If IsFile(strFile) Then 'Prüfen, ob Datei existiert
If ADOSheetExists(strFile, strTable) Then 'Prüfen, ob Tabelle in Datei vorhanden
If Right(strFile, 3) = "xls" Then
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;" & "Data Source=" & strFile & ";"
ElseIf Right(strFile, 4) = "xlsx" Or Right(strFile, 4) = "xlsm" Then
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Extended Properties=""Excel 12.0;HDR=YES"";" & "Data Source=" & strFile & ";"
Else
Exit Sub
End If

Set objADO = CreateObject("ADODB.Recordset")

lngLast = Application.Max(4, Cells(Rows.Count, 2).End(xlUp).Row)

With objADO

strSQL = "SELECT * FROM [" & strTable & "$" & strRange & "]"

.Open strSQL, strCon, 3, 3

For lngIndex = 4 To lngLast
.AddNew varFields, Array("31.12.2015", Cells(lngIndex, 2).Value, Cells(lngIndex, 3).Value, "Neuer Eintrag")
Next

.Update

.Close
End With
Else
MsgBox "Die Tabelle '" & strTable & "' wurde in der Datei '" & strFile & "' nicht gefunden!", vbExclamation
End If
Else
MsgBox "Die Datei '" & strFile & "' wurde nicht gefunden!", vbExclamation
End If

Set objADO = Nothing
End Sub

Private Function IsFile(ByVal FileName As String) As Boolean
On Error Resume Next
IsFile = ((GetAttr(FileName) And vbDirectory) <> vbDirectory)
End Function

Private Function ADOSheetExists(ByVal FileName As String, ByVal SheetName As String) As Boolean
'original Function 'GetSheetNames' by Bob Phillips, adapted by j.ehrensberger
Dim objADO As Object, objCAT As Object, objTAB As Object
Dim lngI As Long, intL As Integer, intP As Integer, intS As Integer
Dim strCon As String, strTab As String
Dim vntTmp() As Variant

If Mid(FileName, InStrRev(FileName, ".") + 1) = "xls" Then
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Extended Properties=Excel 8.0;" & _
"Data Source=" & FileName & ";"
ElseIf Mid(FileName, InStrRev(FileName, ".") + 1) Like "xls?" Then
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Extended Properties=""Excel 12.0;HDR=YES"";" _
& "Data Source=" & FileName & ";"
Else
Exit Function
End If

Set objADO = CreateObject("ADODB.Connection")
objADO.Open strCon
Set objCAT = CreateObject("ADOX.Catalog")
Set objCAT.ActiveConnection = objADO

For Each objTAB In objCAT.Tables
strTab = objTAB.Name
intL = Len(strTab)
intP = 0
intS = 1
'Worksheet name with embedded spaces enclosed by single quotes
If Left(strTab, 1) = "'" And Right(strTab, 1) = "'" Then
intP = 1
intS = 2
End If
'Worksheet names always end in the "$" character
If Mid$(strTab, intL - intP, 1) = "$" Then
Redim Preserve vntTmp(lngI)
vntTmp(lngI) = Mid$(strTab, intS, intL - (intS + intP))
lngI = lngI + 1
End If
Next objTAB

If lngI > 0 Then ADOSheetExists = IsNumeric(Application.Match(SheetName, vntTmp, 0))

objADO.Close
Set objCAT = Nothing
Set objADO = Nothing
End Function


Gruß Sepp

 

ADO ist "nur" eine Art, mit einer Datenbank (hier Excelmappe) zu verknüpfen. Eine Alternative wäre DAO (dieses ist in vielem vergleichbar zu ADO und optimiert in Access-Datenbanken). Diese Verknüpfung könnte man jetzt etwa vergleichen mit dem Öffnen einer Excelmappe.
Wenn man nun die neue Mappe in der gleichen vorhandenen Excelinstanz öffnet, dürfte das auch schnell gehen.

Der zweite zu betrachtende Teil ist dann die eigentliche Datenverarbeitung, hier also die Übertragung von Zeilen/Datensätzen. Hier darf man, unabhängig von der Verknüpfungsart, davon ausgehen, dass Massendatenverarbeitung eine Einzelwertverarbeitung performancetechnisch schlägt. Also: Das Übertragen eines ganzen (größtmöglichen) Datenblocks im Stück geht schneller als das einzelne Übertragen der enthaltenen Elemente.

Nun sollte man auch betrachten, wieviel man von wo nach anders wo überträgt. Bei ernstzunehmenden Datenmengen wird der Hauptanteil der benötigten Zeit durch den zweiten Part beansprucht, der konstante Part der Verknüpfung (bzw. Bereitstellung der Zielmappe) tritt in den Hintergrund.

Somit würde man beim Kopieren von einem Tabellenblatt in ein anderes nicht Zeile für Zeile oder gar Wert für Wert kopieren, sondern den gesamten relevanten Bereich auf einmal. Gleichlautend würde man per SQL nicht Datensatz für Datensätz anfügen, sondern die gesamte Datensatzgruppe auf einmal. Dann müsste man aber auch die Quelltabelle im Stück fassen und nicht per ActiveCell-Gewurschtel.

In unteren Datenmengenbereichen dürfte es dann umgedreht ziemlich egal sein, was man macht (wenn es nicht gar zu ungeschickt ist), weil sich da nirgendwo eine spürbare Verzögerung zeigen wird. Sprich, da wird man die Methode wählen, die man gut kennt und mit der man sich wohl fühlt.

 

Hallo Sepp,

ich weiß gar nicht was ich sagen soll ...das ist einfach göttlich, was du da
aus dem Hut gezaubert hast. *boah Tausend Dank hierfür!
Ich werde mir heute viel Zeit nehmen, um deinen Code zu analysieren und zu verstehen.
Sepp, gehuldigt werden sollen deine herausragenden Geistes-Taten!! *boah


Hallo Eberhard,

auch dir vielen Dank für deine Erklärung!

Wenn man z.B. eine Tabelle mit ca. 20 Datensätze täglich in eine zentrale Monatsdatei übertragen möchte (die pro Tag also um ca. 20 Datensätze anwächst und am Monatsanfang wieder bei 0 Datensätze ankommt). Beide Dateien befinden sich auf dem selben Rechner.
Welches Verfahren würdet ihr mir empfehlen? Eberhardt, du meinst, man könnte auch einfach mit einem Workbook.open und dann einfach die Daten übertragen?
Ab welcher Dateigröße oder besser gesagt ab wieviel Datensätzen in einer Tabelle macht Datenübertragung mit ADO wirklich Sinn?

Einen ehrfürchtigen Gruß,
Euer Franzel

 

DAO ist gegenüber ADO performanter das es Office-spezifisch ist.

Ich hatte mal ein Paar Tests gefahren wobei der Import aus einer anderen Excel-Datei per ADO ewig dauerte. Dagegen das Öffnen Übertragen und wieder Schließen um Einiges schneller war.

Ich würde beim Öffnen-Übertragen-Schließen bleiben.

EDIT:
Per ADO lassen sich in einer Excel-Datei zwar Datensätze neu anlegen und ändern aber nicht löschen.

 

Wenn Du ADO-Aktionen schneller schreibst und verstehst als Excel-Copy&Paste, dann auch schon sofort. Dürfte aber eher die Ausnahme sein.
Oder aber Du überträgst in eine richtige Datenbanktabelle. Dort ist Copy&Paste keine ernstzunehmende Methode.

Ansonsten bietet SQL (das ist der entscheidende Baustein) Methoden der Massendatenverarbeitung. Von Massen könnte man reden ab Bereichen von mehreren Tausend bis Millionen (=> Potential), darf aber auch weniger sein.

Dabei darf man aber SQL nicht auf das bloße Übertragen reduzieren. Es könnte ja der Fall auftreten, dass Du Deine 20 Datensätze basierend auf allerlei Verknüpfungen mit zusätzlichen Tabellen, Filterungen und Gruppierungen und Aggregierungen aus einer Tabelle von bspw. 60.000 Datensätzen auswählen und berechnen müsstest. In der Zieltabelle wäre vielleicht zusätzlich zu prüfen, dass die einzutragenden Datensätze neu sind und es somit zu keinen Duplikaten kommt.
An dieser Stelle kann SQL hochfunktionell und performant eingesetzt werden.

 

Hallo Sepp,

ich habe Deinen Code aus #18 in eines meiner Projekte eingebaut. Das Ganze funktioniert wunderbar, solange die Tabelle, in der die Daten
per RecordSet angefügt werden sollen, weniger als 65536 Zeilen hat - sind es mehr, kommt die doofe Fehlermeldung (siehe Anhang).
Dabei spielt es keine Rolle, ob ich Excel-32bit 2010/13/16 verwende und die beiden Dateien vom Typ *.xlsm / xlsx sind.

Gruß von Luschi
aus klein-Paris

PS: braucht man noch einen anderen Provider als:
"Provider=Microsoft.ACE.OLEDB.12.0;..." ?

 

Hallo Luschi,

kann ich nicht bestätigen, solange die Ziel-Datei geschlossen ist, bei geöffneter Datei kommt bei mir die Meldung "Tabelle ist voll".

Gruß Sepp

 

Hallo Sepp,

ich bin immer noch am staunen wie du so schnell und genial den Code
geschrieben hast. Meiner Ansicht trägst du den Schwarz-Gurt in der VBA-Programmierung.

Wenn der Meister erlaubt, hätte ich eine kleine Frage.
Kann man mit ADO auch blockweise Daten exportieren, also z.B. einen Range("A1:B20")?
Wenn ja, wie könnte der Code dazu aussehen?


Viele Grüße,
Franzel

 

Hallo Franzel,

Meister bin ich bei weitem keiner, Excel und VBA ist nur ein Hobby.

Ob eine Blockweise Übertragung möglich ist, kann ich nicht verneinen, mir ist es aber noch nicht gelungen. Ich würde es per Schleife machen.

Gruß Sepp

 

Sepp,

vielen Dank für deine Antwort. Somit kann ich jetzt, mit gutem Gewissen, das Problem nach deinem Lösungsvorschlag angehen. Danke noch mal für deine sehr hilfreiche Unterstützung.

Viele Grüße,
Franzel

P.S.:
Wenn Excel und VBA nur deine Hobbys sind ...würde mich interessieren, was denn dein Beruf ist bzw. deine Hauptbeschäftigung ist?
Ich hoffe, dass ich mit der Frage nicht zu persönlich werde.

 

\@josef e

der Provider hängt doch von der Excelversion der Anwendung ab, in der dieser Code gespeichert.

Hier müsste doch die Excleversion abgefragt werden, oder nicht?!:

Code:

 

Hallo Ralph,

also der Code läuft bei mir seit Jahren problemlos, erst unter xl2003, dann unter xl2010 und jetzt unter xl2016!

Gruß Sepp

 

\@xlph: Es sollte etwas differenzierter sein, ich habe das selber aber nicht vollständig ausgetestet.

Ab Office 2000 gibt es die Jet-Engine (Version 4.0), davor Version 3.5, die aber keine Rolle mehr spielen sollte. Ab Office 2007 gibt es (zusätzlich) die Access Database Engine (ACE), die einige Erweiterungen enthält (für Access: Anlagefelder, mehrwertige Felder, DataMacros) und die natürlich auch die neuen Dateiformate lesen kann, die ja irgendwo neu auf XML basieren.
Zum Verständnis: Jet-Engine und ACE sind Datenbankmaschinen. Diese stellen SQL als Anweisungen bereit, verwalten Tabellen (und machen diese SQL-lesbar). In richtigen Datenbanken kann man dann auch Objekte und Definitionen erstellen (Tabellen, Indizes, Beziehungen).

Jetzt dürfte man davon ausgehen, dass mit installiertem Excel >= 2007 auch die ACE verfügbar ist und eingesetzt werden kann. In Josefs Beispiel wird wohl unterstellt, dass Excelversion und Dateiversion gleich sind. Das ist auch recht oft realistisch.

Für nur XLS sollte aber auch die Jet-Engine nach wie vor verwendbar sein. Formate wie XLSX dürften aber für die Jet-Engine unleserlich sein.

In der Praxis hat man nun das Spannungsfeld Excelversion (oder genauer höchste Version eines Office-Elements), Version der aktiven Mappe sowie Version der externen Mappe. Danch müsste man dann wohl den geeigneten Provider wählen.