Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1556to1560
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Prüfen ob Tabellenblatt in anderer Datei existiert

Prüfen ob Tabellenblatt in anderer Datei existiert
09.05.2017 08:07:18
Rainer
Hallo Excelfreunde,
mit folgendem Code prüfe ich ob Exceldateien existieren und wenn ja, dann werden Hyperlinks erzeugt.
"LinkAdress" und "LinkSubAdress" sind in Tabellenspalten gespeichert.
Z.B. "G:\1.xls" und "Berlin_345!A1"

For i = 3 to LastRow
LinkAdress = Worksheets(1).Range("F" & i)
LinkSubAdress = Worksheets(1).Range("G" & i)
Z = InStrRev(LinkAdress, "\")
Linktext = Mid(LinkAdress, Z + 1, 256)
Z = InStrRev(LinkSubAdress, "!")
Linktext = Linktext & "_" & Mid(LinkSubAdress, 1, Z - 1)
If Dir(LinkAdress)  "" Then        'Datei existiert
Worksheets(1).Range("B" & i).Parent.Hyperlinks.Add Anchor:=Range("B" & i),  _
Address:=LinkAdress, _
SubAddress:=LinkSubAdress, TextToDisplay:=Linktext
Else
Worksheets(1).Range("B" & i) = Linktext
Worksheets(1).Range("B" & i).Font.Color = vbRed
End If
Next i
Gibt es eine Möglichkeit, den Befehl "Dir" zu ersetzen, so dass auch die Existenz des Sheet geprüft werden kann? Aus Geschwindigkeitsgründen ist es wünschenswert, wenn die Zieldateien nicht geöffnet werden.
Viele Grüße,
Rainer

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Prüfen ob Tabellenblatt in anderer Datei existiert
09.05.2017 08:35:13
EtoPHG
Hallo Rainer,
IMHO eine Frage die dem angegebenen VBA Level nicht gerecht wird!
DIR ist eine Schnittstelle auf das Dateisystem des Betriebssystem, nicht aber auf den Inhalt von Dateien! Also: Nein
Mit 'Zieldateien' meinst du wohl Quelldateien, d.h. Dateien von denen Informationen bezogen werden sollen. Warum der Mythos "Aus geschlossenen Dateien Inhalts-Informationen zu beziehen" sich seit 20 Jahren hartnäckig in der XL-Community hält, geht mir einfach nicht in den Kopf! Zum Auslesen von Dateiinhalten (und dazu gehören natürlich die vorhandenen Worksheets bei XL-Dateien) muss die Datei mindestens für einen Read-Zugriff geöffnet sein. (Punkt)
Gruess Hansueli
Anzeige
AW: Prüfen ob Tabellenblatt in anderer Datei existiert
10.05.2017 06:16:26
Rainer
Hallo Hansueli,
Ich möchte nicht näher auf ihren Beitrag eingehen, aber verweise auf die Lösung von Nutzer "UweD", welche (so scheint es mir mit VBA-Level "Einzeller") den sogenannten "Mythos" doch in Realität verwandelt.
Mit freundlichen Grüßen,
Rainer
AW: Prüfen ob Tabellenblatt in anderer Datei existiert
09.05.2017 09:28:16
UweD
Hallo
bei VBA=gut müsstest du das hier bei dir einbauen können..
Sub Makro1()
    p = "C:\Temp\"
    f = "Mappe1.xlsx"
    s = "ABC"
    R = "A1"
    
    
    MsgBox GetValue(p, f, s, R)
End Sub



Private Function GetValue(path, File, sheet, ref)
    Dim arg As String
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & File) = "" Then
        GetValue = "Datei fehlt"
        Exit Function
    End If
    arg = "'" & path & "[" & File & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
    
    GetValue = IIf(IsError(ExecuteExcel4Macro(arg)), "Blatt fehlt", "Blatt Vorhanden")
End Function

LG UweD
Anzeige
AW: Prüfen ob Tabellenblatt in anderer Datei existiert
10.05.2017 06:10:56
Rainer
Hallo Uwe,
vielen Dank für die Lösung des Problems.
Ich hätte noch eine Verständnisfrage zur Function "GetValue":

arg = "'" & path & "[" & File & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
Der markierte Teil ist doch eigentlich überflüssig, weil "Range" dann doppelt gemoppelt ist? Bzw. zur einfachen Prüfung ob das Sheet existiert reicht mir doch "Range("A1")" in jedem Falle aus, dann muss ich die Variable "ref" nicht übergeben?
Also entweder prüfen ob Sheet existiert vereinfacht mit:

arg = "'" & path & "[" & File & "]" & sheet & "'!" & _
Range("A1").Address(, , xlR1C1)
GetValue = IIf(IsError(ExecuteExcel4Macro(arg)), "Blatt fehlt", "Blatt vorhanden")
oder unter Angabe der Zelle zum Wert auslesen:

