This Code Worked PERFECT in V2003 - I'm having a time getting the equivalent
to work for me in V2007. Can someone assist me?
TIA,


Sub ListFiles()
Dim s As String
Dim sfolder As String
sfolder = Range("D1").Value
s = Range("B3").Value
Range("B4:B65536").ClearContents
s2 = "*" & s & "*.*"
With Application.FileSearch
.NewSearch
.LookIn = sfolder
.SearchSubFolders = True
.Filename = s2
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 3, 2).Value = _
Mid(.FoundFiles(i), 64, 256)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

The flakey .filesearch was removed in xl2007.

Ron de Bruin shows a couple of alternatives:
http://www.rondebruin.nl/copy3.htm
and
http://www.rondebruin.nl/fso.htm

JMay-Rke wrote:

This Code Worked PERFECT in V2003 - I'm having a time getting the equivalent
to work for me in V2007. Can someone assist me?
TIA,

Sub ListFiles()
Dim s As String
Dim sfolder As String
sfolder = Range("D1").Value
s = Range("B3").Value
Range("B4:B65536").ClearContents
s2 = "*" & s & "*.*"
With Application.FileSearch
.NewSearch
.LookIn = sfolder
.SearchSubFolders = True
.Filename = s2
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 3, 2).Value = _
Mid(.FoundFiles(i), 64, 256)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

--

Dave Peterson

WOW, this is a bit "over-the-top" confusing/new to me. I've been googling
the last 24 hrs - havibg seen and previoulsy studied Ron's Code. Still lost;
Being New to 2007 adds to my dilemma.

"Dave Peterson" wrote:

The flakey .filesearch was removed in xl2007.

Ron de Bruin shows a couple of alternatives:
http://www.rondebruin.nl/copy3.htm
and
http://www.rondebruin.nl/fso.htm

JMay-Rke wrote:

This Code Worked PERFECT in V2003 - I'm having a time getting the equivalent
to work for me in V2007. Can someone assist me?
TIA,

Sub ListFiles()
Dim s As String
Dim sfolder As String
sfolder = Range("D1").Value
s = Range("B3").Value
Range("B4:B65536").ClearContents
s2 = "*" & s & "*.*"
With Application.FileSearch
.NewSearch
.LookIn = sfolder
.SearchSubFolders = True
.Filename = s2
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 3, 2).Value = _
Mid(.FoundFiles(i), 64, 256)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

--

Dave Peterson

Try downloading one of the workbooks that Ron provides. Maybe you can step
through the code to understand it.

JMay-Rke wrote:

WOW, this is a bit "over-the-top" confusing/new to me. I've been googling
the last 24 hrs - havibg seen and previoulsy studied Ron's Code. Still lost;
Being New to 2007 adds to my dilemma.

"Dave Peterson" wrote:

The flakey .filesearch was removed in xl2007.

Ron de Bruin shows a couple of alternatives:
http://www.rondebruin.nl/copy3.htm
and
http://www.rondebruin.nl/fso.htm

JMay-Rke wrote:

This Code Worked PERFECT in V2003 - I'm having a time getting the equivalent
to work for me in V2007. Can someone assist me?
TIA,

Sub ListFiles()
Dim s As String
Dim sfolder As String
sfolder = Range("D1").Value
s = Range("B3").Value
Range("B4:B65536").ClearContents
s2 = "*" & s & "*.*"
With Application.FileSearch
.NewSearch
.LookIn = sfolder
.SearchSubFolders = True
.Filename = s2
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 3, 2).Value = _
Mid(.FoundFiles(i), 64, 256)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

--

Dave Peterson


--

Dave Peterson

Dave;
I'm afraid I did - I ran it - got a disaster from it. Gonna have to put
this on the shelf until I have several days to "understand". Thanks,
Jim

"Dave Peterson" wrote:

Try downloading one of the workbooks that Ron provides. Maybe you can step
through the code to understand it.

JMay-Rke wrote:

WOW, this is a bit "over-the-top" confusing/new to me. I've been googling
the last 24 hrs - havibg seen and previoulsy studied Ron's Code. Still lost;
Being New to 2007 adds to my dilemma.

"Dave Peterson" wrote:

The flakey .filesearch was removed in xl2007.

Ron de Bruin shows a couple of alternatives:
http://www.rondebruin.nl/copy3.htm
and
http://www.rondebruin.nl/fso.htm

JMay-Rke wrote:

This Code Worked PERFECT in V2003 - I'm having a time getting the equivalent
to work for me in V2007. Can someone assist me?
TIA,

Sub ListFiles()
Dim s As String
Dim sfolder As String
sfolder = Range("D1").Value
s = Range("B3").Value
Range("B4:B65536").ClearContents
s2 = "*" & s & "*.*"
With Application.FileSearch
.NewSearch
.LookIn = sfolder
.SearchSubFolders = True
.Filename = s2
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 3, 2).Value = _
Mid(.FoundFiles(i), 64, 256)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

--

Dave Peterson


--

Dave Peterson

Didn't take quiet a few days, but my new code I have as follows: It is working
intermittanly, that is the results are both **right* and **wrong**. Do you
see anything drastically wrong?
Thanks,
Jim


Sub ListFiles()
Dim sFol As String
Dim fso As Object, fl As Object
Dim fld As Object
Dim wb As Workbook
Dim Fcount As Long
sFol = ActiveSheet.Range("D1").Value
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(sFol)
s = Range("B3").Value
Range(Cells(4, 2), Cells(Rows.Count, 2)).ClearContents
s2 = "*" & s & "*.*"
Fcount = 0
I = 0
For Each fl In fld.Files
If fl.Name Like s2 Then
Fcount = Fcount + 1
End If
' End If
Next
MsgBox "There were " & Fcount & " file(s) found."
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
For Each fl In fld.Files
If fl.Name Like s2 Then
I = I + 1
Set wb = Workbooks.Open(fl.Path)
ThisWorkbook.ActiveSheet.Cells(I + 3, 2).Value = Mid(fl, 38, 256)
wb.Close False
End If
' End If
Next
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
' MsgBox "How does it look?"
End Sub

"JMay-Rke" wrote:

Dave;
I'm afraid I did - I ran it - got a disaster from it. Gonna have to put
this on the shelf until I have several days to "understand". Thanks,
Jim

"Dave Peterson" wrote:

Try downloading one of the workbooks that Ron provides. Maybe you can step
through the code to understand it.

JMay-Rke wrote:

WOW, this is a bit "over-the-top" confusing/new to me. I've been googling
the last 24 hrs - havibg seen and previoulsy studied Ron's Code. Still lost;
Being New to 2007 adds to my dilemma.

"Dave Peterson" wrote:

The flakey .filesearch was removed in xl2007.

Ron de Bruin shows a couple of alternatives:
http://www.rondebruin.nl/copy3.htm
and
http://www.rondebruin.nl/fso.htm

JMay-Rke wrote:

This Code Worked PERFECT in V2003 - I'm having a time getting the equivalent
to work for me in V2007. Can someone assist me?
TIA,

Sub ListFiles()
Dim s As String
Dim sfolder As String
sfolder = Range("D1").Value
s = Range("B3").Value
Range("B4:B65536").ClearContents
s2 = "*" & s & "*.*"
With Application.FileSearch
.NewSearch
.LookIn = sfolder
.SearchSubFolders = True
.Filename = s2
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(i + 3, 2).Value = _
Mid(.FoundFiles(i), 64, 256)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

--

Dave Peterson


--

Dave Peterson