Herbers Excel-Forum - das Archiv

DateiVerzeichnis ComboBox

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: DateiVerzeichnis ComboBox
von: Joe

Geschrieben am: 15.12.2006 11:49:23
Hallo,
ich benutze folgenden Code um aus einem Verzeichnis alle Dateinamen auszulesen und in einer Combobox ausgeben. Ich möchte aber zusätzlich zu den Dateinamen noch das Dateidatum auslesen und neben dem Dateinamen in der ComboBox anzeigen. Außerdem sollen die Dateien absteigend nach Datum sortiert sein. Hat jemand eine Idee?
Gruß Joe
Private Sub CommandButton1_Click()
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\")
Set fc = f.Files
For Each f1 In fc
UserForm1.ComboBox1.AddItem (f1.Name)
Next
UserForm1.ComboBox1.ListIndex = 0
End Sub

Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Heiko S.

Geschrieben am: 15.12.2006 13:07:22
Hey Jo,
z.B. so, Voraussetzung die Combox hat zwei Spalten.
Private Sub CommandButton1_Click()
Dim fs, f, f1, fc, s
Dim lngI As Long
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("H:\EXCEL")
Set fc = f.Files
lngI = 0
For Each f1 In fc
UserForm1.ComboBox1.AddItem f1.Name
Set s = fs.getfile(f1)
UserForm1.ComboBox1.List(lngI, 1) = s.datecreated
lngI = lngI + 1
Next
SortBox UserForm1.ComboBox1, 2, 2, 3
UserForm1.ComboBox1.ListIndex = 0
End Sub
Public Sub SortBox(cltBox As Control, intSpalten As Integer, _
intSpalte As Integer, Optional bytWie As Byte = 1)
' So DIS 28.04.05
' SortBox sortiert nicht gebundene List- und Comboboxen. Gebundene List- und Comboboxen
' (Angabe bei RowSource oder ListFillRange) können NICHT sortiert werden.
' cltBox     : Name der Listbox die sortiert werden soll.
' intSpalten : Wieviele Spalten sollen mit sortiert werden. Sollte der Anzahl der Spalten
'              in der Listbox entsprechen
' intSpalte  : Nach welcher Spalte soll sortiert werden.
' bytWie     : 1 oder Nicht angegeben als Text
'            : 2 als Zahl, dann muß die ganze Spalte Zahlen enthalten.
'            : 3 als Datum, dann muß die ganze Spalte Datumwerte enthalten.
' Aufruf zum Beispiel so: ListBox1 mit 7 Spalten, Sortierung nach Spalte 1 Sortierordnung Text
' SortBox ListBox1, 7, 1  oder SortBox ListBox1, 7, 1, 1
' Oder so    : Listbox17 mit 2 Spalten, Sortierung nach Spalte 2 Sortierordnung Zahlen
' SortBox ListBox17, 2, 2, 2
Dim intLast As Integer, intNext As Integer, intCounter As Integer, intFehler As Integer
Dim strTmp As String, strFehlertext As String
Dim variLast As Variant, variNext As Variant
On Error GoTo Errorhandler
intFehler = 0
With cltBox
For intLast = 0 To .ListCount - 1
For intNext = intLast + 1 To .ListCount - 1
Select Case bytWie
Case 1
intFehler = 0
variLast = CStr(.List(intLast, intSpalte - 1))
variNext = CStr(.List(intNext, intSpalte - 1))
Case 2
intFehler = 1
variLast = CDbl(.List(intLast, intSpalte - 1))
variNext = CDbl(.List(intNext, intSpalte - 1))
Case 3
intFehler = 2
variLast = CDate(.List(intLast, intSpalte - 1))
variNext = CDate(.List(intNext, intSpalte - 1))
End Select
intFehler = 0
If variLast > variNext Then
For intCounter = 0 To intSpalten - 1
strTmp = CStr(.List(intLast, intCounter))
.List(intLast, intCounter) = CStr(.List(intNext, intCounter))
.List(intNext, intCounter) = strTmp
Next intCounter
End If
Next intNext
Next intLast
End With
Exit Sub
Errorhandler:
Select Case intFehler
Case 0
strFehlertext = "In der Listbox Sortierung ist ein Fehler aufgetreten !"
Case 1
strFehlertext = "Nicht alle Werte in der zu sortierenden Spalte sind Zahlen !"
Case 2
strFehlertext = "Nicht alle Werte in der zu sortierenden Spalte sind Datumswerte !"
Case Else
strFehlertext = "Unerwarteter Fehler !"
End Select
MsgBox strFehlertext & " Bitte informieren Sie 'So' ! " & vbCr & vbCr & _
"Fehler aufgetreten in " & cltBox.Name & " !" & vbCr & _
"Fehlernummer = " & Err.Number & vbCr & _
"Fehlerbeschreibung = " & Err.Description & vbCr & _
"Fehlersource = " & Err.Source, vbCritical, " Meldung vom Makro SortBox !"
End Sub

