Dateiname in Makro aus Zelle beziehen?
Thomas Gehrmann
ich versuche, eine Excel-Tabelle zu erstellen, die Daten aus Tabellen in
anderen Arbeitsmappen (die im gleichen Verzeichnis liegen) übernimmt. Das
Problem dabei ist (siehe meine Postings vom 13.02.), dass die Namen der
Arbeitsmappen nicht fest in den Bezug integriert werden können, sondern sich
in Abhängigkeit davon ergeben, welcher Wert in einer bestimmten Zelle einer
Tabelle der Arbeitsmappe steht, in die die Daten der anderen Tabellen
übernommen werden sollen.
Hintergrund ist folgende Problematik:
In einem mittelständischen Unternehmen hat jede/r Mitarbeiter/in eine
Excel-Tabelle zur Erfassung seiner/ihrer Arbeitszeit, die unter der
jeweiligen Personalnummer als Dateiname gespeichert ist. Es ist so
aufgebaut, dass es für jeden Monat ein Kalenderblatt gibt, auf dem am
Monatsende ein Zeitsaldo und verschiedene andere Informationen (Überstunden,
Resturlaubstage u.a.) ausgegeben werden. Jetzt soll z.B. ein
Abteilungsleiter die Möglichkeit haben, eine Übersicht über diese
Monatsabschlussdaten über alle Mitarbeiter/innen zu bekommen, für die er
verantwortlich ist. Dazu soll ihm eine Excel-Tabelle zu Verfügung stehen, in
der er in den Zellen B7 bis B46 Personalnummern eingeben kann. In den
Spalten rechts daneben, sollen dann der Name des Mitarbeiters sowie die
besagten Monatsabschlussdaten erscheinen (z.B. in Zelle C7 der Name des
Mitarbeiters mit der Personalnummer in B7). Der Abteilungsleiter soll also
einfach die Personalnummern seiner Leute in B eingeben können und Excel
sucht sich dann die Informationen aus den entsprechenden Mitarbeiterdateien.
Ich habe das bisher mit INDIREKT umgesetzt. Das funktioniert aber nur wenn
das abgefragte Workbook auch geöffnet ist. Den Bezug mit Hilfe der Spalte B
variabel zu erstellen ist mir leider (auch mit Hilfe dieser NG) nicht
gelungen - für Näheres dazu siehe meine besagten Postings. Jetzt habe ich
versucht das per Makro zu realisieren. Leider bin ich da nicht sonderlich
versiert, und es stellt sich hier die gleiche Frage:
Wie bringe ich Excel bei, nicht mit einem festen Dateinamen als Bezug zu
arbeiten, sondern den Dateinamen erst aus z.B. Zelle B7 zu generieren. Hinzu
kommt das Problem, dass Excel nach dem Befehl ActiveWindow.Close immer erst
fragt, ob die Änderungen gespeichert werden sollen. Lässt sich das
automatisch mit NEIN beantworten? Genial wäre zudem, wenn mir jemand gleich
noch zeigen könnte, wie ich das automatisch für alle in Spalte B
eingetragenen Mitarbeiter (Personalnummern) ablaufen lassen kann. Ich nehme
an, man bräuchte dann sowas wie 'ne For-Next-Schleife?
Mein Makro sieht bisher so aus:
Sub Aktualisieren()
Workbooks.Open FileName:="123456.xls" 'Statt 123456.xls müsste sich
der Dateiname irgendwie aus B7 erstellen lassen
Sheets("Januar").Range("B3:D3").Copy 'Der Zellbereich B3:D3 ist eine
verbundene Zelle
ActiveWindow.Close
Range("C7").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Range("D7").Select
End Sub
Hat jemand eine Idee?
Thomas
Bernd Held
so könntee das aussehen:
Sub SpeichernTabellenblatt()
Const Pfad = "C:\temp\"
ChDir Pfad
ActiveWorkbook.Close True, ActiveSheet.Range("A1").Value & ".xls"
End Sub
Viele Grüße
Bernd
----------------------------------------------------
Die MacHero EXCEL Homepage
http://members.aol.com/Machero
Excel-Tipps&Tricks, Excel-VBA-Kompendium, Sonstiges
---------------------------------------------------
--
_____________________________________________________________
NewsGroups Suchen, lesen, schreiben mit http://netnews.web.de
Rottensteiner Peter
Ich habe dasselbe Problem und habe es z. Z. so gelöst.
Ich habe für 1 Person die passende Zeile erstellt und diese dann nach unten
kopiert (so viele wie Personen du erfassen möchtest). Die Formeln sind
danach in allen Zeilen gleich. Dann habe ich in der Formelansicht
zeilenweise die Personalnummer des ersten durch die jeweilige Personlanummer
ersetzt (Suchen und Ersetzen). In der Formelansicht (Extras, Optionen,
Formeln) geht das klaglos.
Wenn du dann die Formelansicht wieder ausschaltest, dann sind die Formeln
zeilenweise an die einzelnen Personen angepasst.
Das ist natürlich nur sinnvoll, wenn der Personalstand gleich bleibt bzw.
nur wenige Änderungen ausweist.
Aber auch nur, wenn die Personenzahl eine endliche (ich habs mit 300
gemacht) ist.
Aber wenn du eine entsprechnde Lösung erreichst, bin ich daran interessiert.
Danke
rope aus bruck
Thomas Gehrmann <thomas....@arbeitszeitberatung.de> schrieb in im
Newsbeitrag: 96g7ht$ukv$1...@news.online.de...
Ullrich Schwarz
der VBA-Vode, welchen ich Dir das letzte Mal gepostet hatte, funktioniert
nicht in dieser Form, wie ich leider inzwischen feststellen mußte.Kopiere
den folgende Code in das Microsoft-Excel-Objekt des_Tabellenblattes_
(wichtig!!), also am einfachsten rechter Mausklick auf den Tabellenname im
Arbeitsmappen-Register, CODE ANZEIGEN auswählen und folgende Zeilen
einfügen:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Pfad$, Blatt$, DateiName$, z
Pfad = "c:\temp" '<= anpassen!!
Blatt = "Januar"
Application.EnableEvents = False
For Each z In Intersect(Range("B7:B46"), Target)
DateiName = z.Value & ".xls"
z.Offset(0, 1).Value = GetValue(Pfad, DateiName, Blatt, "B3")
Next
Application.EnableEvents = True
End Sub
Private Function GetValue(path, file, sheet, range_ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function
Pfad natürlich noch anpassen. Der Vorteil dieser Methode: Die Dateien müssen
nicht geöffnet werden.
MfG Ullrich
"Thomas Gehrmann" <thomas....@arbeitszeitberatung.de> schrieb im
Newsbeitrag news:96g7ht$ukv$1...@news.online.de...
Ullrich Schwarz
etwas überarbeitet und korrigiert:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Pfad$, Blatt$, z
Pfad = "c:\temp"
Blatt = "Januar"
If Intersect(Range("B7:B46"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each z In Intersect(Range("B7:B46"), Target)
If IsEmpty(z) Then
z.Offset(0, 1).ClearContents
Else
z.Offset(0, 1).Value = GetValue(Pfad, z.Value & ".xls", Blatt,
"B3")
End If
Next
Application.EnableEvents = True
End Sub
Private Function GetValue(path, file, sheet, range_ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function
MfG Ullrich
Thomas Gehrmann
ganz herzlichen Dank für deine Mühe. Ich kann leider nicht behaupten, dass
ich den Code vollständig verstehe, aber die Logik scheint sich mir doch zu
erschließen und ich bin ganz optimistisch, dass das klappen kann. Ich habe
ihn darum auch gleich mal reinkopiert, und nun muss ich leider noch die
peinliche Frage stellen, wie der Code denn überhaupt aufgerufen (gestartet)
wird. ?
Thomas
Ullrich Schwarz
die Prozedur "Worksheet_Change" wird automatisch ausgeführt, wenn Änderungen
in der Tabelle vorgenommen werden (näheres siehe Online-Hilfe unter
worksheet_change). Das ist natürlich bequem, da so die Aktualisierung
automatisch abläuft. _ABER_: Sollte Dein Chef so clever sein und die Zellen
für die Mitarbeiternummern verformeln, wird das Change-Ereigniss nicht
ausgelöst, wenn sich die Daten in der Tabelle nur aufgrund der Änderungen
von Zellen, auf denen sich die Formeln beziehen, ändern. Dies könnte man
jedoch noch mit dem Ereignis Worksheet_Calculate in Griff bekommen. Keine
vernünftige Lösung habe ich aber bisher für den Fall gefunden, daß der User
mit der Funktion 'BEARBEITEN | AUSFÜLLEN' den Zelleninhalt verändert, da
auch dann_kein_Change-Ereignis ausgelöst wird und somit die automatsiche
Aktualisierung versagt.. Es kann daher sinnvoller sein, die
Aktualisierungsprozedur nicht über ein Ereigniss sondern manuell starten zu
lassen, z.B. durch einen Button auf dem Blatt, welchen der User zum
Neuberechnen anclicken muß, und damit dem User die Verantwortung für die
Aktualisierung aufzubürden. In diesem Fall müßte die Prozedur etwas
umgeschrieben werden, ein Beispiel steht unten.
MfG Ullrich
Diese Zeilen in einem allgemeinen Modul speichern:
'-----snipp
Sub BerechneMitarbeiterDaten()
Dim Pfad$, Blatt$, z
Pfad = "c:\temp"
Blatt = "Januar"
For Each z In ActiveSheet.Range("B7:B46")
If IsEmpty(z) Then
z.Offset(0, 1).ClearContents
Else
z.Offset(0, 1).Value = GetValue(Pfad, z.Value & ".xls", Blatt, "B3")
End If
Next
End Sub
Private Function GetValue(path, file, sheet, range_ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function
'-----snipp
"Thomas Gehrmann" <thomas....@arbeitszeitberatung.de> schrieb im
Newsbeitrag news:96iu10$i8h$1...@news.online.de...
Thomas Gehrmann
genial, es funzt alles. Ganz herzlichen Dank nochmal. Ich hatte schon gar
nicht mehr damit gerechnet, dass sich das realisieren lässt. Jetzt habe ich
nur noch eine Frage, die aber wahrscheinlich nicht weniger kompliziert ist:
Wenn die einzelnen Dateien ein Leseschutzkennwort haben, funktioniert das
ganze natürlich nur dann, wenn beim Abruf auch das Kennwort eingegeben wird.
Hast Du, Ullrich, oder jemand anders aus der NG vielleicht eine Idee, wie
man das automatisieren könnte? Ich stelle mir das so vor, dass die
Kennwörter ebenfalls in dieser Datei (z.B. in einer Liste) hinterlegt und
dann bei der Abfrage automatisch verwendet werden.
Kann Excel sowas?
Thomas
Ullrich Schwarz
"Thomas Gehrmann" <thomas....@arbeitszeitberatung.de> schrieb im
Newsbeitrag news:96jf07$lgt$1...@news.online.de...
> Hallo Ullrich,
> genial, es funzt alles. Ganz herzlichen Dank nochmal. Ich hatte schon gar
> nicht mehr damit gerechnet, dass sich das realisieren lässt. Jetzt habe
ich
> nur noch eine Frage, die aber wahrscheinlich nicht weniger kompliziert
ist:
> Wenn die einzelnen Dateien ein Leseschutzkennwort haben, funktioniert das
> ganze natürlich nur dann, wenn beim Abruf auch das Kennwort eingegeben
wird.
keine Ahnung, ob das Problem mit dem Kennwort bei diesem Lösungsansatz
lösbar ist. Evtl. mit Sendkeys arbeiten? Grds. aber nicht empfehlenswert.
Dann doch lieber den Lösungsansatz mit Workbook.Open ausbauen.
> Hast Du, Ullrich, oder jemand anders aus der NG vielleicht eine Idee, wie
> man das automatisieren könnte? Ich stelle mir das so vor, dass die
> Kennwörter ebenfalls in dieser Datei (z.B. in einer Liste) hinterlegt und
> dann bei der Abfrage automatisch verwendet werden.
> Kann Excel sowas?
MfG Ullrich