Finding specific excel files
 

I have the following to find files in a folder with .xls extension.

Dim MyPath As String
FilesInPath = Dir(MyPath & "*.xls")

How would tweak this to find excel files that start with "1_"?

Any help would be much appreciated!

Kevin

Finding specific excel files
 

I'd use:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk as Workbook

'use whatever you know to get the folder
myPath = "C:\my documents\excel\test\"
If myPath = "" Then Exit Sub
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 < ""
If LCase(myFile) Like LCase("1_*.xls") Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
'do some stuff
TempWbk.Close savechanges:=False 'or True
Next fCtr
End If

End Sub

wrote:

I have the following to find files in a folder with .xls extension.

Dim MyPath As String
FilesInPath = Dir(MyPath & "*.xls")

How would tweak this to find excel files that start with "1_"?

Any help would be much appreciated!

Kevin

--

Dave Peterson

Finding specific excel files
 

There was a typo:
Change:
TempWbk.Close savechanges:=False 'or True
to:
TempWkbk.Close savechanges:=False 'or True

Dave Peterson wrote:

I'd use:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk as Workbook

'use whatever you know to get the folder
myPath = "C:\my documents\excel\test\"
If myPath = "" Then Exit Sub
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 < ""
If LCase(myFile) Like LCase("1_*.xls") Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
'do some stuff
TempWbk.Close savechanges:=False 'or True
Next fCtr
End If

End Sub

wrote:

I have the following to find files in a folder with .xls extension.

Dim MyPath As String
FilesInPath = Dir(MyPath & "*.xls")

How would tweak this to find excel files that start with "1_"?

Any help would be much appreciated!

Kevin

--

Dave Peterson

--

Dave Peterson

Finding specific excel files
 

Not a tweak of what you have because it doesn't use 'Dir' but here is an
alternate approach. This will reveal all the files in C:\Test with xls
extensions and the first character the digit 1.

_______________________________

Dim strPath As String

strPath = "C:\Test"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set fldr = FSO.GetFolder(strPath)

For Each myFile In fldr.Files
If FSO.GetExtensionName(myFile) = "xls" _
And Left(FSO.GetBAseName(myFile), 1) = "1" Then
MsgBox myFile.Name
End If
Next myFile

Set fldr = Nothing
Set FSO = Nothing

______________________________

Steve Yandl



wrote in message
...
I have the following to find files in a folder with .xls extension.

Dim MyPath As String
FilesInPath = Dir(MyPath & "*.xls")

How would tweak this to find excel files that start with "1_"?

Any help would be much appreciated!

Kevin