Gruß Heiko
PS: Rückmeldung wäre nett !
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Joe

Geschrieben am: 15.12.2006 15:51:02
Hallo Heiko,
klappt so weit prima. Ich bekomme aber als "StartWert" in der ComboBox nur den Wert der 1.Spalte. Beim aufklappen der Liste sind beide Spalte da. es liegt anscheinend an
UserForm1.ComboBox1.ListIndex = 0
Anscheinend nimmt dieser Befehl nur den Wert der ersten Spalte. Hast du eine Idee?
Gruß Joe
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Heiko S.

Geschrieben am: 15.12.2006 18:24:44
Hallo Joe,
schön das es läuft.
Das mit der einen Spalte kann ich dir nicht genau beantworten, wenn ich mich recht erinnere dann ist das bei Comboboxen so. Da das eine Art überschrift ist, aber sicher bin ich mir nicht.
Mit
UserForm1.ComboBox1.ListIndex = 0
UserForm1.ComboBox1.Text = "Bitte File wählen"
taucht dann in der ersten Zeile der Text auf, mit dem du andeuten kannst was der Bediener will.
Bei einer Listbox geht es zweispaltig von der ersten Zeile an, mußt sonst die nehmen.
Gruß Heiko
PS: Rückmeldung wäre nett !
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: fcs

Geschrieben am: 15.12.2006 20:51:02
Hallo Joe,
Comboboxen zeigen für die ausgewählte Zeile immer nur den Wert aus der Spalte an, die unter den Eigenschaften als BoundColumn festgelegt wird. Die anderen Spalten der Auswahlliste dienen bei der Auswahl nur als als Information.
unter VBA kann man nach der Auswahl eines Wertes in Userforms mit
Wert1 = Me.Combobox1.Value
auf den ausgewählten Wert zugreifen
mit
Wert2 = Me.Combobox1.List(Me.Combobox1.Listindex,1)
z.B. auf den Wert in der 2. Spalte
"Me" ggf. durch den Namen des Userforms ersetzen, wenn von außerhalb der Userformprozeduren auf die Werte zugegriffen werden soll.
Gruss
Franz
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Joe

Geschrieben am: 18.12.2006 09:28:26
Hallo Heiko,
ok ich kann damit leben, dass nur eine Spalte in der ComboBoxen angezeigt wird. Aber ich brauche nochmal deine Hilfe.
Ich möchte noch zusätzlich eine dritte Spalte hinzufügen und zwar soll aus allen in der ersten Spalte angezeigten Excel-Dateien ein Wert ausgelesen werden ohne die Datei zu öffnen.
Der Wert steht immer im Worksheet("KALK").Range("D3")
Gruß
Joe
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Heiko S.

