someone had given me this code... (see below)...but the "filesearch" option
does not pop up when I start typing the code.. I am using 2007 version...will
that make a difference?

Thank you

Here is another method

Sub test()
folder = "c:\temp"

Set FS = Application.FileSearch
With FS
.NewSearch
.LookIn = folder
.SearchSubFolders = False
'.Filename = "Run"
'.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks

FileCount = .Execute

If FileCount > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
Else
MsgBox "There were no files found."
End If

End With


End Sub

Look in the vba help index for DIR


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"dstiefe" <(E-Mail Removed)> wrote in message
news:0911EE2B-C472-40FD-9F14-(E-Mail Removed)...
> someone had given me this code... (see below)...but the "filesearch"
> option
> does not pop up when I start typing the code.. I am using 2007
> version...will
> that make a difference?
>
> Thank you
>
> Here is another method
>
> Sub test()
> folder = "c:\temp"
>
> Set FS = Application.FileSearch
> With FS
> .NewSearch
> .LookIn = folder
> .SearchSubFolders = False
> '.Filename = "Run"
> '.MatchTextExactly = True
> .FileType = msoFileTypeExcelWorkbooks
>
> FileCount = .Execute
>
> If FileCount > 0 Then
> MsgBox "There were " & .FoundFiles.Count & _
> " file(s) found."
> Else
> MsgBox "There were no files found."
> End If
>
> End With
>
>
> End Sub
>
>
>

Have you tried the DIR option i posted///
--
If this post helps click Yes
---------------
Jacob Skaria


"dstiefe" wrote:

> someone had given me this code... (see below)...but the "filesearch" option
> does not pop up when I start typing the code.. I am using 2007 version...will
> that make a difference?
>
> Thank you
>
> Here is another method
>
> Sub test()
> folder = "c:\temp"
>
> Set FS = Application.FileSearch
> With FS
> .NewSearch
> .LookIn = folder
> .SearchSubFolders = False
> '.Filename = "Run"
> '.MatchTextExactly = True
> .FileType = msoFileTypeExcelWorkbooks
>
> FileCount = .Execute
>
> If FileCount > 0 Then
> MsgBox "There were " & .FoundFiles.Count & _
> " file(s) found."
> Else
> MsgBox "There were no files found."
> End If
>
> End With
>
>
> End Sub
>
>
>

it didn't work...



"Jacob Skaria" wrote:

> Have you tried the DIR option i posted///
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "dstiefe" wrote:
>
> > someone had given me this code... (see below)...but the "filesearch" option
> > does not pop up when I start typing the code.. I am using 2007 version...will
> > that make a difference?
> >
> > Thank you
> >
> > Here is another method
> >
> > Sub test()
> > folder = "c:\temp"
> >
> > Set FS = Application.FileSearch
> > With FS
> > .NewSearch
> > .LookIn = folder
> > .SearchSubFolders = False
> > '.Filename = "Run"
> > '.MatchTextExactly = True
> > .FileType = msoFileTypeExcelWorkbooks
> >
> > FileCount = .Execute
> >
> > If FileCount > 0 Then
> > MsgBox "There were " & .FoundFiles.Count & _
> > " file(s) found."
> > Else
> > MsgBox "There were no files found."
> > End If
> >
> > End With
> >
> >
> > End Sub
> >
> >
> >

Note the slash after the folder name
strFolder = "c:\temp\"

If this post helps click Yes
---------------
Jacob Skaria


"dstiefe" wrote:

> it didn't work...
>
>
>
> "Jacob Skaria" wrote:
>
> > Have you tried the DIR option i posted///
> > --
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "dstiefe" wrote:
> >
> > > someone had given me this code... (see below)...but the "filesearch" option
> > > does not pop up when I start typing the code.. I am using 2007 version...will
> > > that make a difference?
> > >
> > > Thank you
> > >
> > > Here is another method
> > >
> > > Sub test()
> > > folder = "c:\temp"
> > >
> > > Set FS = Application.FileSearch
> > > With FS
> > > .NewSearch
> > > .LookIn = folder
> > > .SearchSubFolders = False
> > > '.Filename = "Run"
> > > '.MatchTextExactly = True
> > > .FileType = msoFileTypeExcelWorkbooks
> > >
> > > FileCount = .Execute
> > >
> > > If FileCount > 0 Then
> > > MsgBox "There were " & .FoundFiles.Count & _
> > > " file(s) found."
> > > Else
> > > MsgBox "There were no files found."
> > > End If
> > >
> > > End With
> > >
> > >
> > > End Sub
> > >
> > >
> > >

