Office: (Office 2007) Effizienter Import/Export mit TransferSpreadSheet und FileDialog

Hi Leute,

das ist meine erste Anfrage hier im Forum, also weist mich bitte darauf hin, falls ich was vergessen habe. Ich nutze Ac2007.

Ich habe für ein simples Fakturierungsprogramm in einem kleine Betrieb eine Import/Export Prozedur (nach Excel) geschrieben.
Mein Problem ist nicht, dass der Code nicht funktioniert, denn das tut er idealerweise*Smilie , aber ich würde gerne eure Meinung hören, ob das ganze nicht auch einfacher oder Effizienter geht.

Zielstellung: Ich habe in meiner DB verschiedene Tabellen. Diese Tabellen möchte ich einzeln nach Excel exportieren können. Der Import von Excel nach Access soll ebenfalls dementsprechend funktionieren. Die Auswahl des Speicherorts der Export-Tabelle, sowie des Ortes der Import-Tabelle, sollen jeweils über ein Auswahlfenster erfolgen. Es werden nicht alle Tabellen auf einmal importiert/exportiert, sondern jede Tabelle einzeln.

Die Benutzeroberfläche enthält jeweils eine ComboBox (cboExportAuswahl, cboImportAuswahl) zur Auswahl der jeweiligen Tabelle und einen Button (cmdExport, cmdImport) zum Ausführen der jeweiligen Aktion.
Hier ein Screenshot:

https://www.dropbox.com/s/ndwg4bgsct...nsfer.png?dl=0

Die Import-Funktion soll darüber hinaus zwei Varianten des Import bereithalten:
1. bestehende Datensätze werden erhalten
2. bestehende Datensätze werden überschrieben
Dazu die die CheckBox.

Meine Prozedur heißt xlTransfer und funktioniert in beide Richtungen:

Code:

Die GetRootTbl-Funktion dient dazu um die entsprechende Tabelle zur Auswahl zu erhalten, da die ComboBoxen ungebunden manuell mit Daten gefüllt wurde:

Code:

Ich habe xlTransfer und GetRootTbl zusammen in ein extra Modul ausgelagert. Für die FileDialog-Box braucht man die Microsoft Office Object Library (bei mir Version 12.0) als Verweis.

Der Code kann dann in das Click-Ereignis der Buttons mit der jeweiligen TransferDirection geschrieben werden:

Code:

Ich übergebe die Form-Variable mit, für den Fall, dass Import und Export in verschiedenen Formularen relaisiert werden sollen.

Wenn ihr noch Anregungen und vorschläge, sowie Fragen habt, immer her damit.

:)

 

Hallo Enekist,

ich verstehe die Intention nicht ganz, warum du ganz Tabellen nach Excel exportieren möchtest - und dann auch noch einzeln, also ohne relationale Beziehung zu anderen Tabellen. Sind deine Daten eventuell nicht in der Normalform?

Um sich mal gelegentlich etwas anzuschauen, geht das einfach in der Oberfläche von Access und dann spielt Effizienz keine Rolle.

Für Auswertungen würde man die DB als Datenquelle direkt in Excel einbinden und damit fällt ein Export auch weg.

Gruß Ulrich

PS: bitte Screenshots hier hochladen.

 

Hallo knobbi38,

danke für deine schnelle Antwort. Die Tabellen kann ich dir leider nicht zur Verfügung stellen. sie werden Firmenintern in anderen Programmen weiterverarbeitet und diese Programme können mit Access-DBs nun mal nichts anfangen, aber dafür mit Excel-Tabellen.
Die Access-Oberfläche wurde absichtlich ausgeblendet, um den Nutzern das Arbeiten zu erleichtern (da keiner von ihnen mit Access an sich umgehen kann).

Es hätte ja sein können, dass sich innerhalb des angegebenen Codes noch etwas vereinfachen lässt. Der Ablauf, so wie er dort steht, soll aber im Allgemeinen so bleiben (da es ja bereits funktioniert und das auch so wie geplant).

Aber es ist ja so, es gibt Code und es gibt schönen Code.

Trotzdem Danke für deine Anmerkungen.

 

Hallo Enekist,

du solltest nicht die Tabellen zu Verfügung stellen, so war das nicht gemeint.

Wenn es dir um deinen Code geht, würde ich versuchen, das ganze in ein Modul zu packen und so generisch wie möglich zu machen. Dazu gehört, keine externen Referenz auf Forms/Controls, wie z.B. hier:
Code:

und auch keine hartkodierten Angaben im Code: (siehe Hartkodiert – Wikipedia)
Code:

GetRootTbl() halte ich für überflüssig und würde das im Formular mit Konstanten festlegen. Das kann per Argument übergeben werden und für "TransferDirection As Integer" würde ich eine Enumeration wählen, womit dann auch Intellisens besser untersützt würde. Was passiert eigentlich bei "TransferDirection=2". Nur mal so...

Im Übrigen bevorzuge ich ein "klassische" Fehlerbehandlungsroutine, also mit Resume und nur einem Exit aus der Sub.

Gruß Ulrich

 

Hallo Ulrich,

zur ersten Anmerkung von dir:
Da hast du absolut recht! Das habe ich wahrscheinlich bloß übersehen.
Es reicht ja, wenn ich
Code:

an der entsprechenden stelle schreibe.

Bei der GetRootTbl() bin ich auch die ganze Zeit am überlegen.
Ich werde das denke ich, so machen, wie du gesagt hast.
Die Enumeration für TransferDirection muss ich mir nochmal genau anschauen, damit habe ich in VBA noch nicht direkt gearbeitet, aber es sieht auf den ersten Blick relativ einfach aus.
Wenn TransferDirection nicht eins oder Null ist, dann entsteht ein Fehler, und er springt nach TRANSFERFEHLER. Das liegt daran, dass 0 und 1 den Werten acImport und acExport entsprechen.

Morgen werde ich mal eine überarbeitete Version der Prozedur einstellen.

Die Fehlerbehebung werde ich auch noch anpassen. Ich habe eine Weile nicht mehr mit VBA gearbeitet und muss mich erst wieder umstellen von Python auf VBA^^, daher habe ich erstmal das geschrieben was mir noch im Gedächtnis war.

 

Ich glaube nicht, dass TransferSpreadsheet dies leisten kann.

 

Hallo Enekist,

Schau dir das nochmal genau an, was ausgeführt wird, wenn TransferDirection = 2 ist.

ist auch eine hartkodierte externe Referenz, was ja eigentlich vermieden werden sollte. Der Wert der Kombobox sollte als Parameter übergeben werden.

Ganz wichtig ist der Punkt, den Eberhard angesprochen hat! Wenn das gewünscht ist, muß die Logik umgeschrieben werden.

Gruß Ulrich

 

Hallo Eberhard, hallo Ulrich,

Ihr habt recht, wenn ihr sagt, dass TransferSpreadSheet Tabellen nicht direkt überschreiben kann. Da es jedoch entsprechende Datensätze an bestehende Tabellen anhängt, habe ich für den Fall, dass die CheckBox (chkReplace) ausgewählt wurde, eine If-Anweisung zum vorherigen löschen aller existierenden Datensätze eingebaut, die unabhängig von TransferSpreadSheet läuft:

Code:

Für den Fall TransferDirection = 2 werde ich noch eine Exeption einbauen, den Fall habe ich versehentlich unterschlagen. *grinangel

Ich werde mich jetzt mal an die Sach ransetzen und eure Vorschläge einbauen

 

Wenn die Datenbanktabellen per Beziehungen miteinander verbandelt oder anderwertig voneinander abhängig sind, dürfte es mit einem generellen Löschen schwierig sein.

Wegen des Stichwortes Effizienz: Zum Thema Import habe ich hier einige Gedanken niedergelegt: Grundlagen - SQL ist leicht (4) - Aktualisierung einer Tabelle

 

Es tut mir Leid, dass es so lange gedauert hat, aber ich bin jetzt erst dazu gekommen.
Ich habe jetzt einmal die Prozedur leicht umgeschrieben und folgende Änderungen vorgenommen:

1. Vermeidung von hartkodierten Angaben (außer an einer Stelle, da die Lösung umständlicher erscheint, als das Problem -> Column-Referenz der ComboBox)
2. Verzicht auf GetRootTbl() (in der ComboBox wurde die Werteliste durch eine Tabellenreferenz ersetzt)
3. Leichte Verbesserung des Error-Handlings