Geschrieben am: 18.12.2006 13:48:23
Hallo Joe,
na weil nächstes Wochenende Weihnachten ist will ich mal Geschenke auspacken.Die Combobox muss drei Spalten haben und ich geh davon das in den Dateien die du einliest die Tabelle KALK vorhanden ist, wenn nicht dann gibt es eine Fehlermeldung.
Option Explicit
Private Sub CommandButton1_Click()
Dim fs, f, f1, fc, s
Dim lngI As Long
Dim strPath As String
' Pfad anpassen
strPath = "H:\EXCEL\Muell"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(strPath)
Set fc = f.Files
lngI = 0
For Each f1 In fc
UserForm1.ComboBox1.AddItem f1.Name
Set s = fs.getfile(f1)
UserForm1.ComboBox1.List(lngI, 1) = s.datecreated
If InStr(1, s.Type, "Excel") > 0 Then
UserForm1.ComboBox1.List(lngI, 2) = GetValue(strPath, s.Name, "KALK", "D7")
End If
lngI = lngI + 1
Next
SortBox UserForm1.ComboBox1, 2, 2, 3
UserForm1.ComboBox1.ListIndex = 0
End Sub
Public Sub SortBox(cltBox As Control, intSpalten As Integer, _
intSpalte As Integer, Optional bytWie As Byte = 1)
' So DIS 28.04.05
' SortBox sortiert nicht gebundene List- und Comboboxen. Gebundene List- und Comboboxen
' (Angabe bei RowSource oder ListFillRange) können NICHT sortiert werden.
' cltBox     : Name der Listbox die sortiert werden soll.
' intSpalten : Wieviele Spalten sollen mit sortiert werden. Sollte der Anzahl der Spalten
'              in der Listbox entsprechen
' intSpalte  : Nach welcher Spalte soll sortiert werden.
' bytWie     : 1 oder Nicht angegeben als Text
'            : 2 als Zahl, dann muß die ganze Spalte Zahlen enthalten.
'            : 3 als Datum, dann muß die ganze Spalte Datumwerte enthalten.
' Aufruf zum Beispiel so: ListBox1 mit 7 Spalten, Sortierung nach Spalte 1 Sortierordnung Text
' SortBox ListBox1, 7, 1  oder SortBox ListBox1, 7, 1, 1
' Oder so    : Listbox17 mit 2 Spalten, Sortierung nach Spalte 2 Sortierordnung Zahlen
' SortBox ListBox17, 2, 2, 2
Dim intLast As Integer, intNext As Integer, intCounter As Integer, intFehler As Integer
Dim strTmp As String, strFehlertext As String
Dim variLast As Variant, variNext As Variant
On Error GoTo Errorhandler
intFehler = 0
With cltBox
For intLast = 0 To .ListCount - 1
For intNext = intLast + 1 To .ListCount - 1
Select Case bytWie
Case 1
intFehler = 0
variLast = CStr(.List(intLast, intSpalte - 1))
variNext = CStr(.List(intNext, intSpalte - 1))
Case 2
intFehler = 1
variLast = CDbl(.List(intLast, intSpalte - 1))
variNext = CDbl(.List(intNext, intSpalte - 1))
Case 3
intFehler = 2
variLast = CDate(.List(intLast, intSpalte - 1))
variNext = CDate(.List(intNext, intSpalte - 1))
End Select
intFehler = 0
If variLast > variNext Then
For intCounter = 0 To intSpalten - 1
strTmp = CStr(.List(intLast, intCounter))
.List(intLast, intCounter) = CStr(.List(intNext, intCounter))
.List(intNext, intCounter) = strTmp
Next intCounter
End If
Next intNext
Next intLast
End With
Exit Sub
Errorhandler:
Select Case intFehler
Case 0
strFehlertext = "In der Listbox Sortierung ist ein Fehler aufgetreten !"
Case 1
strFehlertext = "Nicht alle Werte in der zu sortierenden Spalte sind Zahlen !"
Case 2
strFehlertext = "Nicht alle Werte in der zu sortierenden Spalte sind Datumswerte !"
Case Else
strFehlertext = "Unerwarteter Fehler !"
End Select
MsgBox strFehlertext & " Bitte informieren Sie 'So' ! " & vbCr & vbCr & _
"Fehler aufgetreten in " & cltBox.Name & " !" & vbCr & _
"Fehlernummer = " & Err.Number & vbCr & _
"Fehlerbeschreibung = " & Err.Description & vbCr & _
"Fehlersource = " & Err.Source, vbCritical, " Meldung vom Makro SortBox !"
End Sub
Function GetValue(path, file, sheet, ref)
' VBA Function to Get a Value From a Closed File
' VBA does not include a method to retrieve a value from a closed file. You can, however,
' take advantage of Excel's ability to work with linked files.
' This tip contains a VBA function that retrieves a value from a closed workbook.
' It does by calling an XLM macro.
' The GetValue function, listed below takes four arguments:
' path:  The drive and path to the closed file (e.g., "d:\files")
' file:  The workbook name (e.g., "99budget.xls")
' sheet: The worksheet name (e.g., "Sheet1")
' ref:   The cell reference (e.g., "C4")
' Aufruf z.B.  i = GetValue("C:\test", "test1.xls", "Tabelle1", "A1")
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Gruß Heiko
PS: Rückmeldung wäre nett !
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Heiko S.

