counting xls files in a folder
 

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

counting xls files in a folder
 

Look in the vba help index for DIR


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dstiefe" wrote in message
...
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

counting xls files in a folder
 

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

counting xls files in a folder
 

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

counting xls files in a folder
 

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

counting xls files in a folder
 

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

counting xls files in a folder
 

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

counting xls files in a folder
 

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

counting xls files in a folder
 

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