Office: (Office 2010) Kann Access benannte Bereiche mit dynamischer Größenanpassung nicht verknüpfen?

hallo

Ich möchte in Access Verknüpfungen anlegen zu "benannten Bereichen" in Excel (Assistent für Externe Daten, Verknüpfung erstellen, Benannte Bereiche anzeigen...). Prinzipiell klappt das auch.

Dann habe ich diese Bereiche dynamisch gemacht
Code:

Der Bereich beginnt in Zeile 10 von Spalte A und geht dann soweit nach rechts, wie Spaltenüberschriften gefunden werden und soweit nach unten, wie Daten in A sind. In Excel funktioniert zumindest die Anzeige mit F5.

Der Verknüpfungsassistent zeigt diese dynamischen Bereiche aber nicht an.
Der Verknüpfungsassistent zeigt auch dynamische Bereiche nicht an, bei denen die Spalten fest definiert sind und nur die Zeilenzahl dynamisch definiert wird.

Kann Access solche dynamischen Bereiche generell nicht verknüpfen oder ginge das mit VBA?

Mögliche mir bekannte Alternativen:
* Feste Bereiche mit ausreichend vielen Zeilen (10.000), die an Access übergebenen zahlreichen Leerzeilen/Spalten werden nicht großartig stören
* Import der Tabellen mittels Anfügeabfragen in die gewünschten Datenformate und rudimentäre Prüfung auf korrekte Daten. Das klingt gut und sinnvoll - dauert aber noch, bis ich das kann.

Grüsse
simon

:)

 

Moin Simon.
Mit VBA geht das in jedem Fall.
Über die Erstellung eines Excel-Objekts kannst Du in Access direkt auf die Excel-Objekte zugreifen und dynamisch den Bereich ermitteln, in dem die nicht-leeren Zellen sich befinden.

 

Wie genau verknüpfst Du?

Der Einfachheit halber würde ich feste (ausreichende) Bereiche oder eigene Tabellenblätter verknüpfen.
Bei ersterem kann man leere Zeilen ausfiltern.

Import sowie auch Excel-Automation wären aufwändiger und umständlicher.

Zur Eingangsfrage: Ich bin mir ziemlich sicher, dass eine datenbankgemäße Verknüpfung mit den Excelformeln nichts anfangen kann.

 

Moin,

> Wie genau verknüpfst Du?

Wie eingangs beschrieben, Assistent für Externe Daten, Verknüpfung erstellen, Benannte Bereiche anzeigen...

> datenbankgemäße Verknüpfung mit den Excelformeln nichts anfangen kann

Ich hatte erwartet, dass Access bei Excel nach dem Bereich fragt und Excel dann den Bereich ermittelt und übergibt. So ähnlich wie der Aufruf der Excel-Rundungs-Funktionen. Anscheinend holt sich Access aber die Definition des Bereichs und kann damit nix anfangen, warum auch immer...

grüsse
simon

 

z.K.,
DoCmd.TransferSpreadsheet acImport oder das Makro ImportierenExportierenTabellenblatt können mit dynamischen Bereichen auch nix anfangen.

 

... wie das zu erwarten ist.

Verknüpfen und Standardimporte basieren auf Jet/SQL, und da wird einfach die Tabellenangabe sofort und verständlich für diese Methoden erwartet.
Das "Nachfragen" müsste dann der Entwickler über einen eigenprogrammierten Import selber umsetzen (was möglich ist).

Die unbestimmte Zeilenanzahl wäre einfach zu beherrschen, indem man einen genügend tiefen Bereich fest angibt und die leeren Zeilen einfach wegfiltert.

Mit beliebig vielen Spalten kann aber SQL recht wenig anfangen, weil Felder benannt werden müssen. DB-Tabellen sind definiert, nicht beliebig. Daneben müssen ja die unbekannten Felder in Folge sinnvoll verarbeitet werden können. Also wäre es zielführend, nicht nach dem nächstbesten Knopf zu suchen, der die Daten IRGENDWIE in die Access-DB hineinbringt, sondern man könnte daran denken, die Daten bereits in Excel tauglich aufzubereiten. Mit richtigen Datenstrukturen ergeben sich manche Probleme gar nicht erst.

Man sollte sich also die Struktur des Excelsheets ansehen und analysieren (wer kann). Ggf. reicht schon ein Unpivot, welches sich mit Power Query komfortabel umsetzen lässt.

 

Hallo Simon,

Dein Hilferuf hat mich veranlaßt, mal ein bißchen zu testen, und siehe da:
- Access schwächelt (auch V2019),
- wenn der Excel-Bereichsname dynamisch ist und mit volatilen Funktionen wie
- Bereich.Verschieben(), Indirekt() usw.
- aber auch: =Tabelle2!$E$4:INDEX(Tabelle2!$E$4:$E$24;ANZAHL2(Tabelle2!$E$4:$E$24)*wink.gif*
- gebildet wird, dann werden diese Bereichnamen im Verküpfungsassistent nicht angezeigt
- auch wenn der Bereichsname auf 'Intelligente Tabellen' von Excel beruht
- ignoriert Access diesen Namen

Und damit ist PQ (PowerQuery) auch kein Lösungsansatz, denn das Ergebnis in Excel ist ja eine 'intelligente Tabelle'!

Dafür steckt M$ lieber die ganze Kraft in Menü-Struktur-Veränderungen wie Ribbon, die jeder Admin per Gruppen-Richtlinienkompetenz so gestalten kann,
daß dem fortschrittliche User jegliche eigene Ribbon-Programmierung untersagt wird.
Das es auch ohne Ribbon geht, zeigt Visual Studio.

Gruß von Luschi
aus klein-Paris

PS: selbst Lorenz Hölscher in seinen Büchern und 'access-im-unternehmen' machen um dieses Thema einen großen Bogen!