placing limits on ubound, and lbound



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

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 &

If AlreadyProcessed = True Then
MsgBox "The File: " & myFileNoExt & " has already been
'not there, so include that file
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile

End If
End If
myFile = Dir()

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


Hi again.
I'm starting to think that I've stated this wrong, or have given too complex
a request.
I guess I should ask-- is something like this even possible?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question