im2bz2p345
Board Regular
- Joined
- Mar 31, 2008
- Messages
- 229
I have the following code, but need some changes made to it:
#1) Instead of specifying "XYZ" as the value to search for, is it possible to run this exact code for ALL the business units (Column A) in my master file (here is a screenshot of it: http://ploader.net/files/f2f6fbc15e328ef4be11dcdcec763bc7.png). So basically a loop would be created, it would look at the 1st business unit in Column A, find the file in the directory, open it, do some actions (ProcessMatchedFile), then move onto the 2nd business unit in Column A, open it, do some actions (ProcessMatchedFile), and so forth...
#2) I'm just having a bit of trouble now doing a series of actions on the source file (in the ProcessMatchedFile). Here is a sample of how a source file looks: http://ploader.net/files/c6a256d09b0faebb5b07173b48487bee.png
I want this to get the value next to words "Pre-tax Cash Flow" in my source file and paste it back into my master file in the appropriate BU's file (that was opened). The parts highlighted in red are the ones that I am not sure how to get.
Hopefully this makes sense. Please feel free to clean up or optimize any code as you see fit (I'm very new to VB coding, so still struggling to do basic things). Let me know if you have any questions or need further clarification.
~ Im2bz2p345
Rich (BB code):
Option Explicit
Private Sub ProcessMatchedFile(ByVal sFileName As String)
Dim num As Variant, _
LR As Long, _
target As String, _
cashflowamt As Long, _
XLSFile As Workbook
MsgBox sFileName
'do somethin with any matching files
Set XLSFile = Workbooks.Open(sFileName)
target = "Pre-tax Cash Flow"
LR = Range("B" & Rows.Count).End(xlUp).Row
'Find the row the string "Pre-tax Cash Flow" appears on.
num = Application.Match("*" & target & "*", Range("B1:B" & LR), 0)
'If no match is found with wildcards, the next match will search for when the string "Pre-tax Cash Flow" is the only thing in the cell.
If IsError(num) Then
num = Application.Match(target, Range("B1:B" & LR), 0)
End If
'If the string is found, continue with code.
If Not IsError(num) Then
'Copies cashflow amount from source file
cashflowamt = Range("C" & num).Offset(0, 1).Value
'Pastes the cashflow amount for the appropriate BU in the master file
cashflowamt.Copy Destination:=ThisWorkbook.Sheets("All Regions_Detail").Range("AL" & BUrow)
End If
End Sub
Private Function DirectoryListToArray(ByVal sSourceFolder As String, FileAry() As String) As Boolean
Dim objFSO As FileSystemObject
Dim objFile As File
Dim lNum As Long
DirectoryListToArray = False
On Error GoTo QuickExit
If sSourceFolder = "" Then Exit Function
Set objFSO = New FileSystemObject
With Application.FileSearch
.LookIn = sSourceFolder
.FileType = msoFileTypeAllFiles
'search sub directories if required
.SearchSubFolders = False
.Execute
Erase FileAry
lNum = 0
If .FoundFiles.Count > 0 Then
For lNum = 1 To .FoundFiles.Count
Set objFile = objFSO.GetFile(.FoundFiles(lNum))
ReDim Preserve FileAry(lNum)
'if you searched sub-folders as well, will need to play with the objFile.Path to qualify the file name
FileAry(lNum) = objFile.Name
Next
End If
End With
Set objFile = Nothing
Set objFSO = Nothing
If lNum > 0 Then DirectoryListToArray = True
QuickExit:
On Error GoTo 0
End Function
Private Function GetFolderName() As String
Dim MonthNumber As String
Dim MonthAbbreviation As String
If IsDate(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1")) Then
MonthNumber = Format(Month(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1").Value), "00")
MonthAbbreviation = MonthName(MonthNumber, True)
End If
GetFolderName = "O:\Shared Svcs Acctg\_Close 2011\All\" & MonthNumber & _
" " & MonthAbbreviation & "\ROIC Calculation\"
End Function
Sub LocateMatchingFiles()
Dim lNum As Long
Dim FAry() As String
Dim sPath As String
'get the source folder name
sPath = GetFolderName
'get the array containing the list of files in the source folder
'if this returns FALSE, no files were found or there was an error
If DirectoryListToArray(sPath, FAry) Then
For lNum = LBound(FAry) To UBound(FAry)
'here determine if the partial string "XYZ" is contained in the file name
'change "XYZ" to whatever needs to be matched
If InStr(1, FAry(lNum), "XYZ", vbTextCompare) > 0 Then
'found a natching filename - now process it
'add the source folder name if you need to do something with the path and file
'make sure there is a "\" on the end of the path name for this to work
ProcessMatchedFile sPath & FAry(lNum)
End If
Next
End If
Erase FAry
End Sub#1) Instead of specifying "XYZ" as the value to search for, is it possible to run this exact code for ALL the business units (Column A) in my master file (here is a screenshot of it: http://ploader.net/files/f2f6fbc15e328ef4be11dcdcec763bc7.png). So basically a loop would be created, it would look at the 1st business unit in Column A, find the file in the directory, open it, do some actions (ProcessMatchedFile), then move onto the 2nd business unit in Column A, open it, do some actions (ProcessMatchedFile), and so forth...
#2) I'm just having a bit of trouble now doing a series of actions on the source file (in the ProcessMatchedFile). Here is a sample of how a source file looks: http://ploader.net/files/c6a256d09b0faebb5b07173b48487bee.png
I want this to get the value next to words "Pre-tax Cash Flow" in my source file and paste it back into my master file in the appropriate BU's file (that was opened). The parts highlighted in red are the ones that I am not sure how to get.
Hopefully this makes sense. Please feel free to clean up or optimize any code as you see fit (I'm very new to VB coding, so still struggling to do basic things). Let me know if you have any questions or need further clarification.
~ Im2bz2p345
