5
Formel in VBA (Excel 2003) mit Pfad und Dateiname aus Variable?
Hallo,
ich habe ein Problem, bei dem ich nicht weiterkomme. Ich möchte per VBA eine Formel in eine Zelle schreiben, bei der der Dateipfad, der Dateiname und der Spaltenindex für Sverweis aus Variablen kommen sollen. Aber ich bekomme es mit den Anführungszeichen nicht hin.
Originalformel:
"=WENN(SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH)="";0;SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH))"
Es funktioniert, wenn ich die Formel folgendermaßen ändere und als Ganzes in eine Variable speichere:
Let a = "=WENN(SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH)="""";0;SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH))"
ActiveSheet.Range("C10").FormulaLocal = a
Was muss ich ändern, wenn der Pfad durch eine Variable "Pfad" und der Dateiname durch eine Variable "Dateiname" und der Spaltenindex durch eine Variable "Index" ersetzt werden soll?
Habe schon alles möglich in folgender Form probiert:
ActiveSheet.Range("C10").FormulaLocal = "=WENN(SVERWEIS($C$2;'" & Pfad "[" & Dateiname "]Blatt1'!$A$2:$C$70;" & Index ";FALSCH)="""";0; ...
Aber dann bekomme ich doppelte Klammern oder doppelte Dateinamen usw. Finde auch im Forum nicht den geeigneten Hinweis.
Vielen Dank für Eure Hilfe.
Grüße, Thomas
ich habe ein Problem, bei dem ich nicht weiterkomme. Ich möchte per VBA eine Formel in eine Zelle schreiben, bei der der Dateipfad, der Dateiname und der Spaltenindex für Sverweis aus Variablen kommen sollen. Aber ich bekomme es mit den Anführungszeichen nicht hin.
Originalformel:
"=WENN(SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH)="";0;SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH))"
Es funktioniert, wenn ich die Formel folgendermaßen ändere und als Ganzes in eine Variable speichere:
Let a = "=WENN(SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH)="""";0;SVERWEIS($C$2;'D:\Test\[Testdatei.xls]Blatt1'!$A$2:$C$70;3;FALSCH))"
ActiveSheet.Range("C10").FormulaLocal = a
Was muss ich ändern, wenn der Pfad durch eine Variable "Pfad" und der Dateiname durch eine Variable "Dateiname" und der Spaltenindex durch eine Variable "Index" ersetzt werden soll?
Habe schon alles möglich in folgender Form probiert:
ActiveSheet.Range("C10").FormulaLocal = "=WENN(SVERWEIS($C$2;'" & Pfad "[" & Dateiname "]Blatt1'!$A$2:$C$70;" & Index ";FALSCH)="""";0; ...
Aber dann bekomme ich doppelte Klammern oder doppelte Dateinamen usw. Finde auch im Forum nicht den geeigneten Hinweis.
Vielen Dank für Eure Hilfe.
Grüße, Thomas
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 176262
Url: https://administrator.de/contentid/176262
Printed on: April 19, 2024 at 09:04 o'clock
5 Comments
Latest comment
Hallo Thomas!
Versuchs mal so:
wobei das Stringverkettungszeichen (&) vor und nach dem Variablennamen stehen muss
Gruß Dieter
Versuchs mal so:
Formel = "=WENN(ISTFEHLER(SVERWEIS($C$2;'" & Pfad & "[" & Dateiname & "]Blatt1'!$A$2:$C$70;" & SpaltenNr & ";FALSCH));0;SVERWEIS($C$2;'" & Pfad & "[" & Dateiname & "]Blatt1'!$A$2:$C$70;" & SpaltenNr & ";FALSCH))"
Range("C10").FormulaLocal = Formel Gruß Dieter
Hallo Dieter,
vielen Dank für die schnelle Antwort. Leider funktioniert das auch noch nicht. Der Pfad, der in der Variablen Pfad gespeichert ist und auch das ' Zeichen davor tauchen in der Formel in Zelle C10 nicht auf.
Ich glaube, dass es mit den Anführungszeichen zusammenhängt, die wahrscheinlich noch nicht alle richtig gesetzt sind.
Gruß, Thomas
vielen Dank für die schnelle Antwort. Leider funktioniert das auch noch nicht. Der Pfad, der in der Variablen Pfad gespeichert ist und auch das ' Zeichen davor tauchen in der Formel in Zelle C10 nicht auf.
Ich glaube, dass es mit den Anführungszeichen zusammenhängt, die wahrscheinlich noch nicht alle richtig gesetzt sind.
Gruß, Thomas
Hallo nochmal,
die Formel scheint auch ohne Pfad zu funktionieren. Ignoriert Excel diesen in der Formel, wenn die Formel-Datei im gleichen Ordner liegt wie die Datei, auf die Sverweis zugreift?
Werde mal ein bißchen rumprobieren und melde mich wieder, wenn es sicher funktioniert.
Danke und Gruß, Thomas
die Formel scheint auch ohne Pfad zu funktionieren. Ignoriert Excel diesen in der Formel, wenn die Formel-Datei im gleichen Ordner liegt wie die Datei, auf die Sverweis zugreift?
Werde mal ein bißchen rumprobieren und melde mich wieder, wenn es sicher funktioniert.
Danke und Gruß, Thomas
Moin schustec,
Ja nee... ist falschrum formuliert.
WENN ein Pfad angegeben ist, DANN ignoriert Excel den nicht.
Aber es muss kein Pfad angegeben werden, da ohnehin nur eine (in derselben Session) bereits geöffnete Datei berücksichtigt werden kann beim SVERWEIS().
Unddie doofen Praktikanten Excel steigt schon vorher an zwei, drei Stellen, falls du mehrere gleichnamige Dateien aus verschiedenen Laufwerken/Verzeichnissen öffnen willst.
Also unterm Strich:
Ja, du kannst Laufwerk&Verzeichnis weglassen -der Dateiname (wenn mehrere Dateien beteiligt sind) reicht.
Andererseits.....
... du willst doch dieses Anführungszeichen-String-mein-Excel-versteht-mich-nicht-Puzzle nicht halbfertig weglegen, oder?
Grüße
Biber
die Formel scheint auch ohne Pfad zu funktionieren. Ignoriert Excel diesen in der Formel,
wenn die Formel-Datei im gleichen Ordner liegt wie die Datei, auf die Sverweis zugreift?
wenn die Formel-Datei im gleichen Ordner liegt wie die Datei, auf die Sverweis zugreift?
Ja nee... ist falschrum formuliert.
WENN ein Pfad angegeben ist, DANN ignoriert Excel den nicht.
Aber es muss kein Pfad angegeben werden, da ohnehin nur eine (in derselben Session) bereits geöffnete Datei berücksichtigt werden kann beim SVERWEIS().
Und
Also unterm Strich:
Ja, du kannst Laufwerk&Verzeichnis weglassen -der Dateiname (wenn mehrere Dateien beteiligt sind) reicht.
Andererseits.....
... du willst doch dieses Anführungszeichen-String-mein-Excel-versteht-mich-nicht-Puzzle nicht halbfertig weglegen, oder?
Grüße
Biber
Hallo Biber,
halbfertig weglegen ist tatsächlich nicht so mein Ding.
Vielleicht kurz zum Hintergrund:
Ich muss Mehrarbeitsdaten aus einem Programm in ein mir vorgegebenes, blattgeschütztes Excel-Blatt (quasi Vorlagendatei) übertragen, in das nur an best. Zellen Werte (oder eben auch Formeln
) eingetragen werden können und welches unter dem entsprechenden Namen des Mitarbeiters gespeichert werden soll. Für viele Mitarbeiter ist das ne ewige Quälerei, das hunderte Male mit copy & paste zu erledigen. Nun bin ich eben so weit, dass ich per Makro die Daten aus dem Programm (aus einer Art Textdatei) herausfiltere und nach Namen sortiert in einer Tabelle habe (nennen wir sie Datendatei). Von dort aus wird nun die quasi Vorlagendatei geöffnet und automatisch unter dem entsprechenden Mitarbeiternamen gespeichert. Bisher hatte ich die Formeln (Sverweise) in der Vorlagendatei bereits fest in den Spalten, was aber den Nachteil hatte, dass ich die Datendatei nicht unter einem neuen Ort oder Namen (z.B. im Folgejahr) speichern kann, ohne alle Formeln in der Vorlagendatei anzupassen. Deswegen der Wunsch, dass beim Öffnen der Vorlagendatei die Sverweise über die Variablen an den Pfad der Datendatei und deren Namen angepasst werden.
Lösung:
Da die Formeln nun ohne Pfadübernahme funktionierten, habe ich folgendes ausprobiert:
1. Die Datei in einen anderen Ordner speichern lassen
Ergebnis: Sie funktioniert immer noch, ohne dass der Pfad in der Formel auftaucht.
2. Die Datei ohne geöffnete Datendatei geöffnet (Nachfrage, ob Werte aktualisiert werden sollen, bejaht) und siehe da:
Nun wird der gesamte Pfad in der Formel angezeigt.
Ich bin anscheinend einem Anfängerfehler aufgesessen. Solange die Datei, auf die Sverweis zugreift, geöffnet ist, wird in der Zelle die Formel ohne Pfad angezeigt, sonst mit Pfad. Formel funktioniert also, wie von Dieter beschrieben.
Damit ist das Problem gelöst. Vielleicht kommen ja noch ein paar Antwortkommentare, deshalb warte ich mit der Markierung "Beitrag gelöst" noch ein paar Tage.
Vielen Dank an alle für die Mithilfe!
Gruß, Thomas
halbfertig weglegen ist tatsächlich nicht so mein Ding.
Vielleicht kurz zum Hintergrund:
Ich muss Mehrarbeitsdaten aus einem Programm in ein mir vorgegebenes, blattgeschütztes Excel-Blatt (quasi Vorlagendatei) übertragen, in das nur an best. Zellen Werte (oder eben auch Formeln
) eingetragen werden können und welches unter dem entsprechenden Namen des Mitarbeiters gespeichert werden soll. Für viele Mitarbeiter ist das ne ewige Quälerei, das hunderte Male mit copy & paste zu erledigen. Nun bin ich eben so weit, dass ich per Makro die Daten aus dem Programm (aus einer Art Textdatei) herausfiltere und nach Namen sortiert in einer Tabelle habe (nennen wir sie Datendatei). Von dort aus wird nun die quasi Vorlagendatei geöffnet und automatisch unter dem entsprechenden Mitarbeiternamen gespeichert. Bisher hatte ich die Formeln (Sverweise) in der Vorlagendatei bereits fest in den Spalten, was aber den Nachteil hatte, dass ich die Datendatei nicht unter einem neuen Ort oder Namen (z.B. im Folgejahr) speichern kann, ohne alle Formeln in der Vorlagendatei anzupassen. Deswegen der Wunsch, dass beim Öffnen der Vorlagendatei die Sverweise über die Variablen an den Pfad der Datendatei und deren Namen angepasst werden.Lösung:
Da die Formeln nun ohne Pfadübernahme funktionierten, habe ich folgendes ausprobiert:
1. Die Datei in einen anderen Ordner speichern lassen
Ergebnis: Sie funktioniert immer noch, ohne dass der Pfad in der Formel auftaucht.
2. Die Datei ohne geöffnete Datendatei geöffnet (Nachfrage, ob Werte aktualisiert werden sollen, bejaht) und siehe da:
Nun wird der gesamte Pfad in der Formel angezeigt.
Ich bin anscheinend einem Anfängerfehler aufgesessen. Solange die Datei, auf die Sverweis zugreift, geöffnet ist, wird in der Zelle die Formel ohne Pfad angezeigt, sonst mit Pfad. Formel funktioniert also, wie von Dieter beschrieben.
Damit ist das Problem gelöst. Vielleicht kommen ja noch ein paar Antwortkommentare, deshalb warte ich mit der Markierung "Beitrag gelöst" noch ein paar Tage.
Vielen Dank an alle für die Mithilfe!
Gruß, Thomas
