Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 ------------------------------------------------------------- as I consider this farther, I'm wondering if the limitation would be better placed at the Lcase line. Any thoughts? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
placing limits on ubound, and lbound
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? "SteveDB1" wrote: 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 ------------------------------------------------------------- as I consider this farther, I'm wondering if the limitation would be better placed at the Lcase line. Any thoughts? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UBound | Excel Programming | |||
resize(Ubound, Lbound) | Excel Programming | |||
Moving into a cell range using Offset and LBound | Excel Programming | |||
Change LBound of array | Excel Programming | |||
Ubound & Lbound | Excel Programming |