Das ist nur der erste Versuch und es haben sich bestimmt neue Fehler eingeschlichen, aber es funktioniert immer noch in beide Richtungen:
Code:

Ich hatte ein paar Probleme mit dem Handling von Control-Objekten nach der Übergabe, daher habe ich den ComboBox-Namen nicht als Control sondern als String übergeben.

Zur Anmerkung bzgl. des löschens des Tabelleninhalts:
Dieser Abschnitt funktioniert bei mir auch problemlos.

 

Hallo Enekist,

ein paar kleine Anregungen:
1) Die Sub würde ich in eine Funktion umwandeln und einen Boolean Wert zurückgeben, welcher angibt, ob die Funktion mit einem Fehler beendet wurde oder nicht. So kann der Aufrufer entsprechend reagieren.

2) den Namen der Sub/Funktion sollte nicht mit xl.. beginnen. Diese Schreibweise wird normlerweise für Excel-Konstanten und Aufzählungen verwendet.

3) Ich würde weder eine Referenz auf das Formular, noch auf das Control mitgeben, sondern die Werte selber übergeben. Form-Referenz entfällt und Tabellennamen, Initial-ExportFilenamen ink. Pfad würde ich einfach als Parameter, ggf. auch optional, mitgeben. Damit der Filename einheitlich wird, könnte man eine kleine Hilfsfunktion erstellen, z.B. MakeExportFilename() und im Aufrufer erledigen.
So läßt sich das später auch gut maintenieren, wenn sich das Format ändern sollte. Dann brauch man nur diese Funktion anpacken.

4) "chkReplace" ist absolut indiskutabel, weil global! So etwas gehört als optionaler Parameter übergeben.

5) "GoTo TRANSFERFEHLER" und "Resume Next"*entsetzt
Aber das wolltest du ja sowieso noch überarbeiten. Hinweis: es ist eine gute Praxis, daß eine Sub/Funktion immer nur einen "Ausgang" hat, also eigentlich ist "Exit Sub/Function" verpönt, mit Ausnahme natürlich des Exits vor der üblichen Fehlerbehandlungsroutine und nach dem "Cleanup".

Gruß Ulrich

 

\@Enekist

Am Einfachsten in dem du den If-Konstrukt durch Select Case ersetzt.
Da brauchst du diesen Fehler auch nicht in einer Fehlerbehandlung abfangen,
sondern kannst ihn von vorne herein ausschliessen (vermeiden statt behandeln); -
deine Msgbox kommt dann schon in Case Else.
gruss ekkehard

 

So ich habe jetzt noch ein paar von euren Anmerkungen umgesetzt
Ich habe alle Steuerelement-Referenzen ersetzt (Form, Controls).
Der Dateiname wird in einer extra Funktion erstellt (CreateFileName)

Den Rest aber werde ich jetzt weitestgehend so lassen.

So sieht es jetzt aus:

Code:

Das Exit Sub bekomme ich leider nicht raus, da ich das TransferSpreadsheet nicht in die Select Case schreiben möchte und ich habe mich auch dafür entschlossen es nicht als Function, also ohne Rückgabewert zu schreiben. Daran sieht man ja, dass es an der einen oder anderen Stelle noch möglich wäre eine kleine Verbesserung vorzunehmen, ich bin aber soweit erst einmal zufrieden damit.

Auf jeden Fall danke ich euch für eure tollen Anregungen!

 

Code:

Wie auch: TransferDirection wird als Argument an die Prozedur übergeben. da könnte man schon bei Prozeduraufruf sicherstellen, was da übergeben wird.

 

Hallo Enekist,

hier ein Struktur-Vorschlag mit einer rudimentären Fehlerbehandlung (gekürzt):

aber immer noch in*ak*zep*ta*bel ist der Aufruf von
.InitialFileName = CreateFileName(vtTblExport, opTblBez)
innerhalb der Subm weil vtTblExport extern zur Prozedur ist. So etwas gehört zu den DON'Ts in der Programmierung.

Gruß Ulrich