<\/p>\n
Wer aus der Excel-Welt kommt, fühlt sich beim Umgang mit Access-Tabellen unter Umständen etwas eingeschränkt: Man kann nicht mal eben eine Formel einbauen, Berechnungen ad hoc durchführen oder Felder nach Wunsch formatieren. Das Integrieren von Excel per OLE ist auch nicht das Gelbe vom Ei, aber es gibt noch eine Variante – wenn auch eine aussterbende: Das Spreadsheet-Steuerelement der Office Web Components, die letztmalig mit Office 2003 ausgeliefert wurden, bietet eine gute Gelegenheit, Excel-Funktionalität in Access-Formulare zu integrieren.<\/b><\/p>\n
Wenn Sie die Office-Spreadsheet-Komponente nutzen möchten, müssen Sie diese unter Umständen erst herunterladen. Den Download für die 2003er-Komponente, die auch mit Access 2007 zusammenarbeitet, finden Sie unter dem Link http:\/\/www.microsoft.com\/downloads\/details.aspxFamilyId=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en<\/b>. Die EULA dieses Pakets, die Sie beim Installieren bestätigen müssen, besagt sinngemäß, dass die Komponenten nur zusammen mit der jeweils passenden Office-Version verwendet werden dürfen. Die Beschreibung der in Office 2007 weggefallenen Komponenten unter http:\/\/technet.microsoft.com\/en-us\/library\/cc178954.aspx <\/b>(siehe unter OWC11<\/b>) ist jedoch weniger restriktiv. Vielleicht liegt die Einschränkung in der EULA einfach daran, dass man zur Zeit der Fertigstellung des Setups noch nicht daran dachte, ob und wie die Office Web Components in Office 2007 integriert würden. Wir beobachteten jedoch, dass auch die Installation von Office 2007 und sogar der Runtime-Version das Steuerelement mit auf den Rechner brachte.<\/p>\n
Bei der Beispieldatenbank zu diesem Beitrag könnte es übrigens zu kleineren Problemen mit den Verweisen kommen. Diese lassen sich gegebenenfalls durch Entfernen von als NICHT VORHANDEN <\/b>gekennzeichneten Verweisen und Löschen und erneutes Einfügen und Umbenennen des Steuerelements in clsSpreadsheet <\/b>beheben. Wie auch immer: Wenn wir davon ausgehen, dass Sie eine Version der OWC auf dem Rechner haben, können Sie das Spreadsheet-Steuerelement über den Dialog ActiveX-Steuerelement einfügen <\/b>zu einem Formular hinzufügen (siehe Bild 1). In der Formularansicht verbreitet das Steuerelement dann auch gleich einen Hauch von Excel-Feeling (siehe Bild 2). Natürlich ist dies kein vollwertiges Excel, aber es lassen sich zumindest die Zellen mit Werten füllen – und auch mit Formeln. So funktioniert beispielsweise nicht nur das Eintragen von Formeln mit relativen und absoluten Bezügen zu den übrigen Zellen, sondern relative Formeln werden beim Kopieren und Einfügen auch entsprechend angepasst.<\/p>\n
![\"pic001.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic001_opt.jpeg\")
<\/p>\n
Bild 1: Einfügen der Spreadsheet-Komponente in ein Access-Formular<\/span><\/b><\/p>\n Bild 2: Das Spreadsheet-Steuerelement im Access-Formular<\/span><\/b><\/p>\n Es gibt keine Anzeige der in der aktuell markierten Zelle enthaltenen Formel in der Kopfzeile des Steuerelements, dafür können Sie aber einen kleinen Dialog einblenden, der alle gewünschten Informationen anzeigt (siehe Bild 3). Damit können Sie auch etwa die Formatierungen der aktuellen Zelle direkt nach ihrer Markierung einsehen und anpassen. Unter uns gesagt: Damit ist man näher am Geschehen als unter Excel selbst, denn dort kann man nicht die Zelle wechseln, während etwa der Dialog zum Einstellen des Zellenformats geöffnet ist.<\/p>\n Bild 3: Anzeige der Formel eines Feldes<\/span><\/b><\/p>\n Der nächste Clou ist, dass Sie problemlos komplette Bereiche aus einer in Excel geöffneten Excel-Datei in das Spreadsheet-Steuerelement kopieren können und umgekehrt. Das bedeutet auch, dass alle Formeln und Formatierungen mit kopiert werden (siehe Bild 4). <\/p>\n Bild 4: Das Hin- und Herkopieren von Bereichen zwischen Excel und des in einem Formular eingebettetem Spreadsheet-Steuerelement ist kein Problem.<\/span><\/b><\/p>\n Wenn Sie im Dialog Befehle und Optionen <\/b>die Optionen Horizontale Bildlaufleiste<\/b>, Vertikale Bildlaufleiste<\/b>, Blattregisterkarten <\/b>und Symbolleiste <\/b>deaktivieren, sieht das Steuerelement fast wie ein Unterformular in der Datenblattansicht aus – mit dem kleinen Unterschied, dass die Spalten- und Zeilenköpfe doch an Excel erinnern (siehe Bild 5). Wenn Sie die Symbolleiste ausgeblendet haben, können Sie den Dialog Befehle und Optionen <\/b>übrigens über das Kontextmenü des Spreadsheet-Steuerelements wieder einblenden.<\/p>\n Bild 5: Das nackte Spreadsheet-Steuerelement<\/span><\/b><\/p>\n Die Zeilen- und Spaltenüberschriften sowie die Gitternetzlinien können Sie übrigens auch ein- und ausblenden, und zwar mit den entsprechenden Optionen auf der Registerseite Blatt<\/b>. Auf der gleichen Registerseite des Dialogs können Sie einzelne Tabellenblätter ausblenden oder hinzufügen und auch ihre Reihenfolge ändern (hier findet sich übrigens auch die einzige Möglichkeit, zwischen den Tabellenblättern zu wechseln – mit Ausnahme der wenig bekannten Tastenkombination Strg + Bild auf<\/b>\/Bild ab<\/b>).<\/p>\n Mehr Optionen in der Entwurfsansicht<\/b><\/p>\n Eines unterscheidet das Spreadsheet-Steuerelement von fast allen übrigen Steuerelementen, mit denen Sie bislang gearbeitet haben: Sie können auch in der Entwurfsansicht damit arbeiten. Und noch besser: Dort stehen noch mehr Optionen als in der Formularansicht bereit.<\/p>\n <\/p>\n Zellen schützen<\/b><\/p>\n Mit der Option Aktuelles Blatt schützen <\/b>können Sie das komplette Blatt vor änderungen schützen (siehe Bild 6). Typischerweise soll der Benutzer aber mit den Daten arbeiten, zumindest aber Zugriff auf einen Teil der Zellen haben. Dies können Sie festlegen, indem Sie Aktives Blatt schützen <\/b>deaktivieren und dann die Zellen beziehungsweise Bereiche, die nicht geschützt werden sollen, markieren und für diese auf der Registerkarte Format<\/b> die Option Zellen sperren<\/b>\/Zellsperrung aufheben <\/b>aktivieren beziehungsweise deaktivieren (siehe Bild 7). Die Sperrung der nicht so behandelten Zellen aktivieren Sie dann wieder auf der Registerkarte Schutz <\/b>durch Aktivieren der Option Aktives Blatt schützen<\/b>.<\/p>\n Bild 6: Einstellen des Blattschutzes<\/span><\/b><\/p>\n Bild 7: Mit dieser Option schließen Sie einzelne Zellbereiche vom Blattschutz aus.<\/span><\/b><\/p>\n Einstellungen per Code festlegen<\/b><\/p>\n Wenn Sie einige Einstellungen wie etwa den Zellenschutz vornehmen oder Daten eingeben und Zellen formatieren und anschließend das Formular schließen und wieder öffnen, werden Sie feststellen, dass die Einstellungen und Inhalte nur teilweise übernommen werden. Der Zellschutz beispielsweise bleibt bestehen, die Inhalte und Formatierungen einzelner Zellen, Spalten oder Zeilen hingegen werden zurückgesetzt. Gleiches gilt für das Ein- und Ausblenden der Symbolleiste, der Spalten- und Zeilenköpfe und so weiter. Sie werden also nicht umhin kommen, die Eigenschaften des Spreadsheets per Code beim Laden des Formulars nach Ihren Wünschen einzustellen. Es folgen einige Beispiele.<\/p>\n Titelleiste ein-\/ausblenden:<\/p>\n oder<\/p>\n Titel einstellen:<\/p>\n Eigenschaften des Titels wie Schriftgröße einstellen (weitere Eigenschaften siehe Objektkatalog oder IntelliSense):<\/p>\n Office-Logo ausblenden:<\/p>\n Symbolleiste ausblenden:<\/p>\n Zellschutz einstellen – hier durch Deaktivieren des Schutzes für die komplette Tabelle, Entsperren der ersten zehn Zellen der ersten Spalte und anschließendes Aktivieren des Schutzes:<\/p>\n Fenster fixieren – durch Festlegen der Zelle, die im zu scrollenden Bereich links oben liegt, hier etwa für die komplette zweite Zeile:<\/p>\n Über weitere Einstellmöglichkeiten wie etwa das Füllen und Formatieren von Zellen und Bereichen erfahren Sie mehr im Beitrag Excel automatisieren <\/b>(Shortlink 693).<\/p>\n Spreadsheet mit Daten füllen<\/b><\/p>\n Der wichtigste Punkt beim Umgang mit dem Spreadsheet-Steuerelement ist für uns sicher der folgende: Wie bekomme ich den Inhalt von Tabellen oder Abfragen in das Spreadsheet-Steuerelement, und vor allem: Wie speichere ich dort an den Daten vorgenommene änderungen wieder in der zugrunde liegenden Tabelle All dies werden wir uns in den folgenden Abschnitten ansehen.<\/p>\n Es scheint klar, dass wir hier nicht ohne Programmierung auskommen – könnte man das Spreadsheet-Control anders an eine Datenherkunft binden, wäre es sicher bereits das Lieblingssteuerelement vieler Access-Entwickler und nicht mit Office 2007 (zumindest offiziell) aus dem Kader geflogen.<\/p>\n Um komfortabel per VBA auf das Steuerelement zugreifen zu können, geben wir ihm zunächst einen griffigen Namen, nämlich ctlSpreadsheet<\/b>.<\/p>\n Um das Steuerelement zu deklarieren und zu instanzieren, legen wir eine Ereignisprozedur für die Ereigniseigenschaft Beim Laden <\/b>des Formulars an, die wie folgt aussieht:<\/p>\n Die hier verwendete Objektvariable objSpreadsheet <\/b>deklarieren wir im Modulkopf, damit sie auch von anderen Prozeduren aus ansprechbar ist:<\/p>\n Spreadsheet aus Tabelle füllen<\/b><\/p>\n Für Testzwecke verwenden wir nun eine Tabelle namens tblPersonen <\/b>mit den drei Feldern PersonID <\/b>(Primärschlüsselfeld), Vorname <\/b>und Nachname <\/b>(beides Textfelder), der wir ein paar Beispieldatensätze hinzufügen.<\/p>\n Für das Füllen von Excel-Tabellen und somit auch für das OWC-Spreadsheet-Steuerelement gibt es die CopyFromRecordset<\/b>-Methode, die einen vorgegebenen Bereich des Tabellenblatts mit Daten aus dem als Parameter angegebenen Recordset füllt.<\/p>\n Wie bei datensensitiven und für VB6 beziehungsweise das Web entwickelten Steuerelementen von Microsoft üblich, können Sie diese nur mit ADO füllen. Dazu benötigen Sie einen Verweis auf die Bibliothek Microsoft Active Data Objects x.x<\/b>, den Sie in Bild 8 erkennen können.<\/p>\n Bild 8: Für den fehlerfreien Einsatz der CopyFromRecordset-Methode brauchen Sie ein ADO-Recordset, das Sie mit diesem Verweis verfügbar machen.<\/span><\/b><\/p>\n Die erste Variante sieht wie folgt aus:<\/p>\n Das Ergebnis liefert Abb. 9. Wie dort zu erkennen ist, kopiert CopyFromRecordset <\/b>auch die Feldnamen und fügt diese als Spaltenüberschriften in das Spreadsheet ein.<\/p>\n Bild 9: Das Spreadsheet-Steuerelement mit frisch eingefügten Daten aus einem ADO-Recordset<\/span><\/b><\/p>\n Wichtig ist in diesem Zusammenhang, dass Sie vor dem Einlesen ermitteln müssen, wie viele Spalten und Zeilen die Daten aus dem Recordset füllen werden. CopyFromRecordset <\/b>füllt nämlich immer nur den angegebenen Bereich und nimmt sich nicht etwa den Platz, der für das komplette Recordset nötig wäre.<\/p>\n Eine alternative Variante ist diese hier:<\/p>\n Finetuning<\/b><\/p>\n Versehen wir die Prozedur zum Füllen des Spreadsheet-Steuerelements noch mit ein wenig Finetuning: Dazu sorgen wir beispielsweise dafür, dass die Datenherkunft in der Titelzeile des Spreadsheets angezeigt wird und dass die Symbolleiste, die Spalten- und Zeilenüberschriften verschwinden.<\/p>\n Außerdem soll die erste Zeile, also die mit den Feldüberschriften, gesperrt und fixiert werden, sodass der Benutzer deren Inhalt nicht ändern kann und diese Zeile beim Scrollen nicht oben herausrutscht. Schließlich sollen die Spaltenbreiten an die eingelesenen Daten angepasst werden.<\/p>\n Eines ist in diesem Zusammenhang ganz wichtig und kann Ihnen helfen, unnötige Fehlerrecherchen zu umgehen: Sie können natürlich keine Daten in einen Bereich füllen, in dem auch nur ein einziges Feld gesperrt ist. Daher sollten Sie vor dem Einsatz von CopyFromRecordset <\/b>oder anderen Varianten zum Füllen von Zellen mit Daten immer den Schutz des Arbeitsblatts aufheben.<\/p>\n Zurückschreiben der Daten in die Tabelle<\/p>\n Wir raten nicht an, das Spreadsheet-Steuerelement zum Bearbeiten von Daten aus Tabellen zu verwenden. Daher gehen wir nicht im Detail darauf ein, wie Sie per CopyFromRecordset<\/b> in das Spreadsheet eingelesene Daten nach dem ändern wieder zurück in die entsprechenden Tabellen schreiben.<\/p>\n Es gibt zwar Möglichkeiten, Daten aus Tabellen direkt über das Spreadsheet zu ändern, dies würde jedoch an diese Stelle den Rahmen sprengen.<\/p>\n Zusammenfassung und Ausblick<\/b><\/p>\n Wozu nun können wir das Spreadsheet-Steuerelement verwenden Nun: Beispielsweise der Import von Daten aus einer Excel-Tabelle könnte so vereinfacht werden.<\/p>\n Normalerweise hilft einem der Import-Assistent dabei recht ordentlich (vor allem seit Access 2007), aber einen Nachteil hat er doch: Wenn Sie nur einen Bereich eines Excel-Tabellenblatts einlesen möchten, müssen Sie diesen zuvor in Excel selbst definieren und als benannten Bereich speichern.<\/p>\n Wie wäre es denn, wenn wir den Inhalt eines Excel-Sheets komplett in das Spreadsheet-Steuerelement einlesen könnten und dann innerhalb von Access festlegen, welchen Bereich wir importieren möchten – ganz ohne Excel öffnen zu müssen<\/p>\n Wie dies funktioniert, schauen wir uns in einem weiteren Beitrag namens Excel-Importassistent im Eigenbau <\/b>(Shortlink 696) an. Dort lernen Sie noch weitere Techniken in Zusammenhang mit dem Spreadsheet-Steuerelement kennen.<\/p>\n Grundsätzlich sehen wir das Spreadsheet-Steuerelement jedoch als einfache Möglichkeit, ein Tabellenblatt zum Durchführen von Kalkulation in die gewohnte Access-Umgebung zu integrieren – das Einbetten eines Excel-Objekts per OLE liefert zwar das „echte“ Excel, wirkt aber bei weitem nicht so integriert wie das Spreadsheet-Steuerelement.<\/p>\n Enthaltene Beispieldateien:<\/p>\n Spreadsheets.mdb<\/p>\n![\"pic002.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic002_opt.jpeg\")
<\/p>\n![\"pic003.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic003_opt.jpeg\")
<\/p>\n![\"pic004.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic004_opt.jpeg\")
<\/p>\n![\"pic005.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic005_opt.jpeg\")
<\/p>\n![\"pic006.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic006_opt.jpeg\")
<\/p>\n![\"pic007.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic007_opt.jpeg\")
<\/p>\nobjSpreadsheet.TitleBar.Visible = True\/False<\/pre>\n
objSpreadsheet.DisplayTitleBar = True\/False<\/pre>\n
objSpreadsheet.TitleBar.Caption = "Beispieltitel"<\/pre>\n
objSpreadsheet.TitleBar.Font.Size = 24<\/pre>\n
objSpreadsheet.DisplayOfficeLogo = False<\/pre>\n
objSpreadsheet.DisplayToolbar = False<\/pre>\n
With objSpreadsheet\r\n.ActiveSheet.Protection.Enabled = False\r\n.ActiveSheet.Range("A1:A10").Locked = False\r\n.ActiveSheet.Protection.Enabled = True\r\nEnd With<\/pre>\nWith objSpreadsheet\r\n.ActiveSheet.Range("A2").Activate\r\n.ActiveWindow.FreezePanes = True\r\nEnd With<\/pre>\nPrivate Sub Form_Load()\r\n Set objSpreadsheet = Me!ctlSpreadsheet.Object\r\n End Sub<\/pre>\n
Dim objSpreadsheet As OWC11.Spreadsheet<\/pre>\n
![\"pic008.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic008_opt.jpeg\")
<\/p>\nPrivate Sub Form_Load()\r\n Dim cnn As ADODB.Connection\r\n Dim rst As ADODB.Recordset\r\n Set cnn = CurrentProject.Connection\r\n Set rst = New ADODB.Recordset\r\n rst.Open \"SELECT * FROM tblPersonen\", cnn\r\n Set objSpreadsheet = Me.ctlSpreadsheet.Object\r\n objSpreadsheet.Range(\"A1:C3\") _\r\n CopyFromRecordset rst\r\n End Sub<\/pre>\n
![\"pic009.png\"](\"..\/fileadmin\/_temp_\/2009_06\/Spreadsheets-web-images\/pic009_opt.jpeg\")
<\/p>\nPrivate Sub Form_Load()\r\n Dim cnn As ADODB.Connection\r\n Set cnn = CurrentProject.Connection\r\n Set objSpreadsheet = Me!ctlSpreadsheet.Object\r\n With objSpreadsheet\r\n .ActiveSheet.ConnectionString = _\r\n cnn.ConnectionString\r\n .ActiveSheet.CommandText = _\r\n "SELECT * FROM tblPersonen"\r\n .Refresh\r\n End With\r\n End Sub<\/pre>\n
Private Sub Form_Load()\r\n Dim cnn As ADODB.Connection\r\n Dim rst As ADODB.Recordset\r\n Set cnn = CurrentProject.Connection\r\n Set rst = New ADODB.Recordset\r\n rst.Open "SELECT * FROM tblPersonen", cnn\r\n Set objSpreadsheet = Me!ctlSpreadsheet.Object\r\n With objSpreadsheet\r\n .DisplayTitleBar = True\r\n .ActiveWindow.DisplayColumnHeadings = _\r\n False\r\n .ActiveWindow.DisplayRowHeadings = False\r\n .TitleBar.Caption = rst.Source\r\n .ActiveSheet.Protection.Enabled = False\r\n .ActiveSheet.Cells.Locked = False\r\n .ActiveSheet.Rows(1).Locked = True\r\n .Range("A2").Activate\r\n .ActiveWindow.FreezePanes = True\r\n .DisplayToolbar = False\r\n .Cells.CopyFromRecordset rst\r\n .Cells.AutoFit\r\n .ActiveSheet.Protection.Enabled = True\r\n End With\r\n End Sub<\/pre>\nDownloads zu diesem Beitrag<\/h3>\n