Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Formel in VBA (Excel 2003) mit Pfad und Dateiname aus Variable?

Frage Entwicklung VB for Applications

Mitglied: schustec

schustec (Level 1) - Jetzt verbinden

14.11.2011 um 22:44 Uhr, 10247 Aufrufe, 5 Kommentare

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
Mitglied: 76109
15.11.2011 um 09:18 Uhr
Hallo Thomas!

Versuchs mal so:
01.
    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))" 
02.
     
03.
    Range("C10").FormulaLocal = Formel
wobei das Stringverkettungszeichen (&) vor und nach dem Variablennamen stehen muss

Gruß Dieter
Bitte warten ..
Mitglied: schustec
15.11.2011 um 20:33 Uhr
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
Bitte warten ..
Mitglied: schustec
15.11.2011 um 21:31 Uhr
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
Bitte warten ..
Mitglied: Biber
15.11.2011 um 22:01 Uhr
Moin schustec,

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?

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 die 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
Bitte warten ..
Mitglied: schustec
16.11.2011 um 12:18 Uhr
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
Bitte warten ..
Neuester Wissensbeitrag
Festplatten, SSD, Raid

12TB written pro SSD in 2 Jahren mit RAID5 auf Hyper-VServer

Erfahrungsbericht von Lochkartenstanzer zum Thema Festplatten, SSD, Raid ...

Ähnliche Inhalte
Microsoft Office
gelöst Verschieben von Zellinformation in andere Spalte (per VBA) excel 2010 (5)

Frage von thomas1972 zum Thema Microsoft Office ...

VB for Applications
Bilder vom LDAP in VBA - Excel (3)

Frage von Roadrunner777 zum Thema VB for Applications ...

VB for Applications
gelöst VBA Excel Recordset - Abfrage auf SQL-Server (4)

Frage von Aximand zum Thema VB for Applications ...

Microsoft Office
gelöst Excel-Formel oder VBA (7)

Frage von nicki01 zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
Windows Userverwaltung
Ausgeschiedene Mitarbeiter im Unternehmen - was tun mit den AD Konten? (34)

Frage von patz223 zum Thema Windows Userverwaltung ...

LAN, WAN, Wireless
gelöst Server erkennt Client nicht wenn er ausserhalb des DHCP Pools liegt (28)

Frage von Mar-west zum Thema LAN, WAN, Wireless ...

LAN, WAN, Wireless
FritzBox, zwei Server, verschiedene Netze (21)

Frage von DavidGl zum Thema LAN, WAN, Wireless ...

Viren und Trojaner
Aufgepasst: Neue Ransomware Goldeneye verbreitet sich rasant (20)

Link von Penny.Cilin zum Thema Viren und Trojaner ...