Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1548to1552
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

clsFileSearch unter 64bit?

clsFileSearch unter 64bit?
02.04.2017 18:15:19
cH_rI_sI
Guten Abend liebe VBA-Experten,
ich nutze das Klassenmodul clsFileSearch von Nepumuk unter Excel 2007 - nun bin ich jedoch auf Excel 2013 64bit umgestiegen und das Klassenmodul funktioniert nicht mehr... Was genau muss man machen, damit dies auch unter 64bit läuft?
Ich habe aufgrund mangelnder VBA-Kenntnisse echt keinen Plan...
Anbei ein Beispielfile:
https://www.herber.de/bbs/user/112569.xlsm
Wäre nett, wenn sich das jemand ansehen könnte - natürlich wäre es gut, wenn das Klassenmodul auch unter 32 bit lauffähig bleibt.
Oder welche filesearch-Alternativen gibt es sonst noch? Bin für jeden Vorschlag dankbar!
Danke und schönen Abend noch!
Lg,
Chrisi

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: clsFileSearch unter 64bit?
02.04.2017 19:51:38
Nepumuk
Hallo,
teste mal (ich kann nicht da kein Office 64Bit)
' **********************************************************************
' Modul: clsFileSearch Typ: Klassenmodul
' **********************************************************************



'// Module : clsFileSearch, Klassenmodul
'// Author : NEPUMUK at http://www.office-loesung.de/ftopic148247_0_0_asc.php
'// Created : 28. Mai 2007
'// Modified :
'// Purpose : FileSearch
Option Explicit

Private Declare PtrSafe Function FindFirstFile Lib "kernel32.dll" Alias "FindFirstFileA" ( _
    ByVal lpFileName As String, _
    ByRef lpFindFileData As WIN32_FIND_DATA) As LongPtr
Private Declare PtrSafe Function FindNextFile Lib "kernel32.dll" Alias "FindNextFileA" ( _
    ByVal hFindFile As LongPtr, _
    ByRef lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare PtrSafe Function FindClose Lib "kernel32.dll" ( _
    ByVal hFindFile As LongPtr) As Long
Private Declare PtrSafe Function FileTimeToLocalFileTime Lib "kernel32.dll" ( _
    ByRef lpFileTime As FILETIME, _
    ByRef lpLocalFileTime As FILETIME) As Long
Private Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32.dll" ( _
    ByRef lpFileTime As FILETIME, _
    ByRef lpSystemTime As SYSTEMTIME) As Long

Private Enum FILE_ATTRIBUTE
    FILE_ATTRIBUTE_READONLY = &H1
    FILE_ATTRIBUTE_HIDDEN = &H2
    FILE_ATTRIBUTE_SYSTEM = &H4
    FILE_ATTRIBUTE_DIRECTORY = &H10
    FILE_ATTRIBUTE_ARCHIVE = &H20
    FILE_ATTRIBUTE_NORMAL = &H80
    FILE_ATTRIBUTE_TEMPORARY = &H100
End Enum

Private Const MAX_PATH = 260&
Private Const INVALID_HANDLE_VALUE = -1&

Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternate As String * 14
End Type

Private mlngFileCount As Long
Private mudtFiles() As FILEINFO
Private mstrFolderPath As String
Private mstrExtension As String
Private mstrSearchLike As String
Private mblnSubFolders As Boolean
Private mblnCaseSenstiv As Boolean

Friend Property Get Files(lngIndex As Long) As FILEINFO
    Files = mudtFiles(lngIndex)
End Property

Friend Property Get FileCount() As Long
    FileCount = mlngFileCount
End Property

Friend Property Let FolderPath(strFolderPath As String)
    mstrFolderPath = strFolderPath
End Property

Friend Property Let Extension(strExtension As String)
    mstrExtension = strExtension
End Property

Friend Property Let SearchLike(strSearchLike As String)
    mstrSearchLike = strSearchLike
End Property

Friend Property Let SubFolders(blnSubFolders As Boolean)
    mblnSubFolders = blnSubFolders
End Property

Friend Property Let CaseSenstiv(blnCaseSenstiv As Boolean)
    mblnCaseSenstiv = blnCaseSenstiv
End Property

Friend Function Execute(Optional enmSortBy As SORT_BY = Sort_by_None, _
        Optional enmSortOrder As SORT_ORDER = Sort_Order_Ascending) As Long

    Call FindFiles(mstrFolderPath)
    If mlngFileCount > 1 And enmSortBy <> Sort_by_None Then _
        Call prcSort(1, mlngFileCount, enmSortBy, enmSortOrder)
    Execute = mlngFileCount
End Function

Private Sub FindFiles(ByVal strFolderPath As String)
    Dim WFD As WIN32_FIND_DATA, lngptrSearch As LongPtr, strDirName As String
    On Error GoTo ErrorHandling
    If Right$(strFolderPath, 1) <> "\" Then strFolderPath = strFolderPath & "\"
    lngptrSearch = FindFirstFile(strFolderPath & "*.*", WFD)
    If lngptrSearch <> INVALID_HANDLE_VALUE Then
        Call GetFilesInFolder(strFolderPath)
        If mblnSubFolders Then
            Do
                If (WFD.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) Then
                    strDirName = Left$(WFD.cFileName, InStr(WFD.cFileName, Chr$(0)) - 1)
                    If (strDirName <> ".") And (strDirName <> "..") Then _
                        Call FindFiles(strFolderPath & strDirName)
                End If
            Loop While FindNextFile(lngptrSearch, WFD)
        End If
        FindClose lngptrSearch
    End If
    Exit Sub
    ErrorHandling:
    MsgBox "Fehler " & CStr(Err.Number) & vbLf & vbLf & _
        Err.Description, vbCritical, "Fehler"
End Sub

Private Sub GetFilesInFolder(ByVal strFolderPath As String)
    Dim WFD As WIN32_FIND_DATA, lngptrSearch As LongPtr, strFilename As String
    Dim udtFiletime As FILETIME, udtSystemtime As SYSTEMTIME
    On Error GoTo ErrorHandling
    If Right$(strFolderPath, 1) <> "\" Then strFolderPath = strFolderPath & "\"
    lngptrSearch = FindFirstFile(strFolderPath & mstrExtension, WFD)
    If lngptrSearch <> INVALID_HANDLE_VALUE Then
        Do
            If (WFD.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) <> FILE_ATTRIBUTE_DIRECTORY Then
                strFilename = Left$(WFD.cFileName, InStr(WFD.cFileName, Chr$(0)) - 1)
                If IIf(mblnCaseSenstiv, strFilename, LCase$(strFilename)) Like _
                    IIf(mblnCaseSenstiv, mstrSearchLike, LCase$(mstrSearchLike)) Then
                    mlngFileCount = mlngFileCount + 1
                    Redim Preserve mudtFiles(1 To mlngFileCount)
                    With mudtFiles(mlngFileCount)
                        .strPath = strFolderPath & strFilename
                        .strFilename = strFilename
                        .lngSize = WFD.nFileSizeLow
                        FileTimeToLocalFileTime WFD.ftCreationTime, udtFiletime
                        FileTimeToSystemTime udtFiletime, udtSystemtime
                        .dmtDateCreate = CDate(DateSerial(udtSystemtime.wYear, udtSystemtime.wMonth, udtSystemtime.wDay) + _
                            TimeSerial(udtSystemtime.wHour, udtSystemtime.wMinute, udtSystemtime.wSecond))
                        FileTimeToLocalFileTime WFD.ftLastAccessTime, udtFiletime
                        FileTimeToSystemTime udtFiletime, udtSystemtime
                        .dmtLastAccess = CDate(DateSerial(udtSystemtime.wYear, udtSystemtime.wMonth, udtSystemtime.wDay) + _
                            TimeSerial(udtSystemtime.wHour, udtSystemtime.wMinute, udtSystemtime.wSecond))
                        FileTimeToLocalFileTime WFD.ftLastWriteTime, udtFiletime
                        FileTimeToSystemTime udtFiletime, udtSystemtime
                        .dmtLastModify = CDate(DateSerial(udtSystemtime.wYear, udtSystemtime.wMonth, udtSystemtime.wDay) + _
                            TimeSerial(udtSystemtime.wHour, udtSystemtime.wMinute, udtSystemtime.wSecond))
                    End With
                End If
            End If
        Loop While FindNextFile(lngptrSearch, WFD)
        FindClose lngptrSearch
    End If
    Exit Sub
    ErrorHandling:
    MsgBox "Fehler " & CStr(Err.Number) & vbLf & vbLf & _
        Err.Description, vbCritical, "Fehler"
