Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with appilcation.filesearch | Excel Programming | |||
Filesearch in VB6 | Excel Programming | |||
FileSearch | Excel Programming | |||
FileSearch using VBA | Excel Programming | |||
.FileSearch | Excel Programming |