S
SteveDB1
morning all.
I have a macro from Dave Peterson that I've modified for my purposes, and
now want to modify it further by placing a limit on the upper, and lower
bounds.
I have approximately 520 files in my primary directory, and want to limit it
to only look at approx. 40 files, in the middle of the batch.
The files all have a common prefix: ABC-.
The rest of the file name is a series of numbers.
The numeric range would be from 231 through 266.
There are 4 or 5 files that are combined with other numeric elements--
178/262, 179/206/259, 179.5/257, 207.5_265.5, as well as a couple of others,
and for these, I'd need to look at the numbers within the string that are
between the 231 through 266.
Thus far, as I've studied lbound, and ubound, it appears this will work to
limit, but I'm unsure how to state it.
The full macro is already working for previous versions, the code is below.
--------------------------------------------------------------
Sub AFileSearch()
Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim myProcessedPath As String
Dim myFileNoExt As String
Dim FSO As Object
Dim AlreadyProcessed As Boolean
Dim TempWkbk As Workbook
'use whatever you know to get the folder
myPath = "C:\StevesTemp\PreRun\"
If myPath = "" Then Exit Sub
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myProcessedPath = myPath & "PostRun"
If myProcessedPath = "" Then Exit Sub
If Right(myProcessedPath, 1) <> "\" Then
myProcessedPath = myProcessedPath & "\"
End If
myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xl*")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
Set FSO = CreateObject("Scripting.FileSystemObject")
'get the list of files
fCtr = 0
Do While myFile <> "" 'this will populate a list of file names.
If LCase(myFile) Like LCase("DTR*.xl*") Then
myFileNoExt = Left(myFile, InStrRev(myFile, ".") - 1)
AlreadyProcessed = FSO.fileexists(myProcessedPath & myFileNoExt &
".xlsx")
If AlreadyProcessed = True Then
MsgBox "The File: " & myFileNoExt & " has already been
processed."
Else
'not there, so include that file
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
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
TempWkbk.Close savechanges:=True 'False 'or
Next fCtr
End If
End Sub
I have a macro from Dave Peterson that I've modified for my purposes, and
now want to modify it further by placing a limit on the upper, and lower
bounds.
I have approximately 520 files in my primary directory, and want to limit it
to only look at approx. 40 files, in the middle of the batch.
The files all have a common prefix: ABC-.
The rest of the file name is a series of numbers.
The numeric range would be from 231 through 266.
There are 4 or 5 files that are combined with other numeric elements--
178/262, 179/206/259, 179.5/257, 207.5_265.5, as well as a couple of others,
and for these, I'd need to look at the numbers within the string that are
between the 231 through 266.
Thus far, as I've studied lbound, and ubound, it appears this will work to
limit, but I'm unsure how to state it.
The full macro is already working for previous versions, the code is below.
--------------------------------------------------------------
Sub AFileSearch()
Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim myProcessedPath As String
Dim myFileNoExt As String
Dim FSO As Object
Dim AlreadyProcessed As Boolean
Dim TempWkbk As Workbook
'use whatever you know to get the folder
myPath = "C:\StevesTemp\PreRun\"
If myPath = "" Then Exit Sub
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myProcessedPath = myPath & "PostRun"
If myProcessedPath = "" Then Exit Sub
If Right(myProcessedPath, 1) <> "\" Then
myProcessedPath = myProcessedPath & "\"
End If
myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xl*")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
Set FSO = CreateObject("Scripting.FileSystemObject")
'get the list of files
fCtr = 0
Do While myFile <> "" 'this will populate a list of file names.
If LCase(myFile) Like LCase("DTR*.xl*") Then
myFileNoExt = Left(myFile, InStrRev(myFile, ".") - 1)
AlreadyProcessed = FSO.fileexists(myProcessedPath & myFileNoExt &
".xlsx")
If AlreadyProcessed = True Then
MsgBox "The File: " & myFileNoExt & " has already been
processed."
Else
'not there, so include that file
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
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
TempWkbk.Close savechanges:=True 'False 'or
Next fCtr
End If
End Sub