AW: Auslesen von Dateiverzeichnis und Namen
24.04.2007 20:45:00
Dateiverzeichnis
Hallo,
mal als Ansatz:
prcFileListe starten
Option Explicit
Dim wksInhalt As Worksheet, vntFiles(), lngFiles As Long
Sub prcFileListe()
Dim FSO As Object, oFolder As Object
Dim strFolder As String
Application.ScreenUpdating = False
strFolder = fncFolderName
If strFolder = "" Then Exit Sub 'Abbruch
Set FSO = CreateObject("Scripting.FileSystemObject")
Set wksInhalt = Workbooks.Add.Sheets(1)
Set oFolder = FSO.GetFolder(strFolder)
lngFiles = 1
With wksInhalt
.Cells(1, 1) = "Pfad"
.Cells(1, 2) = "kB"
.Cells(1, 3) = "le.Änd."
.Cells(1, 4) = "erstellt"
.Rows(1).Font.Bold = True
End With
prcFiles oFolder
prcSubFolders oFolder
With wksInhalt
.Range(.Cells(2, 1), .Cells(lngFiles, 4)) = WorksheetFunction.Transpose(vntFiles)
With .Columns("B:D")
.Font.ColorIndex = xlAutomatic
.Font.Underline = xlUnderlineStyleNone
End With
Columns("B").NumberFormat = "#,##0.00"
.Columns("C:D").NumberFormat = "DD.MM.YYYY hh:mm:ss"
.Columns.AutoFit
.Activate
End With
Application.ScreenUpdating = True
End Sub
Private Sub prcSubFolders(oFolder)
Dim oSubFolder As Object
For Each oSubFolder In oFolder.subfolders
prcFiles oSubFolder
prcSubFolders oSubFolder
Next
End Sub
Private Sub prcFiles(oFolder)
Dim oFile As Object
For Each oFile In oFolder.Files
ReDim Preserve vntFiles(1 To 4, 1 To lngFiles)
vntFiles(1, lngFiles) = "=hyperlink(" & Chr(34) & oFile.Path & Chr(34) & ")"
vntFiles(2, lngFiles) = oFile.Size / 1024
vntFiles(3, lngFiles) = Format(oFile.datelastmodified, "DD.MM.YYYY hh:mm:ss")
vntFiles(4, lngFiles) = Format(oFile.datecreated, "DD.MM.YYYY hh:mm:ss")
' vntFiles(5, lngFiles) = oFile.datelastaccessed
lngFiles = lngFiles + 1
Next
End Sub
Private Function fncFolderName() As String
'ab Version 2000:
Dim strFolder As String
With Application.FileDialog(4)
.InitialFileName = "C:\"
.InitialView = 2
If .Show = -1 Then
fncFolderName = .SelectedItems(1)
'MsgBox strFolder 'Pfad des ausgewählten Ordners
End If
End With
End Function
Gruß ausm Pott
Udo