S
SteveDB1
Morning.
Back on Tax day-- 4/15/08-- an individual with the moniker- ucanalways--
posted a request to search through files, and Dave Peterson posted a response.
The link is:
http://www.microsoft.com/communitie...79b0&mid=e0b45239-df59-449e-85bf-679518f24400
I'm interested in modifying Dave's code to match my needs.
Here's the primary code.
'--------------------------------------------------------------------------
Sub testme01()
Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook
Dim FSO As Object
'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
myFile = ""
On Error Resume Next
myFile = Dir(myPath & "DTR*.xl*") 'I changed the xls name to match my own
'need.
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
'Set FSO = CreateObject("scripting.filesystemobject")
'This maps my network drives. This was from codeout of Ron DB's Copy4 macro.
'get the list of files
fCtr = 0
Do While myFile <> ""
'myFile = Left(myFile, InStr(myFile, ".") - 1)
'This appears to remove the existing file's extension. I would like to strip
the file
'extension to compare IF a particular file has been operated on, in a
subdirectory-
'directory B.
'If FSO.FileExists("C:\StevesTemp\PreRun\PostRun\" & myFile & ".xlsx") =
True Then 'this is part of Ron DB's modification to compare files.
'final directory- directory B
'MsgBox "The file: " & myFile & " has been processed."
If LCase(myFile) Like LCase("*.xl*") Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
myFile = Dir()
'End If- I did try this input, and it just gave an infinite loop. How
would I go about modifying to do my compare file?
Loop
If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
MsgBox "this is a placeholder for another macro to perform some set of
tasks."
'this is a msgbox I placed to show me that it could in fact do the job I want.
'do some stuff
TempWkbk.Close savechanges:=False 'or True
Next fCtr
End If
End Sub
'----------------------------------------------------------
This macro works, until I place my elements in there to
1- Compare files in the base directory- A, to files in subdirectory- B.
2- Strip the file extension off the file in directory A.
3- If the file in director A has a matching file name (without the file
extension) in directory B, I want to skip that file, and go to the next on in
directory A.
i.e. if MyPath & FileA = MyPath1 & FileA then .......
Thank you for your helps.
Back on Tax day-- 4/15/08-- an individual with the moniker- ucanalways--
posted a request to search through files, and Dave Peterson posted a response.
The link is:
http://www.microsoft.com/communitie...79b0&mid=e0b45239-df59-449e-85bf-679518f24400
I'm interested in modifying Dave's code to match my needs.
Here's the primary code.
'--------------------------------------------------------------------------
Sub testme01()
Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook
Dim FSO As Object
'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
myFile = ""
On Error Resume Next
myFile = Dir(myPath & "DTR*.xl*") 'I changed the xls name to match my own
'need.
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If
'Set FSO = CreateObject("scripting.filesystemobject")
'This maps my network drives. This was from codeout of Ron DB's Copy4 macro.
'get the list of files
fCtr = 0
Do While myFile <> ""
'myFile = Left(myFile, InStr(myFile, ".") - 1)
'This appears to remove the existing file's extension. I would like to strip
the file
'extension to compare IF a particular file has been operated on, in a
subdirectory-
'directory B.
'If FSO.FileExists("C:\StevesTemp\PreRun\PostRun\" & myFile & ".xlsx") =
True Then 'this is part of Ron DB's modification to compare files.
'final directory- directory B
'MsgBox "The file: " & myFile & " has been processed."
If LCase(myFile) Like LCase("*.xl*") Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
myFile = Dir()
'End If- I did try this input, and it just gave an infinite loop. How
would I go about modifying to do my compare file?
Loop
If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
MsgBox "this is a placeholder for another macro to perform some set of
tasks."
'this is a msgbox I placed to show me that it could in fact do the job I want.
'do some stuff
TempWkbk.Close savechanges:=False 'or True
Next fCtr
End If
End Sub
'----------------------------------------------------------
This macro works, until I place my elements in there to
1- Compare files in the base directory- A, to files in subdirectory- B.
2- Strip the file extension off the file in directory A.
3- If the file in director A has a matching file name (without the file
extension) in directory B, I want to skip that file, and go to the next on in
directory A.
i.e. if MyPath & FileA = MyPath1 & FileA then .......
Thank you for your helps.