arg = "'" & path & "[" & File & "]" & sheet & "'!" & _
Range(ref).Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
Danke für deine Hilfe.
Gruß,
Rainer
Anzeige
ja, du hast Recht...
10.05.2017 09:46:48
UweD
..scheint keinen Einfluss auf das Ausführen zu haben.
Hab aber in der Literatur nichts weiter dazu gefunden.
Im Gegenteil, es wird immer diese Syntax verwendet.
Evtl. hat jemand Anderes eine Erklärung
LG UweD
Erklärung findest du in XL4 Benutzerhandbuch
10.05.2017 15:36:31
EtoPHG
Uwe & Rainer,
Aber die dürfte im Netz schwer auffindbar sein. Vielleicht in Buch-Antiquariaten?
EXEL4 Makros sind nur noch aus Kompatibilitätsgründen in den neueren XL Versionen vorhanden.
Sie verlangen grundsätzlich immer eine R1C1-Notation von Zellbezügen.
Die 'Originalfunktion' von UweD funktioniert, weil die Information über Blattnamen aus den Dateieigenschaften der Datei ausgelesen wird. Sie funktioniert selbst dann, wenn der referenzierte Blattname ein Diagrammblatt ist, was völlig absurd ist, weil ein solches keine Zelladressen enthält. Wenn die referenzierte Datei geöffnet ist und die Funktion auf ein Diagrammblatt referenziert, wird sie einen 1004-Fehler werfen, der nicht durch IsError abgefangen werden kann!
Mit der Einschränkung GetValue = ExecuteExcel4Macro(arg) wir der 'letzte gespeicherte' Inhalt der Zelle ausgelesen. Enthält die Zelle volatile Daten (z.B. =HEUTE()), sind diese nicht neu berechnet, bzw. aktualisiert!
Soviel zu VBA-Level "Einzeller" und Mythos .... ;-)
Gruess Hansueli
Anzeige
Modern times
11.05.2017 03:06:09
Rainer
Hallo Hansueli,
vielen Dank für deine Erläuterungen.
Vorab: Der VBA-Level "Einzeller" hat sich ausdrücklich nicht auf deine Fähigkeiten bezogen, aber lassen wir es dabei.
Zu Zeiten von Excel 4.0 war der hauptsächliche Einsatzzweck meines 486ers noch "Sid Meiers COLONIZATION". Mit Excel quäle ich mich seit Version 2000 herum, mit VBA erst viel später.
Aber zurück zum Thema: Die Detailfrage bezog sich vor allem auf die Variable "arg" in der Function "GetValue". Da ist der Range zweimal angegeben und die Frage bezog sich auf den Sinn oder Unsinn dieser Syntax.

arg = "'" & path & "[" & File & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
Gibt es deiner Meinung nach evtl. auch noch einen "moderneren" Weg? Z.B. die "workbook.xml" zu extrahieren und dann diesen Abschnitt mit VBA zu prüfen:

(sheets)(sheet name="Tabelle1" sheetId="1" r:id="rId1"/)(sheet name="Diagramm1" sheetId="3" r: _
id="rId2"/)(sheet name="Tabelle2" sheetId="2" r:id="rId3"/)(/sheets)
(Achtung: Ersetzungen der Kleiner/Größer-Zeichen durch Klammern, damit es den Code anzeigen kann)
Gruß,
Rainer
Anzeige
Sinn oder Unsinn von Range(ref).Range("A1")...
11.05.2017 09:26:05
Range(ref).Range("A1")...
Hallo Rainer,
Eine Syntax wie
Range(ref).Range("A1").Address(, , xlR1C1)
kann durchaus Sinn machen.
Für ref="A1" ist sie zwar redundant. Für jeden anderen Wert von ref liefert sie aber eine andere R1C1-Adresse. Probier doch mal:
Sub TEST()
Dim ref As String
ref = "A1"
MsgBox Range(ref).Range("A1").Address(, , xlR1C1), , ref
ref = "F10:H25"
MsgBox Range(ref).Range("A1").Address(, , xlR1C1), , ref
End Sub
Deiner 2ten Frage, bezgl. workbook.xml, kann ich, mangels Anforderungsbeschreibung, leider nicht folgen.
Gruess Hansueli
Anzeige
AW: Sinn oder Unsinn von Range(ref).Range("A1")...
11.05.2017 12:08:25
Range(ref).Range("A1")...
Hallo Hansueli,

Sub TEST()
Dim ref As String
ref = "A1"
MsgBox Range(ref).Range("A1").Address(, , xlR1C1), , ref
ref = "F10:H25"
MsgBox Range(ref).Range("A1").Address(, , xlR1C1), , ref
End Sub
Wenn ich den Range durch die Variable "ref" definiere, warum schreibe ich direkt dahinter nochmal "A1"? Dieses zweite "A1" hat doch (anscheinend) keinerlei Funktion?
Die Frage nach der "workbook.xml" nochmal detaillierter:
Wenn ich eine Exceldatei als ZIP Datei öffne, dann sehe ich eine Datenstruktur. Im Unterordner "xl" finde ich die Datei "workbook.xml". In dieser findet sich der Abschnitt:

