Modifying existing VBA process/code

[im2bz2p345]

Hello VBA Experts,

I had received some *excellent* help from daverunt & MrKowz regarding automating a process on a worksheet for a project at my job: http://www.mrexcel.com/forum/showthread.php?t=528448

I have a similar project, which I would like some VBA coding help with.

We have a bunch of ROIC calculation files for each business unit of my company. The path to the folder which contains all of these files is: O:\Shared Svcs Acctg\_Close 2011\All\03 Mar\ROIC Calculation. Here is a screenshot showing these files: http://ploader.net/files/95bcf8a341337872adc4e406035f654d.png

I want to be able to use these ROIC files and update columns AL-AO in my master file (called "ROIC Historical Payout Detail"). Here is a screenshot of my master file: http://ploader.net/files/f2f6fbc15e328ef4be11dcdcec763bc7.png

Notice that in my master file, there are business units in column A. I need to able to use each of these business units (i.e. 16122), then automatically look for a file with that specific business unit in it's name (ROIC_bonus_calc_16122_2011 2011-03-28 16122.xls). Once it's found, open it up, and calculate the following information. Here is an example of a source file: http://ploader.net/files/c6a256d09b0faebb5b07173b48487bee.png

2011 Pre Tax Cash Flow (Column AL in master file) = In source file: Pre-Tax Cash Flow/# of working days in the year. It would be nice if it could lookup the words "Pre-tax Cash Flow" in Column B and use the number in Column C associated with that row. For the # of working days, if it could lookup "S0998" in Column D and use the number in Column C associated with that row.

If I could just be shown how to do this for one of the calculations, I could probably figure out the rest on my own.

The biggest way this differs from my previous project is that I need to able to run this monthly on it's own. So in my master file, I want to able to change the date in column AM (right now it says 3/31/2011). The path where the ROIC files are being pulled from should automatically changed based on this date (I have highlighted the part that needs to change based on the month - O:\Shared Svcs Acctg\_Close 2011\All\03 Mar\ROIC Calculation). The "03" stands for March being the 3rd month and the month is always going to be abbreviated by the first 3 letters (i.e. April will be Apr).

I know this sounds like a lot, but it really not that bad, my biggest difficulty is figuring out how to open specific files based on the BU and the path issue that I described above.

Please let me know if any clarification is needed,

~ Im2bz2p345

 

[im2bz2p345]

I was just trying to figure out the path code on my own (I'm VERY new to VB coding). After looking at the previous project that I had help on & doing some Google searches, I came up with this:

Sub TestPath()
Dim path As String, _
monthnumber As String, _
monthabbreviation As String, _
combinedpath As String
path = "O:\Shared Svcs Acctg\_Close 2011\All\"
monthnumber = ThisWorkbook.Sheets("All Regions_Detail").Range("AM1").NumberFormat = "mm"
monthabbreviation = ThisWorkbook.Sheets("All Regions_Detail").Range("AM1").NumberFormat = "mmm"""
combinedpath = path & monthnumber & " " & monthabbreviation & "ROIC Calculation"

Unfortunately monthnumber & monthabbreviation return "False" as I step through the code. Could anyone help with guiding me on what I'm doing wrong?

Sorry for the very basic question,

~ Im2bz2p345

 

[GTO]

Hi there,

Quickly looked, but your current appears to ask, is the .NumberFormat of a certain cell "mm"?

Presuming you want a long, try:
<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> x()<br><br><SPAN style="color:#00007F">Dim</SPAN> MonthNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> IsDate(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1")) <SPAN style="color:#00007F">Then</SPAN><br>        MonthNumber = Month(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1"))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[GTO]

ACK! If I got that first part right, look at the MonthName Function for the second.

 

[im2bz2p345]

Thanks for your guidance GTO.

This is what I got so far:

Sub x()
Dim MonthNumber As Long, _
MonthAbbreviation As String, _
Path As String
    If IsDate(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1")) Then
        MonthNumber = month(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1"))
        MonthAbbreviation = MonthName(MonthNumber, True)
    End If
    Path = "O:\Shared Svcs Acctg\_Close 2011\All\" & MonthNumber & " " & MonthAbbreviation & "\ROIC Calculation"
End Sub

Unfortunately the month() function only returns to me a one-digit value for the month (i.e. for March, it only gives me a "3"). I need for it to provide a two-digit value. Is this possible?

~ Im2bz2p345

 

[GTO]

Sorry I didn't catch that before. Declare monthnumber as a String instead and wrap what you have now with Format. Like:

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> strMonthNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    strMonthNumber = Format(Month(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1").Value), "00")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[im2bz2p345]

Thanks so much GTO!

Here is my code according to your changes:

Sub x()
Dim MonthNumber As String, _
MonthAbbreviation As String, _
Path 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
    Path = "O:\Shared Svcs Acctg\_Close 2011\All\" & MonthNumber & " " & MonthAbbreviation & "\ROIC Calculation"
End Sub

Okay, now that I have the path code correct, is there someone who can help me run a loop by matching column A in my master file with the appropriate ROIC file in the path?

I PMed daverunt and he has provided me with this code, but unfortunately it didn't work. Maybe someone could modify it with the path code that I have above?

Sub Macro1()
 
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
'Display Open Dialog to select file directory
  filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select BU Files", "Open", False)
 
'If the user cancels file selection then exit
     If TypeName(filenames) = "Boolean" Then
        Exit Sub
     End If
 
'Set xls as SourceFile
        SourceFile = Dir("*.xls")
 
     Do While SourceFile <> ""
 
For Each dn In Rng
    If InStr(SourceFile, dn.Value) > 0 Then
 
    FileMatched = True
 
    Workbooks.Open (SourceFile)
    Set XLSFile = ActiveWorkbook
 
MsgBox("File Opened")
 
  ' -- Do your stuff here--.
 
 
   End If
 
 
    Windows.Application.CutCopyMode = False
    XLSFile.Close False
 
Next
  SourceFile = Dir
  Loop
End Sub

Thanks for any help in advance,

~ Im2bz2p345

 

[im2bz2p345]

After doing some searching around, I found a function that could possibly open a file using a partial part of the filename (see the last reply): http://www.techrepublic.com/forum/questions/101-282778

Not sure if it could be used for this scenario, but if anyone with more knowledge on VBA could offer some assistance/insight, that would be great.

~ Im2bz2p345

 

[im2bz2p345]

*Edit*

I found a better example of code to open Excel files based on partical file names here on the forums: http://www.mrexcel.com/forum/showthread.php?t=540038

Can it be adjusted to fit my scenario by anyone? Please help!

~ Im2bz2p345

 

[daverunt]

Hi,

This is the latest attempt.

error 424 was because the macro was trying to close a file that wasn't opened.
So here we check the filematch was True before attempting to close the file later in the code. Filematch is then reset to false.

As is It should now cycle through the directory and look for BU matches with filenames, open the file, show you a MsgBox with the filename then close the file.

Sub Macro1()
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
'Display Open Dialog to select file directory
  filenames = Application.GetOpenFilename("Excel Files (*.xls*)," & _
    "*.xls*", 1, "Select BU Files", "Open", False)
 
'If the user cancels file selection then exit
     If TypeName(filenames) = "Boolean" Then
        Exit Sub
     End If
 
'Set xls as SourceFile
        SourceFile = Dir("*.xls")
 
     Do While SourceFile <> ""
 
For Each dn In Rng
    If InStr(SourceFile, dn.Value) > 0 Then
 
    Filematched = True
 
    Workbooks.Open (SourceFile)
    Set XLSFile = ActiveWorkbook
 
    MsgBox (ActiveWorkbook.Name)
  ' -- Do your stuff here--.
 
   End If
 
Next
 Windows.Application.CutCopyMode = False
    If Filematched = True Then XLSFile.Close False
   SourceFile = Dir
  Filematched = False
  Loop
 
 
End Sub