End Sub

Private Sub prcSort(lngLBorder As Long, lngUBorder As Long, enmSortBy As SORT_BY, enmSortOrder As SORT_ORDER)
    Dim lngIndex1 As Long, lngIndex2 As Long
    Dim udtBuffer As FILEINFO, vntTemp As Variant
    lngIndex1 = lngLBorder
    lngIndex2 = lngUBorder
    Select Case enmSortBy
        Case Sort_by_Name: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).strFilename
        Case Sort_by_Path: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).strPath
        Case Sort_by_Size: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).lngSize
        Case Sort_by_Last_Access: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).dmtLastAccess
        Case Sort_by_Last_Modyfy: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).dmtLastModify
        Case Sort_by_Date_Create: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).dmtDateCreate
    End Select
    Do
        Select Case enmSortBy
            Case Sort_by_Name
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).strFilename < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).strFilename
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).strFilename > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).strFilename
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Path
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).strPath < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).strPath
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).strPath > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).strPath
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Size
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).lngSize < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).lngSize
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).lngSize > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).lngSize
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Last_Access
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).dmtLastAccess < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).dmtLastAccess
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).dmtLastAccess > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).dmtLastAccess
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Last_Modyfy
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).dmtLastModify < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).dmtLastModify
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).dmtLastModify > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).dmtLastModify
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Date_Create
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).dmtDateCreate < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).dmtDateCreate
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).dmtDateCreate > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).dmtDateCreate
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
        End Select
        If lngIndex1 <= lngIndex2 Then
            udtBuffer = mudtFiles(lngIndex1)
            mudtFiles(lngIndex1) = mudtFiles(lngIndex2)
            mudtFiles(lngIndex2) = udtBuffer
            lngIndex1 = lngIndex1 + 1
            lngIndex2 = lngIndex2 - 1
        End If
    Loop Until lngIndex1 > lngIndex2
    If lngLBorder < lngIndex2 Then Call prcSort(lngLBorder, lngIndex2, enmSortBy, enmSortOrder)
    If lngIndex1 < lngUBorder Then Call prcSort(lngIndex1, lngUBorder, enmSortBy, enmSortOrder)
End Sub