(sheets)(sheet name="Tabelle1" sheetId="1" r:id="rId1"/)(sheet name="Diagramm1" sheetId="3" r:  _
_
id="rId2"/)(sheet name="Tabelle2" sheetId="2" r:id="rId3"/)(/sheets)
Wenn ich diesen Weg nun über ein VBA Skript nachstellen könnte, dann könnte man auch so die existierenden Sheet-Namen ermitteln und so die ursprüngliche Aufgabe (Prüfen ob Tabellenblatt in anderer Datei existiert) lösen ohne den Befehl "ExecuteExcel4Macro(arg)", bei dem du angemerkt hast, dass er sehr alt ist und nur aus Kompatibilitätsgründen existiert.
Gruß,
Rainer
Anzeige
AW: Sinn oder Unsinn von Range(ref).Range("A1")...
11.05.2017 22:28:08
Range(ref).Range("A1")...
Hallo Rainer,
Ich verstehe deine Frage nicht. Die zweite Range ist die Angabe der Adresse, innerhalb der Range der zweiten. Setze doch mal B2 anstelle von A1 im 2ten Beispiel ein!
Warum willst du den Umweg über das XML machen, da ist es doch einfacher die Mappe normal zu öffnen und die Sheets auszulesen.
Irgendwie check ich das Ganze einfach nicht!
Gruess Hansueli
AW: Sinn oder Unsinn von Range(ref).Range("A1")...
12.05.2017 07:23:07
Range(ref).Range("A1")...
Hallo Hansueli,
ich denke ich habe es jetzt verstanden.

Sub TEST()
Dim ref As String
ref = "F10:H25"
MsgBox Range(ref).Range("A1").Address(, , xlR1C1), , ref
End Sub
Liefert als Bezug die Zelle "F10".

Sub TEST()
Dim ref As String
ref = "F10:H25"
MsgBox Range(ref).Range("B2").Address(, , xlR1C1), , ref
End Sub
Liefert als Bezug die Zelle "G11". Richtig?
Um damit die Ausgangsfrage zu beantworten:
Der zweite "Range" stellt sicher, dass immer nur eine einzige Zelle als Bezug existiert, auch wenn vorher im ersten "Range" ein Bereich definiert wurde. Richtig?
Zum "workbook.xml" schreibe ich einen eigenen Beitrag, der Übersicht wegen.
Gruß,
Rainer
Anzeige
Alternative: workbook.xml kopieren und lesen
12.05.2017 07:40:08
Rainer
Hallo Uwe, Hansueli, alle anderen
Eine alternative Lösung habe ich "erschaffen" (zumindest zusammenkopiert).
Die Lösung von UweD ist super und flexibler (Da sie auch Zellenwerte auslesen kann).
Meine alternative sieht so aus:

Sub Unzip() 'Quelle: http://www.rondebruin. _
nl/win/s7/win002.htm
Dim FSO As Object
Dim oApp As Object
Dim Fname As Variant
Dim FileNameFolder As Variant
Dim DefPath As String
Fname = "E:\test\Mappe1.xlsx"
If Fname = False Then
'Do nothing
Else
'Make a zip file
Zname = Left(Fname, InStr(Fname, ".")) & "zip"
Name Fname As Zname
'Destination folder
DefPath = "E:\test\"    ' "\" Then
DefPath = DefPath & "\"
End If
FileNameFolder = DefPath
'        'Delete all the files in the folder DefPath first if you want
'        On Error Resume Next
'        Kill DefPath & "*.*"
'        On Error GoTo 0
'Extract the files into the Destination folder
Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Zname).items.Item("xl\workbook. _
xml")
'MsgBox "You find the files here: " & FileNameFolder
On Error Resume Next
Set FSO = CreateObject("scripting.filesystemobject")
FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
End If
'Reset original file name
Name Zname As Fname
End Sub
Dann öffnen der XML:

Sub Read_Sheet_Names()
strTargetFile = "E:\test\workbook.xml"
Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList)
NameCol = Application.WorksheetFunction.Match("name", Range("1:1"), 0)
End Sub
Geht leider aber nur mit Schreibezugriff auf die Excel-Datei, da man den Filename in "*.zip" ändern muss, um die workbook.xml zu kopieren. Hat evtl. jemand von den Profis hier noch eine Idee, wie ich dem Code "vormache", dass auch eine "xlsx" als "zip" zu öffnen geht?
Abschließend noch die Beantwortung von Hansuelis Frage: Wozu der Aufwand, wenn ich auch einfach die Datei öffnen kann?
Ich bin der Meinung, dass es so schneller geht. Das extrahieren der Information "Welche Sheetnamen gibt es" geht schneller, als wenn ich dazu (viele und große) Exceldateien öffnen und wieder schließen muss.
Gruß,
Rainer
Anzeige
Alternative DSOFile.dll
12.05.2017 09:11:22
EtoPHG
Hallo Rainer,
Lies: Document Properties
und dort den 2 untersten Abschnitt "Returning Property Values From A Closed File".
Gruess Hansueli

321 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige