FileSearch Problems
 

Hello,

I wrote a program on Excel 2003 that used Application.FileSearch to look for
any file within a certain folder, checked to see if it was an *.xls file,
opened it if it was, copied data to a host spreadsheet, closed the open file
when finished, then moved the file to another folder. I was able to run this
code fine at work, but on my home computer, the code cannot detect any file
within the folder. As a matter of fact, Excel can't seem to find any file of
any file type within any folder.

My file addresses are correct, so that isn't the problem. Is there some
setting that needs to be turned on to allow file system objects? I'm at a
loss...

Here's the code:

Dim fs
Set fs = Application.FileSearch
With fs
.LookIn = "F:\Forms\New Orders"
.Filename = "*.xls"
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
Dim xlsfile As String
xlsfile = .FoundFiles(i)
....

Also, is there a way to set the mouse wheel to scroll when in Visual Basic
Editor?

Thanks,

Matthew

FileSearch Problems
 

FileSearch seems to be flakey. Sometimes, it returns a smaller number of files
than it should--sometimes, that smaller number is 0.

You could use the old dir() function to get a list of files in that folder:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String

'change to point at the folder to check
myPath = "F:\Forms\New Orders"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
MsgBox myPath & myNames(fCtr)
'your code would go here
Next fCtr
End If

End Sub

This actually just one level. If you need to go through the subfolders:

http://groups.google.co.uk/groups?se...utput= gplain
From Bill Manville.

Bob Phillips likes to use Scripting to do the same kind of thing:
http://groups.google.co.uk/group/mic...3cf093 7a404a

or

http://snipurl.com/h97z



For the scrolling issue:

I use VBScroll, but there are at least a couple of options:

http://www.gasanov.net/VBScroll.htm
http://www.geocities.com/SiliconVall...freewheel.html



Pflugs wrote:

Hello,

I wrote a program on Excel 2003 that used Application.FileSearch to look for
any file within a certain folder, checked to see if it was an *.xls file,
opened it if it was, copied data to a host spreadsheet, closed the open file
when finished, then moved the file to another folder. I was able to run this
code fine at work, but on my home computer, the code cannot detect any file
within the folder. As a matter of fact, Excel can't seem to find any file of
any file type within any folder.

My file addresses are correct, so that isn't the problem. Is there some
setting that needs to be turned on to allow file system objects? I'm at a
loss...

Here's the code:

Dim fs
Set fs = Application.FileSearch
With fs
.LookIn = "F:\Forms\New Orders"
.Filename = "*.xls"
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
Dim xlsfile As String
xlsfile = .FoundFiles(i)
...

Also, is there a way to set the mouse wheel to scroll when in Visual Basic
Editor?

Thanks,

Matthew

--

Dave Peterson

FileSearch Problems
 

Dave,

Thanks for all of your help. I modified your code to fit my computer, and
it works great. I don't yet understand everything, but I will. Thanks again.

Matthew

"Dave Peterson" wrote:

FileSearch seems to be flakey. Sometimes, it returns a smaller number of files
than it should--sometimes, that smaller number is 0.

You could use the old dir() function to get a list of files in that folder:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String

'change to point at the folder to check
myPath = "F:\Forms\New Orders"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
MsgBox myPath & myNames(fCtr)
'your code would go here
Next fCtr
End If

End Sub

This actually just one level. If you need to go through the subfolders:

http://groups.google.co.uk/groups?se...utput= gplain
From Bill Manville.

Bob Phillips likes to use Scripting to do the same kind of thing:
http://groups.google.co.uk/group/mic...3cf093 7a404a

or

http://snipurl.com/h97z



For the scrolling issue:

I use VBScroll, but there are at least a couple of options:

http://www.gasanov.net/VBScroll.htm
http://www.geocities.com/SiliconVall...freewheel.html



Pflugs wrote:

Hello,

I wrote a program on Excel 2003 that used Application.FileSearch to look for
any file within a certain folder, checked to see if it was an *.xls file,
opened it if it was, copied data to a host spreadsheet, closed the open file
when finished, then moved the file to another folder. I was able to run this
code fine at work, but on my home computer, the code cannot detect any file
within the folder. As a matter of fact, Excel can't seem to find any file of
any file type within any folder.

My file addresses are correct, so that isn't the problem. Is there some
setting that needs to be turned on to allow file system objects? I'm at a
loss...

Here's the code:

Dim fs
Set fs = Application.FileSearch
With fs
.LookIn = "F:\Forms\New Orders"
.Filename = "*.xls"
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
Dim xlsfile As String
xlsfile = .FoundFiles(i)
...

Also, is there a way to set the mouse wheel to scroll when in Visual Basic
Editor?

Thanks,

Matthew

--

Dave Peterson