Geschrieben am: 18.12.2006 14:02:06
Hallo Joe,
und Nikolaus war ja auch schon, drum hier noch ne Version mit Überprüfung ob Blatt vorhanden.
Option Explicit
Private Sub CommandButton1_Click()
Dim fs, f, f1, fc, s
Dim lngI As Long
Dim strPath As String
' Pfad anpassen
strPath = "H:\EXCEL\Muell\Test"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(strPath)
Set fc = f.Files
lngI = 0
For Each f1 In fc
Set s = fs.getfile(f1)
If InStr(1, s.Type, "Excel") > 0 Then
UserForm1.ComboBox1.AddItem f1.Name
UserForm1.ComboBox1.List(lngI, 1) = s.datecreated
UserForm1.ComboBox1.List(lngI, 2) = GetValue(strPath, s.Name, "KALK", "D7")
lngI = lngI + 1
End If
Next
SortBox UserForm1.ComboBox1, 2, 2, 3
UserForm1.ComboBox1.ListIndex = 0
End Sub
Public Sub SortBox(cltBox As Control, intSpalten As Integer, _
intSpalte As Integer, Optional bytWie As Byte = 1)
' So DIS 28.04.05
' SortBox sortiert nicht gebundene List- und Comboboxen. Gebundene List- und Comboboxen
' (Angabe bei RowSource oder ListFillRange) können NICHT sortiert werden.
' cltBox     : Name der Listbox die sortiert werden soll.
' intSpalten : Wieviele Spalten sollen mit sortiert werden. Sollte der Anzahl der Spalten
'              in der Listbox entsprechen
' intSpalte  : Nach welcher Spalte soll sortiert werden.
' bytWie     : 1 oder Nicht angegeben als Text
'            : 2 als Zahl, dann muß die ganze Spalte Zahlen enthalten.
'            : 3 als Datum, dann muß die ganze Spalte Datumwerte enthalten.
' Aufruf zum Beispiel so: ListBox1 mit 7 Spalten, Sortierung nach Spalte 1 Sortierordnung Text
' SortBox ListBox1, 7, 1  oder SortBox ListBox1, 7, 1, 1
' Oder so    : Listbox17 mit 2 Spalten, Sortierung nach Spalte 2 Sortierordnung Zahlen
' SortBox ListBox17, 2, 2, 2
Dim intLast As Integer, intNext As Integer, intCounter As Integer, intFehler As Integer
Dim strTmp As String, strFehlertext As String
Dim variLast As Variant, variNext As Variant
On Error GoTo Errorhandler
intFehler = 0
With cltBox
For intLast = 0 To .ListCount - 1
For intNext = intLast + 1 To .ListCount - 1
Select Case bytWie
Case 1
intFehler = 0
variLast = CStr(.List(intLast, intSpalte - 1))
variNext = CStr(.List(intNext, intSpalte - 1))
Case 2
intFehler = 1
variLast = CDbl(.List(intLast, intSpalte - 1))
variNext = CDbl(.List(intNext, intSpalte - 1))
Case 3
intFehler = 2
variLast = CDate(.List(intLast, intSpalte - 1))
variNext = CDate(.List(intNext, intSpalte - 1))
End Select
intFehler = 0
If variLast > variNext Then
For intCounter = 0 To intSpalten - 1
strTmp = CStr(.List(intLast, intCounter))
.List(intLast, intCounter) = CStr(.List(intNext, intCounter))
.List(intNext, intCounter) = strTmp
Next intCounter
End If
Next intNext
Next intLast
End With
Exit Sub
Errorhandler:
Select Case intFehler
Case 0
strFehlertext = "In der Listbox Sortierung ist ein Fehler aufgetreten !"
Case 1
strFehlertext = "Nicht alle Werte in der zu sortierenden Spalte sind Zahlen !"
Case 2
strFehlertext = "Nicht alle Werte in der zu sortierenden Spalte sind Datumswerte !"
Case Else
strFehlertext = "Unerwarteter Fehler !"
End Select
MsgBox strFehlertext & " Bitte informieren Sie 'So' ! " & vbCr & vbCr & _
"Fehler aufgetreten in " & cltBox.Name & " !" & vbCr & _
"Fehlernummer = " & Err.Number & vbCr & _
"Fehlerbeschreibung = " & Err.Description & vbCr & _
"Fehlersource = " & Err.Source, vbCritical, " Meldung vom Makro SortBox !"
End Sub
Function GetValue(path, file, sheet, ref)
' VBA Function to Get a Value From a Closed File
' VBA does not include a method to retrieve a value from a closed file. You can, however,
' take advantage of Excel's ability to work with linked files.
' This tip contains a VBA function that retrieves a value from a closed workbook.
' It does by calling an XLM macro.
' The GetValue function, listed below takes four arguments:
' path:  The drive and path to the closed file (e.g., "d:\files")
' file:  The workbook name (e.g., "99budget.xls")
' sheet: The worksheet name (e.g., "Sheet1")
' ref:   The cell reference (e.g., "C4")
' Aufruf z.B.  i = GetValue("C:\test", "test1.xls", "Tabelle1", "A1")
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
If TypeName(GetValue) = "Error" Then
GetValue = "Sheet Not Found"
End If
End Function

