Erro With Application.FileSearch ( Resolvido)

pasedo

15-10-2016 19:46:41

Boa tarde pessoal, achei este código na internet e fiz esta planilha , mas esta dando erro nesta linha With Application.FileSearch , ja coloquei on error resume next, assim ai o erro não aparece, mas também não funciona ao executar,nada acontece , poderiam me ajudar. Grato

Reinaldo

15-10-2016 23:40:03

FileSearch funciona somente até excel 2003, superiores tiveram essa função descontinuada.
Se tiver o excel 2003 instalado, rodará sem problemas, caso contrario experimente o disponibilizado no site http://www.mrexcel.com/forum/excel-ques ... earch.html

ou ainda

Código: Selecionar todos

Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
  Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type

Sub ListFiles()
Dim Directory
    Msg = "Select a location containing the files you want to list."
    Directory = GetDirectory(Msg)
    If Directory = "" Then Exit Sub
    If Right(Directory, 1) <> "\" Then Directory = Directory & "\"

    r = 1

'   Insert headers
    Cells.ClearContents
    Cells(r, 1) = "Nome do Arquivo"
    Cells(r, 2) = "Tamanho"
    Cells(r, 3) = "Data/Hora"
    Range("A1:C1").Font.Bold = True
    r = r + 1

'   Get first file
    f = Dir(Directory, 7)
    Cells(r, 1) = f
    Cells(r, 2) = FileLen(Directory & f)
    Cells(r, 3) = FileDateTime(Directory & f)
    'Cells(r, 4) = Type(directory & f)
'   Get remaining files
    Do While f <> ""
        f = Dir
        If f <> "" Then
            r = r + 1
            Cells(r, 1) = f
            Cells(r, 2) = FileLen(Directory & f)
            Cells(r, 3) = FileDateTime(Directory & f)
        End If
    Loop
End Sub

Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer

' Root folder = Desktop
    bInfo.pidlRoot = 0&

' Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
  End If

' Type of directory to return
    bInfo.ulFlags = &H1

' Display the dialog
    x = SHBrowseForFolder(bInfo)

' Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
  End If
End Function
Copie e cole as rotinas acima em um modulo ou importe (no editor VBE) o modulo .bas no zip anexo

pasedo

16-10-2016 00:34:44

Reinaldo escreveu:FileSearch funciona somente até excel 2003, superiores tiveram essa função descontinuada.
Se tiver o excel 2003 instalado, rodará sem problemas, caso contrario experimente o disponibilizado no site http://www.mrexcel.com/forum/excel-ques ... earch.html

ou ainda

Código: Selecionar todos

Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
  Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type

Sub ListFiles()
Dim Directory
    Msg = "Select a location containing the files you want to list."
    Directory = GetDirectory(Msg)
    If Directory = "" Then Exit Sub
    If Right(Directory, 1) <> "\" Then Directory = Directory & "\"

    r = 1

'   Insert headers
    Cells.ClearContents
    Cells(r, 1) = "Nome do Arquivo"
    Cells(r, 2) = "Tamanho"
    Cells(r, 3) = "Data/Hora"
    Range("A1:C1").Font.Bold = True
    r = r + 1

'   Get first file
    f = Dir(Directory, 7)
    Cells(r, 1) = f
    Cells(r, 2) = FileLen(Directory & f)
    Cells(r, 3) = FileDateTime(Directory & f)
    'Cells(r, 4) = Type(directory & f)
'   Get remaining files
    Do While f <> ""
        f = Dir
        If f <> "" Then
            r = r + 1
            Cells(r, 1) = f
            Cells(r, 2) = FileLen(Directory & f)
            Cells(r, 3) = FileDateTime(Directory & f)
        End If
    Loop
End Sub

Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer

' Root folder = Desktop
    bInfo.pidlRoot = 0&

' Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
  End If

' Type of directory to return
    bInfo.ulFlags = &H1

' Display the dialog
    x = SHBrowseForFolder(bInfo)

' Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
  End If
End Function
Copie e cole as rotinas acima em um modulo ou importe (no editor VBE) o modulo .bas no zip anexo
Valeu amigo Reinaldo, queria com o inputbox para selecionar o Path por escrita, mas agradeço que assim deu certo tambem. obrigado