In gelben Zellen Werte einfügen

Hallo zusammen,

ich habe eine grosse Exceltabelle, in welcher gelbe Eingabefelder sind und grüne (gesperrte) Summenzeilen.
In den gelben Eingabefeldern habe ich Verknüpfungen auf zwei weitere Exceltabellen eingefügt, da häufige Aktualisierungen notwendig sind und eine manuelle Eingabe nicht in Frage kommt.

Die Tabelle muss, wenn sie fertig gefüllt ist, durch eine weitere Anwendung geöffnet und hochgeladen werden - allerdings ist durch die vielen Verknüpfungen die Wartezeit sehr lang.

Darum die Idee, ein Makro einzusetzen, welches automatisch die gelben Zellen erkennt, diese kopiert und die Werte als Werte einfügt.

Wäre super, wenn mir einer von Euch / Ihnen helfen kann.

Danke im voraus,
schuetc

 

1. Was heisst "hochgeladen", wer ladet sie wohin ?

2. Wo sind die "2 weiteren Tabellen" ?
In der gleichen Mappe, oder auf dem gleichen PC, oder... ?
Sind die weiteren Tabellen gleichzeitig geöffnet ?

3. Um wie viele Verknüpfungen geht es... 10, 100, 1000, 10000.... ?

4. Sind in der OriginalDatei mehrere Tabellenblätter, oder nur eines ?
Braucht der "Hochlader" ggf. die ganze Datei oder nur ein bestimmtes Blatt ?

5. Geschieht das Aktualisieren und das "Hochladen" automatisch, oder muss das ein User auslösen ?

6. Am "hochgeladenen" Ort muss die Tabelle nicht mehr aktualisiert werden ?
Sie bleibt dort und wird dann dort gespeichert oder vernichtet ?


Ein Makro könnte in den gelben Zellen schon die Verknüpfungen durch den verknüpften Wert ersetzen, aber...
...dann sind die Verknüpfungen in der Originaldatei weg...

Wenn ich das richtig verstanden habe, dann vielleicht in diesen Richtungen >

- Das Makro holt sich selbst die verknüpften Werte und schreibt sie in die Zellen, oder

- Das Makro macht eine Kopie der Datei oder des Tabellenblattes und löscht dort die Verknüpfungen. Diese Kopie wird dann "hochgeladen".

- ...und... und... ...aber zuerst die Fragen beantworten...

Gruss Urs

 

Hallo Urs,

um Deine Fragen zu beantworten.

Das Hochladen bezieht sich auf das Laden der Daten in eine Hyperion Datenbank. Hierzu muss die Excel-Datei innerhalb dieser Umgebung geöffnet werden.

Die Datenblätter, welche den Input liefern, sind in zwei verschiedenen Excel-Dateien, welche ich entweder gleichzeitig in der o.g. Umgebung öffnen kann (das dauert) oder welche ich geschlossen lassen kann (dann merkt sich die "Hochlade-Excel-Datei" die Werte nicht.

Anzahl Verknüpfungen sind P20:AA882 => zwischen 1.000 und 10.000

Die Anzahl der Tabellenblätter in der Hochladedatei spielt keine Rolle, ich muss halt nur die Werte in den gelben Zellen fixieren, damit ich beim Öffnen in der Hyperion Umgebung nicht so lange warten muss.

Idee ist, dass ich eine Originaldatei habe, in der die Verknüpfungen sind; diese dann kopiere, mit Endung fix versehe und dann im relevanten Datenblatt die Formeln fixiert werden.
Dann kann ich die fixierte Datei in der anderen Umgebung öffnen und laden.

 

...für die Datenbank natürlich nicht, die liest nur aus, was sie auslesen muss - für die Bereitstellung der Fix-Kopie aber schon.

Man kann die ganze Datei kopieren, oder auch nur ein bestimmtes Blatt in eine neue Datei.
Nur das Blatt > dann werden zB allfällige Auto-Makros, oder Makros in Modulen oder in anderen Blättern, nicht mit kopiert. Sie können dann dort auch nicht für Aerger sorgen...

Nach Deinen Bereichsangaben gehe ich jetzt davon aus, dass die gelben Zellen nur in einem einzigen Blatt sind, und nur dieses Blatt von der Datenbank tatsächlich genutzt wird.

Es geht also doch um "einige" Zellen... da muss die Verarbeitung schon optimiert werden...

Jetzt habe ich leider keine Zeit für die Details... bis morgen zum Frühstück...

Gruss Urs

 

Guten Morgen...

Teste dieses Makro an einer Kopie Deiner Nutzdatei...

Kopiere den Code in ein neues Modul1, dann wird er nicht in die Fix-Datei übertragen.
Wenn Du einen Button in der Datei hast, um den Vorgang auszulösen, dann gib dem Button nur diesen Code >

Private Sub CommandButton1_Click()
BlattKopieren
End Sub

Code:

Option Explicit

Sub BlattKopieren()

Dim origDir, origName, copyName, openWB As Workbook

            'sucht Pfad der Original-Datei
origDir = ThisWorkbook.Path & "\"
            'sucht Dateiname ohne .xls
origName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - [COLOR="Red"]4[/COLOR])
            'bildet Dateiname für Kopie
copyName = origName & "[COLOR="red"]Fix.xls[/COLOR]"
            'prüft ob alte Kopie-Datei geöffnet ist, wenn ja schliesst sie
For Each openWB In Application.Workbooks
    If InStr(openWB.Name, copyName) > 0 Then
        openWB.Close SaveChanges:=False
        Exit For
    End If
Next openWB
            'prüft ob alte Kopie-Datei gespeichert ist, wenn ja killt sie
If Dir(origDir & copyName) <> "" Then Kill (origDir & copyName)

            'macht Kopie des Blattes "KopierBlatt" in eine neue XLS
Sheets("[COLOR="red"]KopierBlatt[/COLOR]").Copy
            'speichert Kopie als xxxxFix.xls im gleichen Verzeichnis wie Original-Datei
ActiveWorkbook.SaveAs copyName
            'ersetzt in Kopie Formeln durch Werte
Range("[COLOR="red"]P20:AA882[/COLOR]").Value = Range("[COLOR="red"]P20:AA882[/COLOR]").Value

End Sub

Rote Markierungen anpassen >

- bei Excel2007 an die längere Datei-Endung anpassen
4 muss 5 sein, und xls muss xlsm oder wie das heissen mag sein

- KopierBlatt ist der Name meines Arbeitsblattes mit den gelben Zellen, in der Original-Datei > anpassen an Deinen Blattnamen

- Tabellenbereich muss alle gelben Zellen umfassen, kann ruhig auch viele leere Zeilen als Reserve umfassen


Funktion >

- zuerst wird eine schon vorhandene gleichnamige Datei gekillt

- dann wird eine Kopie des GelbZellenBlattes gemacht und im gleichen Verzeichnis gespeichert und

- alle Formeln werden durch ihren Wert ersetzt

Ob vor dem Entfernen der Formeln die neue Datei die Verknüpfungen noch aktualisiert, konnte ich nicht feststellen.
Vermutlich schon, aber auch mit 10000 Verknüpfungen auf eine andere XLS geht es so dann blitzartig.

Gruss Urs