"PLChan" <Chan.Pui***@gmail.com> wrote in message
news:1142780427.146525.286300@j33g2000cwa.googlegroups.com...
> Can I use Windows API in vba to control windows search for a file in
> certain directory
> e.g. C:\ABC
>
> Many Thanks
>

"PLChan" <Chan.Pui***@gmail.com> wrote in message
news:1142873693.767202.296090@u72g2000cwu.googlegroups.com...
> Thank then
>

"RB Smissaert" <bartsmissa***@blueyonder.co.uk> wrote in message
news:e3u2pnFTGHA.2088@TK2MSFTNGP14.phx.gbl...
> Function RecursiveFindFiles(strPath As String, _
>                            strSearch As String, _
>                            Optional bSubFolders As Boolean = True, _
>                            Optional bSheet As Boolean = False, _
>                            Optional lFileCount As Long = 0, _
>                            Optional lDirCount As Long = 0) As Variant
>
>    'adapted from the MS example:
>    'http://support.microsoft.com/default.aspx?scid=kb;en-us;185476
>    '---------------------------------------------------------------
>    'will list all the files in the supplied folder and it's
>    'subfolders that fit the strSearch criteria
>    'lFileCount and lDirCount will always have to start as 0
>    '---------------------------------------------------------------
>
>    Dim strFileName As String        'Walking strFileName variable.
>    Dim strDirName As String        'SubDirectory Name.
>    Dim arrDirNames() As String        'Buffer for directory name entries.
>    Dim nDir As Long        'Number of directories in this strPath.
>    Dim i As Long        'For-loop counter.
>    Dim n As Long
>    Dim arrFiles
>    Static strStartDirName As String
>    Static strpathOld As String
>
>    On Error GoTo sysFileERR
>
>    If lFileCount = 0 Then
>        Static collFiles As Collection
>        Set collFiles = New Collection
>        Application.Cursor = xlWait
>    End If
>
>    If Right$(strPath, 1) <> "\" Then
>        strPath = strPath & "\"
>    End If
>
>    If lFileCount = 0 And lDirCount = 0 Then
>        strStartDirName = strPath
>    End If
>
>    'search for subdirectories
>    '-------------------------
>    nDir = 0
>
>    ReDim arrDirNames(nDir)
>
>    strDirName = Dir(strPath, _
>                     vbDirectory Or _
>                     vbHidden Or _
>                     vbArchive Or _
>                     vbReadOnly Or _
>                     vbSystem)        'Even if hidden, and so on.
>
>
>    Do While Len(strDirName) > 0
>        'ignore the current and encompassing directories
>        '-----------------------------------------------
>        If (strDirName <> ".") And (strDirName <> "..") Then
>            'check for directory with bitwise comparison
>            '-------------------------------------------
>            If GetAttr(strPath & strDirName) And vbDirectory Then
>                arrDirNames(nDir) = strDirName
>                lDirCount = lDirCount + 1
>                nDir = nDir + 1
>                DoEvents
>                ReDim Preserve arrDirNames(nDir)
>            End If        'directories.
> sysFileERRCont1:
>        End If
>        strDirName = Dir()        'Get next subdirectory
>
>        DoEvents
>    Loop
>
>    'Search through this directory
>    '-----------------------------
>    strFileName = Dir(strPath & strSearch, _
>                      vbNormal Or _
>                      vbHidden Or _
>                      vbSystem Or _
>                      vbReadOnly Or _
>                      vbArchive)
>
>    While Len(strFileName) <> 0
>
>        'dump file in sheet
>        '------------------
>        If bSheet Then
>            If lFileCount < 65536 Then
>                Cells(lFileCount + 1, 1) = strPath & strFileName
>            End If
>        End If
>
>        lFileCount = lFileCount + 1
>
>        collFiles.Add strPath & strFileName
>
>        If strPath <> strpathOld Then
>            Application.StatusBar = "       " & lFileCount & _
>                                    "  " & strSearch & " files found. " & _
>                                    "Now searching " & strPath
>        End If
>
>        strpathOld = strPath
>
>        strFileName = Dir()        'Get next file
>
>        DoEvents
>    Wend
>
>    If bSubFolders Then
>        'If there are sub-directories..
>        '------------------------------
>        If nDir > 0 Then
>            'Recursively walk into them
>            '--------------------------
>            For i = 0 To nDir - 1
>                RecursiveFindFiles strPath & arrDirNames(i) & "\", _
>                                   strSearch, _
>                                   bSubFolders, _
>                                   bSheet, _
>                                   lFileCount, _
>                                   lDirCount
>
>                DoEvents
>            Next
>        End If  'If nDir > 0
>
>        'only bare main folder left, so get out
>        '--------------------------------------
>        If strPath & arrDirNames(i) = strStartDirName Then
>            ReDim arrFiles(1 To lFileCount) As String
>            For n = 1 To lFileCount
>                arrFiles(n) = collFiles(n)
>            Next
>            RecursiveFindFiles = arrFiles
>            Application.Cursor = xlDefault
>            Application.StatusBar = False
>        End If
>
>    Else  'If bSubFolders
>        ReDim arrFiles(1 To lFileCount) As String
>        For n = 1 To lFileCount
>            arrFiles(n) = collFiles(n)
>        Next
>        RecursiveFindFiles = arrFiles
>        Application.Cursor = xlDefault
>        Application.StatusBar = False
>    End If  'If bSubFolders
>
>    Exit Function
> sysFileERR:
>
>    Resume sysFileERRCont1
>
> End Function
>
>
> Sub test()
>
>  Dim arr
>  arr = RecursiveFindFiles("C:\", "*.txt", False, True)
>
> End Sub
>
>
> I am coding in Excel VBA, so if you are not in Excel leave out the bSheet
> and cells bits.
>
>
> RBS
>
>
> "PLChan" <Chan.Pui***@gmail.com> wrote in message
> news:1142873693.767202.296090@u72g2000cwu.googlegroups.com...
>> Thank then
>>
>