Gruß Heiko
PS: Rückmeldung wäre nett !
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Joe

Geschrieben am: 18.12.2006 14:14:06
Hallo Heiko,
erstmal DANKE an den Weihnachtsmann ... ja das klappt schon genauso wie ich es haben will ... aber diese GetValue Function (die kenne ich) ist sehr langsam da sie glaube ich mit "Verknüpfungen" arbeitet. Bei 60 Abfragen in der Schleife kann man Kaffeetrinken.
Das Sheet("KALK") ist immer in der Arbeitsmappe vorhanden, trotzdem Danke für die 2te Version.
Ich nehme das glaube ich wieder raus, sorry. Ich habe auch keine andere Idee wie man das beschleunigen kann. Der Zugriff auf geschlossenen Excel Dateien ist ja ein Problem. Oder hast du noch eine Idee?
Ansonsten beende ich die Frage, da das Grundproblem 100% beantwortet ist.
Gruß
Joe
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Heiko S.
Geschrieben am: 18.12.2006 14:26:31
Hallo Joe,
ohne GetValue geht es nur wenn man die Datei öffnet sich D7 ausliest und wieder schliest, aber ob das schneller als die EXCEL4Macros ist bezweifele ich.
Gruß Heiko
Bild

Betrifft: AW: DateiVerzeichnis ComboBox
von: Joe

Geschrieben am: 18.12.2006 14:37:54
Hallo Heiko,
ja da hast du leider recht für den direkten Weg... es gibt aber noch andere Möglickeiten da sich die abzufragenden Dateien nach dem Speichern nicht nehr ändern z.B.
- eine Art Batchlauf und abspeichern in einer separten Index-Datei (Datenbank) oder
- beim speichern der Originaldatei direkt einen Wert in die Index-Datei (Datenbank) schreiben
Ich denke drüber nach.
Trotzdem erstmal Danke.
Gruß
Joe
 Bild
Excel-Beispiele zum Thema "DateiVerzeichnis ComboBox "
ComboBox als Symbol Alle ComboBoxes auf Position 1
Werte in eine ComboBox einer UserForm übernehmen ComboBox in Abhängigkeit einer 2. ComboBox ein-/ausblenden
ComboBox mit Monatsnamen füllen Mehrspaltige ComboBox
ComboBox2 in Abhängigkeit von ComboBox1 bestücken Nur Zellen mit Inhalt in ComboBox listen
Neue UserForm bedingt nach ComboBox-Auswahl aufrufen Neues Element in ComboBox