Gruß
Nepumuk
Anzeige
AW: clsFileSearch unter 64bit?
03.04.2017 08:14:48
cH_rI_sI
Guten Morgen Nepumuk,
vielen lieben Dank für die rasche Antwort sowie die Bereitstellung eines korrigierten Codes! Ich kann die Korrektur jedoch erst heute Abend testen...
@ all:
Ein Frage habe ich noch:
Ich möchte bei der Wortsuche die Zelle des gefundenen Worts zurück bekommen:
Public Sub Wortsuche()
Dim objFileSearch As clsFileSearch
Dim lngIndex As Long
Dim strSuwort As String
Dim i As Integer
Dim bolErg As Boolean
Set objFileSearch = New clsFileSearch
strSuwort = InputBox("Suchwort eingeben")
With objFileSearch
.CaseSenstiv = False
.Extension = "*.xls*"
.FolderPath = "D:\temp\"
.SearchLike = "*"
.SubFolders = True
If .Execute(Sort_by_Size, Sort_Order_Descending) > 0 Then
Application.ScreenUpdating = False
For lngIndex = 1 To .FileCount
With .Files(lngIndex)
Workbooks.Open (.strPath)                         'Workbook öffnen
On Error Resume Next
For i = 1 To Workbooks(.strFilename).Worksheets.Count
With Workbooks(.strFilename).Worksheets(i)
.Activate
bolErg = .Cells.Find(What:=strSuwort, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,  _
MatchCase:=False).Activate
If bolErg Then
MsgBox strSuwort & " gefunden in " & ActiveWorkbook.Name & Chr( _
10) & _
"Tabelle = " & ActiveWorkbook.Worksheets(i).Name
bolErg = False
End If
End With
Next i
Workbooks(.strFilename).Close savechanges:=False   'Workbook schließen
End With
Next
Else
MsgBox "Nix gefunden"
End If
Application.ScreenUpdating = True
End With
Set objFileSearch = Nothing
End Sub
Geht das? Bin für jede Unterstützung dankbar!
Anbei noch das Beispielfile:
https://www.herber.de/bbs/user/112569.xlsm
Lg,
Chrisi
Anzeige
AW: clsFileSearch unter 64bit?
03.04.2017 13:41:05
Nepumuk
Hallo,
teste mal:
Public Sub Wortsuche()
    Dim objFileSearch As clsFileSearch
    Dim lngIndex As Long
    Dim strSearch As String, strFirstAddress As String
    Dim objWorkbook As Workbook, objWorksheet As Worksheet
    Dim objCell As Range
    
    Set objFileSearch = New clsFileSearch
    
    strSearch = InputBox("Suchwort eingeben", "Eingabe")
    
    With objFileSearch
        .CaseSenstiv = False
        .Extension = "*.xls*"
        .FolderPath = "D:\temp\"
        .SearchLike = "*"
        .SubFolders = True
        If .Execute(Sort_by_Size, Sort_Order_Descending) > 0 Then
            Application.ScreenUpdating = False
            For lngIndex = 1 To .FileCount
                Set objWorkbook = Workbooks.Open(.Files(lngIndex).strPath) 'Workbook öffnen
                For Each objWorksheet In objWorkbook.Worksheets
                    With objWorksheet.Cells
                        Set objCell = .Find(What:=strSearch, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
                        If Not objCell Is Nothing Then
                            strFirstAddress = objCell.Address
                            Do
                                MsgBox strSearch & " gefunden in " & objWorkbook.Name & vbLf & _
                                    "Tabelle = " & objWorksheet.Name & vbLf & _
                                    "Zelle = " & objCell.Address(False, False)
                                Set objCell = .FindNext(objCell)
                            Loop Until objCell.Address = strFirstAddress
                        End If
                    End With
                Next
                Call objWorkbook.Close(SaveChanges:=False) 'Workbook schließen
            Next
            Application.ScreenUpdating = True
        Else
            MsgBox "Nix gefunden"
        End If
    End With
    Set objCell = Nothing
    Set objFileSearch = Nothing
End Sub

Gruß
Nepumuk
Anzeige
AW: clsFileSearch unter 64bit?
03.04.2017 15:22:59
cH_rI_sI
Hallo Nepumuk und auch alles anderen Experten,
ich habe das Ganze jetzt so gelöst - siehe Datei mit Suche:
https://www.herber.de/bbs/user/112588.xlsm
Und hier ist die Quelle, in der nach "XY" gesucht wird:
https://www.herber.de/bbs/user/112589.xlsx
Das Problem hier ist noch, dass ich auch leere Zeilen zurückbekomme;
es sollen nur ganze Zahlen in der Spalte berücksichtigt werden in der sich der Suchbegriff "XY" wiederfindet (Zellen mit Fehler und Formeln sollen ignoriert werden)...
Wäre nett, wenn sich das noch jemand ansehen könnte - ist vermutlich nur eine Kleinigkeit, aber ich finde den Fehler nicht...
Besten Dank im Voraus!
Lg,
Chrisi
Anzeige
AW: clsFileSearch unter 64bit?
03.04.2017 19:34:57
cH_rI_sI
Hallo Nepumuk,
der 64bit Code des Klassenmoduls läuft - besten Dank für deine Mühe...
Und auch alle anderen Probleme sind gelöst ;-)
Danke!!!
Lg und schönen Abend noch!
AW: clsFileSearch unter 64bit?
04.04.2017 08:18:10
cH_rI_sI
Guten Morgen Nepumuk,
leider funktioniert der neue Code nicht unter 32 bit:
Userbild
Gibt es eine Möglichkeit, dass das Klassenmodul in beiden Versionen (32- u. 64 bit) funktioniert?
Ich denke, auf das warten viele ;-)
Vielleicht kannst Du Dir das nochmal ansehen - wäre echt nett...
Schönen Tag!
Lg,
Chrisi
Anzeige
AW: clsFileSearch unter 64bit?
04.04.2017 10:51:53
Nepumuk
Hallo,
so:
' **********************************************************************
' Modul: Klasse1 Typ: Klassenmodul
' **********************************************************************

Option Explicit

#If Win64 Then
Private Declare PtrSafe Function FindFirstFile Lib "kernel32.dll" Alias "FindFirstFileA" ( _
    ByVal lpFileName As String, _
    ByRef lpFindFileData As WIN32_FIND_DATA) As LongPtr
Private Declare PtrSafe Function FindNextFile Lib "kernel32.dll" Alias "FindNextFileA" ( _
    ByVal hFindFile As LongPtr, _
    ByRef lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare PtrSafe Function FindClose Lib "kernel32.dll" ( _
    ByVal hFindFile As LongPtr) As Long
Private Declare PtrSafe Function FileTimeToLocalFileTime Lib "kernel32.dll" ( _
    ByRef lpFileTime As FILETIME, _
    ByRef lpLocalFileTime As FILETIME) As Long
Private Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32.dll" ( _
    ByRef lpFileTime As FILETIME, _
    ByRef lpSystemTime As SYSTEMTIME) As Long
#Else
Private Declare Function FileTimeToLocalFileTime Lib "kernel32.dll" ( _
    ByRef lpFileTime As FILETIME, _
    ByRef lpLocalFileTime As FILETIME) As Long
Private Declare Function FileTimeToSystemTime Lib "kernel32.dll" ( _
    ByRef lpFileTime As FILETIME, _
    ByRef lpSystemTime As SYSTEMTIME) As Long
Private Declare Function FindClose Lib "kernel32.dll" ( _
    ByVal hFindFile As Long) As Long
Private Declare Function FindFirstFile Lib "kernel32.dll" Alias "FindFirstFileA" ( _
    ByVal lpFileName As String, _
    ByRef lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare Function FindNextFile Lib "kernel32.dll" Alias "FindNextFileA" ( _
    ByVal hFindFile As Long, _
    ByRef lpFindFileData As WIN32_FIND_DATA) As Long
#End If

Private Enum FILE_ATTRIBUTE
    FILE_ATTRIBUTE_READONLY = &H1
    FILE_ATTRIBUTE_HIDDEN = &H2
    FILE_ATTRIBUTE_SYSTEM = &H4
    FILE_ATTRIBUTE_DIRECTORY = &H10
    FILE_ATTRIBUTE_ARCHIVE = &H20
    FILE_ATTRIBUTE_NORMAL = &H80
    FILE_ATTRIBUTE_TEMPORARY = &H100
End Enum

Private Const MAX_PATH As Long = 260&
Private Const INVALID_HANDLE_VALUE As Long = -1&

Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternate As String * 14
End Type

Private mlngFileCount As Long
Private mudtFiles() As FILEINFO
Private mstrFolderPath As String
Private mstrExtension As String
Private mstrSearchLike As String
Private mblnSubFolders As Boolean
Private mblnCaseSenstiv As Boolean

Friend Property Get Files(lngIndex As Long) As FILEINFO
    Files = mudtFiles(lngIndex)
End Property

Friend Property Get FileCount() As Long
    FileCount = mlngFileCount
End Property

Friend Property Let FolderPath(strFolderPath As String)
    mstrFolderPath = strFolderPath
End Property

Friend Property Let Extension(strExtension As String)
    mstrExtension = strExtension
End Property

Friend Property Let SearchLike(strSearchLike As String)
    mstrSearchLike = strSearchLike
End Property

Friend Property Let SubFolders(blnSubFolders As Boolean)
    mblnSubFolders = blnSubFolders
End Property

Friend Property Let CaseSenstiv(blnCaseSenstiv As Boolean)
    mblnCaseSenstiv = blnCaseSenstiv
End Property

Friend Function Execute(Optional enmSortBy As SORT_BY = Sort_by_None, _
        Optional enmSortOrder As SORT_ORDER = Sort_Order_Ascending) As Long

    Call FindFiles(mstrFolderPath)
    If mlngFileCount > 1 And enmSortBy <> Sort_by_None Then _
        Call prcSort(1, mlngFileCount, enmSortBy, enmSortOrder)
    Execute = mlngFileCount
End Function

Private Sub FindFiles(ByVal strFolderPath As String)
#If Win64 Then
    Dim lngSearch As LongPtr
    #Else
    Dim lngSearch As Long
    #End If
    Dim WFD As WIN32_FIND_DATA, strDirName As String
    On Error GoTo ErrorHandling
    If Right$(strFolderPath, 1) <> "\" Then strFolderPath = strFolderPath & "\"
    lngSearch = FindFirstFile(strFolderPath & "*.*", WFD)
    If lngSearch <> INVALID_HANDLE_VALUE Then
        Call GetFilesInFolder(strFolderPath)
        If mblnSubFolders Then
            Do
                If (WFD.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) Then
                    strDirName = Left$(WFD.cFileName, InStr(WFD.cFileName, Chr$(0)) - 1)
                    If (strDirName <> ".") And (strDirName <> "..") Then _
                        Call FindFiles(strFolderPath & strDirName)
                End If
            Loop While FindNextFile(lngSearch, WFD)
        End If
        Call FindClose(lngSearch)
    End If
    Exit Sub
    ErrorHandling:
    MsgBox "Fehler " & CStr(Err.Number) & vbLf & vbLf & _
        Err.Description, vbCritical, "Fehler"
End Sub

Private Sub GetFilesInFolder(ByVal strFolderPath As String)
#If Win64 Then
    Dim lngSearch As LongPtr
    #Else
    Dim lngSearch As Long
    #End If
    Dim WFD As WIN32_FIND_DATA, strFilename As String
    Dim udtFiletime As FILETIME, udtSystemtime As SYSTEMTIME
    On Error GoTo ErrorHandling
    If Right$(strFolderPath, 1) <> "\" Then strFolderPath = strFolderPath & "\"
    lngSearch = FindFirstFile(strFolderPath & mstrExtension, WFD)
    If lngSearch <> INVALID_HANDLE_VALUE Then
        Do
            If (WFD.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) <> FILE_ATTRIBUTE_DIRECTORY Then
                strFilename = Left$(WFD.cFileName, InStr(WFD.cFileName, Chr$(0)) - 1)
                If IIf(mblnCaseSenstiv, strFilename, LCase$(strFilename)) Like _
                    IIf(mblnCaseSenstiv, mstrSearchLike, LCase$(mstrSearchLike)) Then
                    mlngFileCount = mlngFileCount + 1
                    Redim Preserve mudtFiles(1 To mlngFileCount)
                    With mudtFiles(mlngFileCount)
                        .strPath = strFolderPath & strFilename
                        .strFilename = strFilename
                        .lngSize = WFD.nFileSizeLow
                        FileTimeToLocalFileTime WFD.ftCreationTime, udtFiletime
                        FileTimeToSystemTime udtFiletime, udtSystemtime
                        .dmtDateCreate = CDate(DateSerial(udtSystemtime.wYear, udtSystemtime.wMonth, udtSystemtime.wDay) + _
                            TimeSerial(udtSystemtime.wHour, udtSystemtime.wMinute, udtSystemtime.wSecond))
                        FileTimeToLocalFileTime WFD.ftLastAccessTime, udtFiletime
                        FileTimeToSystemTime udtFiletime, udtSystemtime
                        .dmtLastAccess = CDate(DateSerial(udtSystemtime.wYear, udtSystemtime.wMonth, udtSystemtime.wDay) + _
                            TimeSerial(udtSystemtime.wHour, udtSystemtime.wMinute, udtSystemtime.wSecond))
                        FileTimeToLocalFileTime WFD.ftLastWriteTime, udtFiletime
                        FileTimeToSystemTime udtFiletime, udtSystemtime
                        .dmtLastModify = CDate(DateSerial(udtSystemtime.wYear, udtSystemtime.wMonth, udtSystemtime.wDay) + _
                            TimeSerial(udtSystemtime.wHour, udtSystemtime.wMinute, udtSystemtime.wSecond))
                    End With
                End If
            End If
        Loop While FindNextFile(lngSearch, WFD)
        Call FindClose(lngSearch)
    End If
    Exit Sub
    ErrorHandling:
    MsgBox "Fehler " & CStr(Err.Number) & vbLf & vbLf & _
        Err.Description, vbCritical, "Fehler"
End Sub

Private Sub prcSort(lngLBorder As Long, lngUBorder As Long, enmSortBy As SORT_BY, enmSortOrder As SORT_ORDER)
    Dim lngIndex1 As Long, lngIndex2 As Long
    Dim udtBuffer As FILEINFO, vntTemp As Variant
    lngIndex1 = lngLBorder
    lngIndex2 = lngUBorder
    Select Case enmSortBy
        Case Sort_by_Name: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).strFilename
        Case Sort_by_Path: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).strPath
        Case Sort_by_Size: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).lngSize
        Case Sort_by_Last_Access: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).dmtLastAccess
        Case Sort_by_Last_Modyfy: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).dmtLastModify
        Case Sort_by_Date_Create: vntTemp = mudtFiles((lngLBorder + lngUBorder) \ 2).dmtDateCreate
    End Select
    Do
        Select Case enmSortBy
            Case Sort_by_Name
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).strFilename < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).strFilename
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).strFilename > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).strFilename
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Path
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).strPath < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).strPath
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).strPath > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).strPath
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Size
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).lngSize < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).lngSize
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).lngSize > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).lngSize
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Last_Access
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).dmtLastAccess < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).dmtLastAccess
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).dmtLastAccess > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).dmtLastAccess
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Last_Modyfy
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).dmtLastModify < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).dmtLastModify
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).dmtLastModify > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).dmtLastModify
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
            Case Sort_by_Date_Create
                If enmSortOrder = Sort_Order_Ascending Then
                    Do While mudtFiles(lngIndex1).dmtDateCreate < vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp < mudtFiles(lngIndex2).dmtDateCreate
                        lngIndex2 = lngIndex2 - 1
                    Loop
                Else
                    Do While mudtFiles(lngIndex1).dmtDateCreate > vntTemp
                        lngIndex1 = lngIndex1 + 1
                    Loop
                    Do While vntTemp > mudtFiles(lngIndex2).dmtDateCreate
                        lngIndex2 = lngIndex2 - 1
                    Loop
                End If
        End Select
        If lngIndex1 <= lngIndex2 Then
            udtBuffer = mudtFiles(lngIndex1)
            mudtFiles(lngIndex1) = mudtFiles(lngIndex2)
            mudtFiles(lngIndex2) = udtBuffer
            lngIndex1 = lngIndex1 + 1
            lngIndex2 = lngIndex2 - 1
        End If
    Loop Until lngIndex1 > lngIndex2
    If lngLBorder < lngIndex2 Then Call prcSort(lngLBorder, lngIndex2, enmSortBy, enmSortOrder)
    If lngIndex1 < lngUBorder Then Call prcSort(lngIndex1, lngUBorder, enmSortBy, enmSortOrder)
End Sub

Gruß
Nepumuk
Anzeige
AW: clsFileSearch unter 64bit?
04.04.2017 11:06:03
cH_rI_sI
Hallo Nepumuk,
Du bist echt der Beste (haben Dir wahrscheinlich schon viele gesagt ;-) - unter Excel 2007 (32 bit) funktioniert der Code schonmal - unter Excel 2013 (64 bit) kann ich erst am Abend testen.
Daher vielen lieben Dank für deine Unterstützung!!!
Falls noch ein Thema auftaucht, melde ich mich - ansonsten schonmal CLOSED.

12 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige