Top-Themen

Aktuelle Themen (A bis Z)

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, 10829 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 ..
Ähnliche Inhalte
VB for Applications
Excel VBA Formel "WENN" einfügen
gelöst Frage von chef1568VB for Applications2 Kommentare

Hallo zusammen, ich möchte per Excel-Makro folgende Formel eintragen: EXCEL: VBA: Leider bekomme ich hier immer einen anwendungsrelevanten Fehler ...

Microsoft Office
Excel-Formel oder VBA
gelöst Frage von nicki01Microsoft Office7 Kommentare

Hallo Ich weiß nicht, ob ich hier richtig bin. Mein Problem: Ich habe mir (mit Mühe) ein Excel-Haushaltsbuch aufgebaut. ...

Microsoft Office
Excel Formel in VBA fummeln
gelöst Frage von YotYotMicrosoft Office1 Kommentar

Moin! Ich habe da so eine neckische kleine Excel-Tabelle mit derzeit rund 9 Mio. gefüllten Zellen, von denen 2 ...

Microsoft Office
Excel lösung in VBA oder mit Formel gesucht..
gelöst Frage von Grave111Microsoft Office2 Kommentare

Guten Tag, ich habe folgendes Szenario Das erste Excel Blatt beinhaltet 2 Spalten mit Daten (Spalte A beinhaltet Artikelnummern) ...

Neue Wissensbeiträge
Apple

IOS 11.2.1 stopft HomeKit-Remote-Lücke

Tipp von BassFishFox vor 13 StundenApple

Das Update für iPhone, iPad und Apple TV soll die Fernsteuerung von Smart-Home-Geräten wieder in vollem Umfang ermöglichen. Apple ...

Windows 10

Windows 10 v1709 EN murkst bei den Regionseinstellungen

Tipp von DerWoWusste vor 18 StundenWindows 10

Dieser kurze Tipp richtet sich an den kleinen Personenkreis, der Win10 v1709 EN-US frisch installiert und dabei die englische ...

Webbrowser

Kein Ton bei Firefox Quantum über RDP

Tipp von Moddry vor 18 StundenWebbrowser

Hallo Kollegen! Hatte das Problem, dass der neue Firefox bei mir auf der Kiste keinen Ton hat, wenn ich ...

Internet

EU-DSGVO: WHOIS soll weniger Informationen liefern

Information von sabines vor 1 TagInternet4 Kommentare

Wegen der europäische Datenschutzgrundverordnung stehen die Prozesse um die Registrierung von Domains auf dem Prüfstand. Sollte die Forderungen umgesetzt ...

Heiß diskutierte Inhalte
Windows Server
RODC kann nicht aus Domäne entfernt werden
Frage von NilsvLehnWindows Server18 Kommentare

HAllo, ich arbeite in einem Universitätsnetzwerk mit 3 Standorten. Die Standorte haben alle ein ESXi Cluster und auf diesen ...

Windows XP
Windows XP Aktivieren geht nicht
Frage von tetikmiroWindows XP13 Kommentare

Hallo Ich habe einen Windows XP mit einen vCenter Converter umgezogen auf eine ESXI. Soweit funktioniert dies auch ohne ...

Blogs
Immer wiederkehrende PHP Fehlermeldung bei Wordpress UTF-8 - ASCII
gelöst Frage von vcdweltBlogs11 Kommentare

Hi, seit einiger Zeit wird mein error_log meines Wordpress Blogs mit immer der gleichen Fehlermeldung überschwemmt. 14-Dec-2017 08:18:05 UTC ...

Switche und Hubs
Redundante L2 LWL Leitung über 2 Standorte - Spanning Tree - HP Equipment
gelöst Frage von ResolvSwitche und Hubs10 Kommentare

Hallo, ich stehe vor der Herausforderung eine Redundante L2 LWL Leitung über 2 Standorte herzustellen. Grundsätzliches Switching Know How ...