Modifying existing VBA process/code

im2bz2p345 · Apr 11, 2011

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 · Apr 11, 2011

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:

Code:

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 · Apr 11, 2011

Hi there,

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

Presuming you want a long, try:
 Sub x() Dim MonthNumber As Long If IsDate(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1")) Then MonthNumber = Month(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1")) End If End Sub

GTO · Apr 11, 2011

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

im2bz2p345 · Apr 11, 2011

Thanks for your guidance GTO.

This is what I got so far:

Code:

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 · Apr 11, 2011

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

Dim strMonthNumber As String strMonthNumber = Format(Month(ThisWorkbook.Sheets("All Regions_Detail").Range("AM1").Value), "00") End Sub

im2bz2p345 · Apr 11, 2011

Thanks so much GTO!

Here is my code according to your changes:

Code:

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?

Code:

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 · Apr 11, 2011

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 · Apr 11, 2011

*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 · Apr 12, 2011

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.

Code:

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

Modifying existing VBA process/code

im2bz2p345

Board Regular

Excel Facts

im2bz2p345

Board Regular

GTO

MrExcel MVP

GTO

MrExcel MVP

im2bz2p345

Board Regular

GTO

MrExcel MVP

im2bz2p345

Board Regular

im2bz2p345

Board Regular

im2bz2p345

Board Regular

daverunt

Well-known Member

Similar threads

Forum statistics

Share this page

Modifying existing VBA process/code

Board Regular

Excel Facts

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock