AW: ordnerinhalt als xls-tabelle
23.04.2008 15:40:00
Rudi
Hallo,
in ein Modul und Dateiliste starten:
Option Explicit
Dim wksInhalt As Worksheet, vntFiles(), lngFiles As Long
Sub DateiListe()
Dim FSO As Object, oFolder As Object
Dim strFolder As String
With Application.FileDialog(4) '1=Open; 2=SaveAs; 3=FilePicker; 4=FolderPicker
.AllowMultiSelect = False
.InitialFileName = "n:\"
.InitialView = 2 '1=Liste; 2=Details; 3=properties; 4=Preview; 5=Thumbnail; 6=LargeIcons; _
7=SmallIcons
.Title = "Bitte einen Ordner wählen"
If .Show = -1 Then
strFolder = .SelectedItems(1)
End If
End With
If strFolder = "" Then Exit Sub
GetMoreSpeed
Set wksInhalt = ThisWorkbook.Sheets("Inhalt")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.getfolder(strFolder)
lngFiles = 1
With wksInhalt
.Cells.ClearContents
.Cells(1, 1) = "Name"
.Cells(1, 2) = "Ext"
.Cells(1, 3) = "Bemerkung"
.Cells(1, 4) = "Ordner"
.Cells(1, 5) = "kB"
.Cells(1, 6) = "le.Änd."
.Cells(1, 7) = "Erstellt"
.Cells(1, 8) = "Pfad"
.Cells(1, 9) = "Link"
.Range(.Cells(1, 1), .Cells(1, 5)).Font.Bold = True
End With
prcFiles oFolder
prcSubFolders oFolder
With wksInhalt
.Range(.Cells(2, 1), .Cells(lngFiles, UBound(vntFiles, 1))) = WorksheetFunction.Transpose( _
vntFiles)
.Activate
End With
GetMoreSpeed False
End Sub
Sub prcSubFolders(oFolder)
Dim oSubFolder As Object
For Each oSubFolder In oFolder.subfolders
prcFiles oSubFolder
prcSubFolders oSubFolder
Next
End Sub
Sub prcFiles(oFolder)
Dim oFile As Object
For Each oFile In oFolder.Files
ReDim Preserve vntFiles(1 To 9, 1 To lngFiles)
vntFiles(2, lngFiles) = GetExtension(oFile.Name)
vntFiles(1, lngFiles) = Left(oFile.Name, Len(oFile.Name) - Len(vntFiles(2, lngFiles)) - 1) _
vntFiles(4, lngFiles) = oFolder
vntFiles(5, lngFiles) = Int(oFile.Size / 1024)
vntFiles(6, lngFiles) = oFile.datelastmodified
vntFiles(7, lngFiles) = oFile.datecreated
vntFiles(8, lngFiles) = oFile.Path
vntFiles(9, lngFiles) = "=hyperlink(""" & oFile.Path & """;""" & "Klick" & """)"
lngFiles = lngFiles + 1
Next
End Sub
Private Function GetExtension(strFile As String) As String
If InStrRev(strFile, ".") > 0 Then
GetExtension = Right(strFile, Len(strFile) - InStrRev(strFile, "."))
Else
GetExtension = ""
End If
End Function
Sub GetMoreSpeed(Optional ByVal Modus As Boolean = True)
With Application
.ScreenUpdating = Not Modus
.EnableEvents = Not Modus
' .DisplayAlerts = Not Modus
.Calculation = IIf(Modus = True, xlManual, xlAutomatic)
.Cursor = IIf(Modus = True, 2, -4143)
End With
End Sub
Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe