Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding excel files | New Users to Excel | |||
Finding Duplicate files in Excel 2007 | Excel Worksheet Functions | |||
Finding Excel files | Excel Discussion (Misc queries) | |||
finding all empty excel files in a folder. | Excel Discussion (Misc queries) | |||
finding macro containing excel files | Excel Programming |