that worked..

how would i loop through the xls files in the folder and open them?

Thank you1!!

"Jacob Skaria" wrote:

> Note the slash after the folder name
> strFolder = "c:\temp\"
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "dstiefe" wrote:
>
> > it didn't work...
> >
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Have you tried the DIR option i posted///
> > > --
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "dstiefe" wrote:
> > >
> > > > someone had given me this code... (see below)...but the "filesearch" option
> > > > does not pop up when I start typing the code.. I am using 2007 version...will
> > > > that make a difference?
> > > >
> > > > Thank you
> > > >
> > > > Here is another method
> > > >
> > > > Sub test()
> > > > folder = "c:\temp"
> > > >
> > > > Set FS = Application.FileSearch
> > > > With FS
> > > > .NewSearch
> > > > .LookIn = folder
> > > > .SearchSubFolders = False
> > > > '.Filename = "Run"
> > > > '.MatchTextExactly = True
> > > > .FileType = msoFileTypeExcelWorkbooks
> > > >
> > > > FileCount = .Execute
> > > >
> > > > If FileCount > 0 Then
> > > > MsgBox "There were " & .FoundFiles.Count & _
> > > > " file(s) found."
> > > > Else
> > > > MsgBox "There were no files found."
> > > > End If
> > > >
> > > > End With
> > > >
> > > >
> > > > End Sub
> > > >
> > > >
> > > >

Try the below and feedback

Sub Macro()
Dim strFolder As String, strFile As String
Dim intCount As Integer
strFolder = "d:\"
strFile = Dir(strFolder & "*.xls", vbNormal)
Do While strFile <> ""
Workbooks.Open strFolder & strFile
intCount = intCount + 1
strFile = Dir
Loop
MsgBox intCount & " files found"
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"dstiefe" wrote:

> that worked..
>
> how would i loop through the xls files in the folder and open them?
>
> Thank you1!!
>
> "Jacob Skaria" wrote:
>
> > Note the slash after the folder name
> > strFolder = "c:\temp\"
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "dstiefe" wrote:
> >
> > > it didn't work...
> > >
> > >
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > Have you tried the DIR option i posted///
> > > > --
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "dstiefe" wrote:
> > > >
> > > > > someone had given me this code... (see below)...but the "filesearch" option
> > > > > does not pop up when I start typing the code.. I am using 2007 version...will
> > > > > that make a difference?
> > > > >
> > > > > Thank you
> > > > >
> > > > > Here is another method
> > > > >
> > > > > Sub test()
> > > > > folder = "c:\temp"
> > > > >
> > > > > Set FS = Application.FileSearch
> > > > > With FS
> > > > > .NewSearch
> > > > > .LookIn = folder
> > > > > .SearchSubFolders = False
> > > > > '.Filename = "Run"
> > > > > '.MatchTextExactly = True
> > > > > .FileType = msoFileTypeExcelWorkbooks
> > > > >
> > > > > FileCount = .Execute
> > > > >
> > > > > If FileCount > 0 Then
> > > > > MsgBox "There were " & .FoundFiles.Count & _
> > > > > " file(s) found."
> > > > > Else
> > > > > MsgBox "There were no files found."
> > > > > End If
> > > > >
> > > > > End With
> > > > >
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >

ok...

how do i skip the xls file that is open

because the spread sheet i am working with is in the folder

and when i run the code it wants to reopen or clsoe the existing file...and
hence stoping my vba code

make sense?
"Jacob Skaria" wrote:

> Try the below and feedback
>
> Sub Macro()
> Dim strFolder As String, strFile As String
> Dim intCount As Integer
> strFolder = "d:\"
> strFile = Dir(strFolder & "*.xls", vbNormal)
> Do While strFile <> ""
> Workbooks.Open strFolder & strFile
> intCount = intCount + 1
> strFile = Dir
> Loop
> MsgBox intCount & " files found"
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "dstiefe" wrote:
>
> > that worked..
> >
> > how would i loop through the xls files in the folder and open them?
> >
> > Thank you1!!
> >
> > "Jacob Skaria" wrote:
> >
> > > Note the slash after the folder name
> > > strFolder = "c:\temp\"
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "dstiefe" wrote:
> > >
> > > > it didn't work...
> > > >
> > > >
> > > >
> > > > "Jacob Skaria" wrote:
> > > >
> > > > > Have you tried the DIR option i posted///
> > > > > --
> > > > > If this post helps click Yes
> > > > > ---------------
> > > > > Jacob Skaria
> > > > >
> > > > >
> > > > > "dstiefe" wrote:
> > > > >
> > > > > > someone had given me this code... (see below)...but the "filesearch" option
> > > > > > does not pop up when I start typing the code.. I am using 2007 version...will
> > > > > > that make a difference?
> > > > > >
> > > > > > Thank you
> > > > > >
> > > > > > Here is another method
> > > > > >
> > > > > > Sub test()
> > > > > > folder = "c:\temp"
> > > > > >
> > > > > > Set FS = Application.FileSearch
> > > > > > With FS
> > > > > > .NewSearch
> > > > > > .LookIn = folder
> > > > > > .SearchSubFolders = False
> > > > > > '.Filename = "Run"
> > > > > > '.MatchTextExactly = True
> > > > > > .FileType = msoFileTypeExcelWorkbooks
> > > > > >
> > > > > > FileCount = .Execute
> > > > > >
> > > > > > If FileCount > 0 Then
> > > > > > MsgBox "There were " & .FoundFiles.Count & _
> > > > > > " file(s) found."
> > > > > > Else
> > > > > > MsgBox "There were no files found."
> > > > > > End If
> > > > > >
> > > > > > End With
> > > > > >
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >

Check whether the file is open or not using the function mentioned in the
link and based on that open

http://support.microsoft.com/default.aspx?kbid=138621

If this post helps click Yes
---------------
Jacob Skaria


"dstiefe" wrote:

>
> ok...
>
> how do i skip the xls file that is open
>
> because the spread sheet i am working with is in the folder
>
> and when i run the code it wants to reopen or clsoe the existing file...and
> hence stoping my vba code
>
> make sense?
> "Jacob Skaria" wrote:
>
> > Try the below and feedback
> >
> > Sub Macro()
> > Dim strFolder As String, strFile As String
> > Dim intCount As Integer
> > strFolder = "d:\"
> > strFile = Dir(strFolder & "*.xls", vbNormal)
> > Do While strFile <> ""
> > Workbooks.Open strFolder & strFile
> > intCount = intCount + 1
> > strFile = Dir
> > Loop
> > MsgBox intCount & " files found"
> > End Sub
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "dstiefe" wrote:
> >
> > > that worked..
> > >
> > > how would i loop through the xls files in the folder and open them?
> > >
> > > Thank you1!!
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > Note the slash after the folder name
> > > > strFolder = "c:\temp\"
> > > >
> > > > If this post helps click Yes
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "dstiefe" wrote:
> > > >
> > > > > it didn't work...
> > > > >
> > > > >
> > > > >
> > > > > "Jacob Skaria" wrote:
> > > > >
> > > > > > Have you tried the DIR option i posted///
> > > > > > --
> > > > > > If this post helps click Yes
> > > > > > ---------------
> > > > > > Jacob Skaria
> > > > > >
> > > > > >
> > > > > > "dstiefe" wrote:
> > > > > >
> > > > > > > someone had given me this code... (see below)...but the "filesearch" option
> > > > > > > does not pop up when I start typing the code.. I am using 2007 version...will
> > > > > > > that make a difference?
> > > > > > >
> > > > > > > Thank you
> > > > > > >
> > > > > > > Here is another method
> > > > > > >
> > > > > > > Sub test()
> > > > > > > folder = "c:\temp"
> > > > > > >
> > > > > > > Set FS = Application.FileSearch
> > > > > > > With FS
> > > > > > > .NewSearch
> > > > > > > .LookIn = folder
> > > > > > > .SearchSubFolders = False
> > > > > > > '.Filename = "Run"
> > > > > > > '.MatchTextExactly = True
> > > > > > > .FileType = msoFileTypeExcelWorkbooks
> > > > > > >
> > > > > > > FileCount = .Execute
> > > > > > >
> > > > > > > If FileCount > 0 Then
> > > > > > > MsgBox "There were " & .FoundFiles.Count & _
> > > > > > > " file(s) found."
> > > > > > > Else
> > > > > > > MsgBox "There were no files found."
> > > > > > > End If
> > > > > > >
> > > > > > > End With
> > > > > > >
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >