Dateipfad variabel in Excel Formel bei geschlossener Datei (also nicht mit INDIREKT)

Imhotep · 14. November 2019 um 09:47

Hallo zusammen,

ich habe leider mal wieder ein Problem.

Ich möchte Daten aus einer anderen Excel-Datei auslesen, deren Name sich nach Monat ändert. Genauso ändern sich die Namen der Tabellenblätter, die nach Kalenderwochen benannt werden. In der eigenen Datei sind die Tabellenblätter genauso nach Kalenderwochen benannt (dies geht jetzt dank der Hilfe von Andreas hier aus dem Forum automatisch)

Also kurz zusammengefasst ist der Pfad zu der Tabelle jeden Monat anders. Wie kann ich auf die Master-Datei zugreifen, ohne diese zu öffnen (was ja mit der Funktion INDIREKT möglich wäre?

Die Master-Datei hat im Namen folgende Struktur:

Master_Monat_Jahr.xlsm

also für Juni 2014 dann Master_Juni_2014.xlsm

Ich habe zum besseren Verständnis die Datei mal hochgeladen:

http://www.file-upload.net/download-8938591/Vorlage_…

Die Berechnung müsste für 5 Bereiche (je Kalenderwoche) erfolgen, wobei die 5.Kalenderwoche nicht immer benötigt wird und das Blatt ggf. in der Master-Datei deshalb nicht enthalten ist.

Ich hoffe ihr versteht was ich sagen möchte

Ich hoffe ihr könnt mir helfen

Vielen Dank schonmal.

Gruß
Sebastian

abgemeldet5 · 14. November 2019 um 09:50

Hallo
ich bin mir nicht sicher aber vielleicht hilft dir dieser Link weiter [Klick]

Gruß Holger

Imhotep · 14. November 2019 um 09:51

Hallo Holger,

leider hat der Code von dem Link, wo du mir gegeben hast irgendwie nicht funktioniert.
Trotzdem Danke

Gruß
Sebastian

Imhotep · 14. November 2019 um 09:52

Hallo zusammen,

ich habe jetzt im Netz einen Code gefunden und wollte ihn für mich anpassen, jedoch komme ich nicht ganz weiter, da meine Werte in der Quelldatei in folgenden Zellen stehen und nicht in einem zusammengehörigen Bereich. Sie sind jedoch systematisch angelegt:

K28 U28 AE28 AO28 AY28
K37 U37 AE37 AO37 AY 37
K46 U46 AE 46 AO46 AY46
K55 U55 AE55 AO55 AY55
K64 U64 AE64 AO64 AY64
K73 U73 AE73 AO73 AY73
K82 U82 AE82 AO82 AY82
K91 U91 AE91 AO91 AY91
K100 U100 AE100 AO100 AY100
K109 U109 AE109 AO109 AY109

Dies ist der Code

Option Explicit

Public Function GetDataClosedWB(SourcePath As String, _
SourceFile As String, sourceSheet As String, _
SourceRange As String, TargetRange As Range) As Boolean
'Holt einen Bereich aus einer _geschlossenen_ Arbeitsmappe
‚Nur in VBA zu verwenden; nicht aus einer Tabellenzelle heraus
‚© [email protected]
’ wird durch die HoleDaten aufgerufen
Dim strQuelle As String
Dim Zeilen As Long
Dim Spalten As Byte
On Error GoTo InvalidInput
strQuelle = "‘" & SourcePath & „[“ & SourceFile & „]“ & sourceSheet & "‘!" & Range(SourceRange).Cells(1, 1).Address(0, 0)
Zeilen = Range(SourceRange).Rows.Count
Spalten = Range(SourceRange).Columns.Count
With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
.Formula = „=IF(“ & strQuelle & „=“""",""""," & strQuelle & „)“
.Value = .Value
End With
GetDataClosedWB = True
Exit Function
InvalidInput:
MsgBox „Die Quelldatei oder der Quellbereich ist ungültig!“, vbExclamation, „Get data from closed Workbook“
GetDataClosedWB = False
End Function

Public Sub HoleDaten()
’ Die Funktion arbeitet mit der obrigen GetDataClosedWB zusammen
Dim Pfad As String
Dim Dateiname As String
Dim Blatt As String
Dim Bereich As String
Dim Ziel As Range
Pfad = „\c4dehicnn01g008\50056-PB33$\PB33_R13\Teamsteuerung\Hotline_Masterdateien“
Dateiname = ActiveSheet.Range(„G5“) ’ aus welcher Datei soll er holen?
Blatt = ActiveSheet.Range(„H6“) ’ von welcher Tabelle soll er holen?
Bereich = „K28“ 'aus welchem Bereich soll er holen?
Set Ziel = ActiveSheet.Range(„B7“) ’ in welchen Bereich soll er kopieren? Genauer gesagt: Bei welcher Zelle soll er anfangen, Datein reinzukopieren? Bsp: ActiveCell geht auch
If GetDataClosedWB(Pfad, Dateiname, Blatt, Bereich, Ziel) Then
’ MsgBox „Daten importiert“
End If
End Sub

hier kann ich bei Bereich nur eine einzelne Zelle oder einen tatsächlichen Bereich z.B. A1:C3 eingeben.

Wie bekomme ich das hin, dass die Werte aus den oben genannten Zellen übernommen werden?

Vielen Dank vorab.

abgemeldet26 · 14. November 2019 um 09:52

Hallo Imhotep,

welche deiner Werte (SourcePath, SourceFile, SourceSheet usw.) stehen denn in welchen Zellen?

Gruß, Andreas

Imhotep · 14. November 2019 um 09:52

Hallo Andreas,

Die Quelle habe ich bereits im unteren Teil vom Code eingegeben:

Pfad = „\c4dehicnn01g008\50056-PB33$\PB33_R13\Teamsteuerung\Hotline_Masterdateien“
Dateiname = ActiveSheet.Range(„G5“)
Blatt = ActiveSheet.Range(„H6“)

Das bei Bereich darunter bekomme ich nicht hin, dass er die verschiedenen, nicht direkt zusammengehörigen Zellen gemeinsam nimmt, also die oben erwähnten K28, K37 usw., sowie dann je ein paar Spalten weiter U28, U37 usw.

Kennst du da einen Trick?

Gruß
Sebastian

abgemeldet26 · 14. November 2019 um 09:53

Noch ne Nachfrage
Hi Imhotep,

wie ist das denn? Wenn du als Ziel B7 angibst und dann die Werte aus K28 und K37 holst, dann steht in B7 der Wert von K28, so weit klar. Aber wo soll der Wert von K37 hin? Direkt unter B7 also nach B8, oder im gleichen Abstand wie in der Quelle, also dann 9 Zeilen weiter unten, nach B16?

Aber egal wie, ich glaube dein Problem lässt sich nur mit einer Schleife lösen. Ich könnte dir wohl was bauen, wenn du geantwortet hast.

Gruß, Andreas

Imhotep · 14. November 2019 um 09:53

Hallo Andreas,

stimmt, das hätte ich noch genauer erwähnen sollen, sorry.

Aber es ist wie du vermutest.

K28 soll nach B7
K37 soll nach B8
…

Genauso dann

U28 nach C7
U37 nach C8

Usw.

das geht 5 Spalten nach rechts und es müssten 10 Zeilen nach unten sein, das kann ich dir morgen noch genauer sagen, da ich grad nicht an den PC komme, wo die Datei drauf ist.

Was auch ich auch noch erwähnen sollte, ist, dass dies dann für das erste Tabellenblatt so ist. Den Namen des jeweiligen Tabellenblatts hab ich jetzt vor über H6 bis H10 vorzugeben. Der Aufbau ist in den nächsten Tabellenblättern identisch, es soll wieder aus K28 usw. Holen, jedoch dann bei (soweit ich es im Moment weiß) bei B21 mit dem Reinkopieren fortsetzen. Also pro Quell-Tabellenblatt im Ziel 14 Zeilen weiter unten als im Bereich davor.

Ich sag die genauen Zellenangaben morgen nochmal.
Hoffentlich hab ich des ganze nicht zu kompliziert geschrieben

Wenn das klappt wäre das so super

Gruß
Sebastian

Imhotep · 14. November 2019 um 09:54

Hallo Andreas,

ich habe nochmal nachgeschaut. Die Angaben, die ich gestern gemacht habe sind alle richtig.

Hier nochmal die Ziel-Bereiche zum reinkopieren:
1.KW/ Tabellenblatt (KW steht in H6)
B7:F16

2.KW/Tabellenblatt (KW steht in H7)
B21:F30

3.KW/ Tabellenblatt (KW steht in H8)
B35:F44

4.KW/ Tabellenblatt (KW steht in H9)
B49:F58

5.KW/ Tabellenblatt (KW steht in H10)
B63:F72

wobei das Tabellenblatt für die 5.KW nicht immer in der Quelldatei enthalten ist. Das Ziel-Tabellenblatt heißt „Eingabe“

Vielen Dank schonmal für die Hilfe.

Gruß
Sebastian

abgemeldet26 · 14. November 2019 um 09:54

Versuch’s mal hiermit
Hallo Imhotep,

versuch es mal mit dieser Sub:

Public Sub HoleDaten()
 ' Die Funktion arbeitet mit der obrigen GetDataClosedWB zusammen
 Dim Pfad As String
 Dim Dateiname As String
 Dim Blatt As String
 Dim Bereich As String
 Dim Ziel As Range
 Dim z As Long, s As Long
 Dim kw As Long

 Pfad = "\\c4dehicnn01g008\50056-PB33$\PB33\_R13\Teamsteuerung\Hotline\_Masterdateien\"
 Dateiname = ActiveSheet.Range("G5") ' aus welcher Datei soll er holen?
 For kw = 1 To 5
 Blatt = Range("H" & kw + 5).Value ' von welcher Tabelle soll er holen?
 For z = 7 + (kw - 1) \* 14 To 7 + (kw - 1) \* 14 + 9
 For s = 2 To 6
 Bereich = Cells((z - (kw - 1) \* 14 - 7) \* 9 + 28, 11 + (s - 2) \* 10).Address 'aus welchem Bereich soll er holen?
 Set Ziel = ActiveSheet.Cells(z, s) ' in welchen Bereich soll er kopieren? Genauer gesagt: Bei welcher Zelle soll er anfangen, Datein reinzukopieren? Bsp: ActiveCell geht auch
 If GetDataClosedWB(Pfad, Dateiname, Blatt, Bereich, Ziel) Then
' MsgBox "Daten importiert"
 End If
 Next s
 Next z
 Next kw
End Sub

Die Funktion

GetDataClosedWB

bleibt unverändert.
Gruß, Andreas

Imhotep · 14. November 2019 um 09:56

Hallo Andreas,

super es funktioniert ganz gut. Vielen Dank.

Ein Problem habe ich jedoch leider noch. Die jeweils 5.KW ist nicht immer in der Quelldatei als Tabellenblatt vorhanden.

Kannst du mir hier bitte noch etwas einbauen, dass es diesen Umstand ignoriert, wenn das Tabellenblatt mit der Bezeichnung der 5.KW (die er sich ja über H10 holt) nicht existiert?

Aber mal wieder echt top, dankeschön schonmal

Gruß
Sebastian

abgemeldet26 · 14. November 2019 um 09:56

Hallo Sebastian,

das mit dem Abfragen des fehlenden Blattes ist schwierig. Man kan in einer ungeöffneten Mappe nicht nachschauen, ob ein Blatt vorhanden ist. Das merkt man erst, wenn man die Formel in die Zelle einträgt, und dann ist es schon zu spät, weil dann schon die Fehlermedung kommt.
Ich habe es auf die Schnelle jetzt mal so gelöst, dass du nur 1x das Blattauswahlfenster mit „Abbrechen“ wegklicken musst, dann ist der Spuk vorbei. Hier der Code:

Option Explicit

Public Function GetDataClosedWB(SourcePath As String, \_
 SourceFile As String, sourceSheet As String, \_
 SourceRange As String, TargetRange As Range) As Boolean
 'Holt einen Bereich aus einer \_geschlossenen\_ Arbeitsmappe
 'Nur in VBA zu verwenden; nicht aus einer Tabellenzelle heraus
 '© [email protected]
 ' wird durch die HoleDaten aufgerufen
 Dim strQuelle As String
 Dim Zeilen As Long
 Dim Spalten As Byte

 On Error GoTo InvalidInput
 strQuelle = "'" & SourcePath & "[" & SourceFile & "]" & sourceSheet & "'!" & Range(SourceRange).Cells(1, 1).Address(0, 0)
 Zeilen = Range(SourceRange).Rows.Count
 Spalten = Range(SourceRange).Columns.Count
 With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
 .Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
 .Value = .Value
 End With
 GetDataClosedWB = True
 Exit Function
InvalidInput:
 ' MsgBox "Die Quelldatei oder der Quellbereich ist ungültig!", vbExclamation, "Get data from closed Workbook"
 GetDataClosedWB = False
End Function

Public Sub HoleDaten()
 ' Die Funktion arbeitet mit der obrigen GetDataClosedWB zusammen
 Dim Pfad As String
 Dim Dateiname As String
 Dim Blatt As String
 Dim Bereich As String
 Dim Ziel As Range
 Dim z As Long, s As Long
 Dim kw As Long

 Pfad = "\\c4dehicnn01g008\50056-PB33$\PB33\_R13\Teamsteuerung\Hotline\_Masterdateien\"
 Dateiname = ActiveSheet.Range("G5") ' aus welcher Datei soll er holen?
 For kw = 1 To 5
 Blatt = Range("H" & kw + 5).Value ' von welcher Tabelle soll er holen?
 For z = 7 + (kw - 1) \* 14 To 7 + (kw - 1) \* 14 + 9
 For s = 2 To 6
 Bereich = Cells((z - (kw - 1) \* 14 - 7) \* 9 + 28, 11 + (s - 2) \* 10).Address 'aus welchem Bereich soll er holen?
 Set Ziel = ActiveSheet.Cells(z, s) ' in welchen Bereich soll er kopieren? Genauer gesagt: Bei welcher Zelle soll er anfangen, Datein reinzukopieren? Bsp: ActiveCell geht auch
 If Not GetDataClosedWB(Pfad, Dateiname, Blatt, Bereich, Ziel) Then
 Ziel.ClearContents
 Exit Sub
 End If
 Next s
 Next z
 Next kw
End Sub

Der Nachteil bei dieser Version ist, dass er auch abbricht, wenn vorher ein Fehler auftritt, also wenn z.B. ein Blatt einen anderen Namen hat, als du in H6:H10 vorgibst.

Ich werde mal versuchen, ob ich irgend eine Von-Hinten-durch-die-Brust-Ins-Auge-Möglichkeit finde, um eine geschlossene Mappe auf einen Blattnamen zu überprüfen.
Kann aber nix versprechen.

Gruß, Andreas

Imhotep · 14. November 2019 um 09:56

Hallo Andreas,

super danke. Ich kann es leider erst wieder morgen testen.

Zu dem Problem mit der Abfrage eines Tabellenblattes in einer geschlossenen Arbeitsmappe.

Kann man hier nicht einfach dem Makro sagen:

Ziehe die Daten aus den ersten 4 Kalenderwochen und dann (vll. darunter als extra Abschnitt im Makro) ->
Versuche aus der 5.Kalenderwoche auch noch Daten zu holen und wenn das nicht klappt, lass es einfach

Obwohl ich vermute, dass das neue Makro von dir das genau so versucht , oder?

Aber ich dachte, dass es dadurch vielleicht nicht mehr abbricht, wenn vor dem Datenimport der 5.Kalenderwoche Fehler auftauchen.

Ist halt menschlich gedacht. Ich weiß das Maschinen da etwas anders ticken

Wenn es nicht geht, macht das überhaupt nix. Ich bin mega froh über dieses Makro von dir.

Ich kann mich echt nur bedanken dafür. Gibt es irgendwo richtig gut erklärte Lehrvideos oder Bücher, wo man VBA mit lernen kann, wenn man noch totaler Anfänger ist? Mir ist das mit dem Definieren ein Rätsel, genauso wie den richtigen Befehl zu finden und in der entsprechenden Form auch zu schreiben, dass der Editor nicht mehr meckert und das Makro funktioniert.

Gruß
Sebastian

abgemeldet26 · 14. November 2019 um 09:56

Hallo Andreas,

Hi imhotep

super danke. Ich kann es leider erst wieder morgen testen.

Bin gespannt.

Kann man hier nicht einfach dem Makro sagen:

Ziehe die Daten aus den ersten 4 Kalenderwochen und dann
(vll. darunter als extra Abschnitt im Makro) ->

Versuche aus der 5.Kalenderwoche auch noch Daten zu holen
und wenn das nicht klappt, lass es einfach

Das Makro erzeugt aus deinen Vorgaben (Pfad, Dateiname, Blatt, Zellenbereich) eine Formel, die in deine Zielzelle eingetragen wird. In dem Moment überprüft Excel, ob es auf deine Vorgaben zugreifen kann. Wenn nicht: Fehler! Dies passiert in der Funktion von Thomas.

Obwohl ich vermute, dass das neue Makro von dir das genau so
versucht , oder?

Nein, es quittiert den Dienst beim ersten Fehler.

Aber ich dachte, dass es dadurch vielleicht nicht mehr
abbricht, wenn vor dem Datenimport der 5.Kalenderwoche Fehler
auftauchen.

Ist halt menschlich gedacht. Ich weiß das Maschinen da etwas
anders ticken

Unterschätze dich mal nicht so. Ich denke ich kann das Makro so umschreiben, wie du es vorschlägst: Die ersten 4 Blätter einfach durchrennen, und danach abbrechen, wenn ein Fehler kommt.
Wenn allerdings vorher ein Fehler auftritt (z.B. das Blatt heißt nicht „KW3“ sonder „KW 3“, dann kracht’s.

Ich kann mich echt nur bedanken dafür. Gibt es irgendwo
richtig gut erklärte Lehrvideos oder Bücher, wo man VBA mit
lernen kann, wenn man noch totaler Anfänger ist?

Das Forum hier ist ja leider (dank Jens und Konsorten) fast am Ende. Ich bin hier seit 2007 dabei und habe viel gelernt, indem ich bei z.B. Reinhard (leider offenbar auch weg), Thomas (immerhin noch als MOD dabei), Holger u.a. mitgelesen habe.
Außerdem habe ich immer, wenn ich ein konkretes Problem hatte, bei Kookel o.ä. gesucht und bin auf mancherlei Schätze gestoßen.
Bis Excel 2003 fand ich auch F1 sehr hilfreich. Das hat sich ab 2007 aber drastisch verschlimmert.

Mir ist das
mit dem Definieren ein Rätsel,

Was für ein „Definieren“?

genauso wie den richtigen
Befehl zu finden und in der entsprechenden Form auch zu
schreiben, dass der Editor nicht mehr meckert und das Makro
funktioniert.

Wenn du konkrete Probleme mit Excel VBA hast, kannst du mich auch gerne per Mail kontaktieren.

Gruß
Sebastian

Dito, Andreas

Imhotep · 14. November 2019 um 09:57

Hi Andreas

Hallo Andreas,

Hi imhotep

super danke. Ich kann es leider erst wieder morgen testen.

Bin gespannt.

ich habs grad getestet, es funktioniert wunderbar. Vielen Dank nochmals.

Kann man hier nicht einfach dem Makro sagen:

Ziehe die Daten aus den ersten 4 Kalenderwochen und dann
(vll. darunter als extra Abschnitt im Makro) ->

Versuche aus der 5.Kalenderwoche auch noch Daten zu holen
und wenn das nicht klappt, lass es einfach

Das Makro erzeugt aus deinen Vorgaben (Pfad, Dateiname, Blatt,
Zellenbereich) eine Formel, die in deine Zielzelle eingetragen
wird. In dem Moment überprüft Excel, ob es auf deine Vorgaben
zugreifen kann. Wenn nicht: Fehler! Dies passiert in der
Funktion von Thomas.

Obwohl ich vermute, dass das neue Makro von dir das genau so
versucht , oder?

Nein, es quittiert den Dienst beim ersten Fehler.

Aber ich dachte, dass es dadurch vielleicht nicht mehr
abbricht, wenn vor dem Datenimport der 5.Kalenderwoche Fehler
auftauchen.

Ist halt menschlich gedacht. Ich weiß das Maschinen da etwas
anders ticken

Unterschätze dich mal nicht so. Ich denke ich kann das Makro
so umschreiben, wie du es vorschlägst: Die ersten 4 Blätter
einfach durchrennen, und danach abbrechen, wenn ein Fehler
kommt.
Wenn allerdings vorher ein Fehler auftritt (z.B. das Blatt
heißt nicht „KW3“ sonder „KW 3“, dann kracht’s.

Oh ich bin überrascht. Ist meine Denkweise gar nicht so falsch, oder?

Ich kann mich echt nur bedanken dafür. Gibt es irgendwo
richtig gut erklärte Lehrvideos oder Bücher, wo man VBA mit
lernen kann, wenn man noch totaler Anfänger ist?

Das Forum hier ist ja leider (dank Jens und Konsorten) fast am
Ende. Ich bin hier seit 2007 dabei und habe viel gelernt,
indem ich bei z.B. Reinhard (leider offenbar auch weg), Thomas
(immerhin noch als MOD dabei), Holger u.a. mitgelesen habe.
Außerdem habe ich immer, wenn ich ein konkretes Problem hatte,
bei Kookel o.ä. gesucht und bin auf mancherlei Schätze
gestoßen.
Bis Excel 2003 fand ich auch F1 sehr hilfreich. Das hat sich
ab 2007 aber drastisch verschlimmert.

Wow, also wie ich das so verstehe benötigt man einiges an Geduld. Ich bin zum Glück nicht so schnell ungeduldig. Aber echt schade, dass es eher schlechter wird.

Mir ist das
mit dem Definieren ein Rätsel,

Was für ein „Definieren“?

Ich meine dieses „Dim Rätsel As String“ usw.

genauso wie den richtigen
Befehl zu finden und in der entsprechenden Form auch zu
schreiben, dass der Editor nicht mehr meckert und das Makro
funktioniert.

Wenn du konkrete Probleme mit Excel VBA hast, kannst du mich
auch gerne per Mail kontaktieren.

danke für das Angebot. Grad gibts da nix konkretes. Ich habe es lediglich mit Excel selbst verglichen, wo zu den Formeln eine gewisse Erklärung steht. Gibt es sowas auch im VBA? Ich schaue da immer in Objektkatalog usw. und bin mir nicht sicher, ob das die Befehle sind, die ich nutzen kann für das Makro oder ob das nur für bestimmte Dinge verwendet werden kann, weil es ja Objektbibliothek heißt und soweit ich weiß ist ja nicht alles ein Objekt, oder?

Gruß
Sebastian

Dito, Andreas

abgemeldet26 · 14. November 2019 um 09:57

Hallo Sebastian,

ich hab mir den Makro noch mal angeschaut. ich denke er macht schon das, was du vorgeschlagen hattest: Blatt 1-4 durchlaufen und erst meckern, wenn Blatt 5 fehlt. Dieses Meckern kann ich ihm allerdings auf die Schnelle nicht abgewöhnen. Wie schon gesagt muss erst die Formel mit dem Zugriff auf Blatt 5 in die Zelle gescheiben werden damit er merken kann, dass das Blatt fehlt. Und dann kommt halt die Fehlermeldung. Ich habe leider noch keine Möglichkeit gefunden in einer nicht-geöffneten Mappe zu schauen, ob ein bestimmtes Blatt vorhanden ist. Das ist glaube ich ziehmlich tricky.

Für Erklärungen zu VBA-Kommandos schau doch mal in die VBA-Hilfe:
Drücke im VBA-Editor F1. Bei meinem Excel2010 geht die Hilfe allerdings seit Neuestem immer mit einer fast leeren Seite auf. Der Standard scheint hier zu sein, dass irgend eine Web-Suche aufgemacht wird, die nix findet. Dann must du auf das kleine schwarze Dreieck rechts neben dem Wort „Suchen“ klicken und aus dem Drop-Down Menü den untersten Eintrag „Hilfe für Entwickler“ wählen. Damit kommst du auf die in VBA eingebaute Hilfe.

Die Objektbibliothek braucht dich erst mal nicht zu interessieren.

Gruß, Andreas

Imhotep · 14. November 2019 um 09:58

Hallo Andreas,

Genau das Makro tut was es soll und funktioniert einwandfrei. Bin total zufrieden damit.

Wegen der VBA Hilfe, das schau ich mir mal an.

Vielen Dank nochmal für alles.

Hab noch ne gute Woche

Gruß
Sebastian