Making the same change to multiple Excel files

[edurden]

I am looking for a way to automate the process of opening 64 separate Excel files and making the same change in each file. The change will be to the same cell on the same tab of each file.

I am also looking for a way to open each file and extract the data in certain cells.

Any help would be greatly appreciated.

 

[Cindy Ellis]

Hi,
This should create your summary...I've included the GetValue macro as well because I had to remove the "Dir" function from it to allow the main macro to loop through the files.

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String

'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"

'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Sub Summarize()

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"

f = Dir(p & "\*.xls")       'assumes that the only workbooks in this directory are those you want to summarize
SummaryRow = 5 'Change this to adjust the first row of the summary 
Application.ScreenUpdating = False
Do While f <> ""
    s = "Exec Summ"
    a = Cells(35, 3).Address
    Cells(SummaryRow, 11) = GetValue(p, f, s, a)
    
    s = "RCNLD-Bldgs"
    a = Cells(35, 9).Address
    Cells(SummaryRow, 12) = GetValue(p, f, s, a)
    
    s = "RCNLD-SI"
    a = Cells(16, 12).Address
    Cells(SummaryRow, 13) = GetValue(p, f, s, a)
    
    s = "Rcnld Equipment"
    a = Cells(79, 13).Address
    Cells(SummaryRow, 14) = GetValue(p, f, s, a)
    
    s = "Exec Summ"
    a = Cells(32, 3).Address
    Cells(SummaryRow, 15) = GetValue(p, f, s, a)
    
    a = Cells(36, 3).Address
    Cells(SummaryRow, 17) = GetValue(p, f, s, a)
    
    a = Cells(37, 3).Address
    Cells(SummaryRow, 18) = GetValue(p, f, s, a)
    
    a = Cells(38, 3).Address
    Cells(SummaryRow, 19) = GetValue(p, f, s, a)
    
    a = Cells(39, 3).Address
    Cells(SummaryRow, 20) = GetValue(p, f, s, a)
    
    a = Cells(41, 3).Address
    Cells(SummaryRow, 21) = GetValue(p, f, s, a)
    
    a = Cells(43, 3).Address
    Cells(SummaryRow, 22) = GetValue(p, f, s, a)
    
    a = Cells(45, 3).Address
    Cells(SummaryRow, 23) = GetValue(p, f, s, a)
    
    s = "Market Rent Analysis"
    a = Cells(15, 6).Address
    Cells(SummaryRow, 25) = GetValue(p, f, s, a)
    
    s = "Direct Cap Summary"
    a = Cells(5, 5).Address
    Cells(SummaryRow, 27) = GetValue(p, f, s, a)
    
    f = Dir()
    SummaryRow = SummaryRow + 1
    
Loop
Application.ScreenUpdating = True

End Sub

Hope this helps,
Cindy[/code]

 

[edurden]

Worked like a charm

Hi Cindy,

The macro worked great! Thank you so much. I do have one more question about the update macro, but if you've spent too much time on me already I completely understand. Is there a way to incorporate a vlookup in the update so it changes the same cell to different values? Same assumptions as before and the list for the vlookup would be in the workbook I am running the update macro out of. Thanks again for the help.

 

[Cindy Ellis]

Hi,
Glad the macro worked!
As for your other question...many things are possible, and many things are even easy, I just can't visualize what you are trying to do. Do you want to update the cell to be updated with a value based on other information contained in that file? If so, using a vlookup within the macro may not be the best solution. What cells contain the criteria? How many options are there that depend on the criteria?
Is this a one-time update, or will you need to regularly update the files?
Sorry for so many questions...but it will make the solution closer to what you need.
--Cindy

 

[edurden]

Re

Hi,

Now that I think about it vlookup isn't the answer. I could have the inputs in a list in same order as the "to be updated" files are listed in the directory. So the update sheet would be simple and contain 2 columns (A and B) A would have the property id # and B would contain the Update info. Thanks for the help.

Do you have any suggestions of ways for me to get started learning more on own?

 

[Cindy Ellis]

Hi,
Glad you figured out a solution!
As for getting started learning more...it sort of depends on how you learn. Some people learn from books, and I know there are some good books out there, but that's not how I learned, so I don't have any specific recommendations.
What's important is to get an understanding of fundamental programming concepts, such as variables and control structures, then figure out how to apply that within VBA. One way to learn is by looking at the examples that are in the VBA help system, then tweaking them to do the thing that you want to do. Another really useful way to learn is to browse through the questions and responses in this forum, gaining an understanding of how things really work by trying out the code for yourself. I learn something new almost every day, just reading the posts of the real gurus and Excel MVPs.
Good luck on your journey...
Cindy

 

[edurden]

Update

Hi Cindy,

Thanks again for all the help. I'm still working on the update macro and am having a little more trouble. The macro is running out of a workbook named update.xls and it is updating files in the sub directory of the folder it is located in "valuation workbooks" I have 2 columns in the worksheet "update1" A and B. A contains a unique identifier for each property and B contains the value I need to be updated in each workbook (it’s different for each one) the cell that needs to be updated is cell C14 of the "input" tab. All of files in the sub directory are named after the unique identifier so the files are in the same order as the data. The code I am working with is:


Sub UpdateFiles()
MyDir = ActiveWorkbook.Path
DataDir = MyDir & "\Valuation Workbooks\"
ChDir (DataDir)
Nextfile = Dir("*.xls")
While Nextfile <> ""
Workbooks.Open (Nextfile)
Workbooks(Nextfile).Sheets("input").Range("A1") = "newvalue"
Workbooks(Nextfile).Save
Workbooks(Nextfile).Close
Nextfile = Dir()
Wend
End Sub


-Since the "new value" is different for each file, I am trying to get the macro the pull the value from the update workbook. The update data starts in cell B2 and ends with cell B75. Basicly I want "newvalue" to =B2 of the "update1" tab of the update.exe workbook when updating the first workbook, =B3 for the second.........

Do you think you could help me with this one too?

 

[edurden]

Summary

I'm also getting an error on the summary macro. It worked fine when I was used it with only ten or so files, but when I ran it with 35 it updated 27 of the files and then gave me an error. I added an extra cell but it's pulling that cell in fine for the one's it's getting through. Right now the code looks like this:

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Sub Summarize()

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"

f = Dir(p & "\*.xls")
SummaryRow = 5
Application.ScreenUpdating = False
Do While f <> ""
s = "Exec Summ"
a = Cells(35, 3).Address
Cells(SummaryRow, 11) = GetValue(p, f, s, a)

s = "RCNLD-Bldgs"
a = Cells(35, 9).Address
Cells(SummaryRow, 12) = GetValue(p, f, s, a)

s = "RCNLD-SI"
a = Cells(16, 12).Address
Cells(SummaryRow, 13) = GetValue(p, f, s, a)

s = "Rcnld Equipment"
a = Cells(79, 13).Address
Cells(SummaryRow, 14) = GetValue(p, f, s, a)

s = "Exec Summ"
a = Cells(32, 3).Address
Cells(SummaryRow, 15) = GetValue(p, f, s, a)

a = Cells(36, 3).Address
Cells(SummaryRow, 17) = GetValue(p, f, s, a)

a = Cells(37, 3).Address
Cells(SummaryRow, 18) = GetValue(p, f, s, a)

a = Cells(38, 3).Address
Cells(SummaryRow, 19) = GetValue(p, f, s, a)

a = Cells(39, 3).Address
Cells(SummaryRow, 20) = GetValue(p, f, s, a)

a = Cells(41, 3).Address
Cells(SummaryRow, 21) = GetValue(p, f, s, a)

a = Cells(43, 3).Address
Cells(SummaryRow, 22) = GetValue(p, f, s, a)

a = Cells(45, 3).Address
Cells(SummaryRow, 23) = GetValue(p, f, s, a)

s = "Market Rent Analysis"
a = Cells(15, 6).Address
Cells(SummaryRow, 25) = GetValue(p, f, s, a)

s = "Direct Cap Summary"
a = Cells(5, 5).Address
Cells(SummaryRow, 27) = GetValue(p, f, s, a)

s = "Input"
a = Cells(14, 3).Address
Cells(SummaryRow, 31) = GetValue(p, f, s, a)

f = Dir()
SummaryRow = SummaryRow + 1

Loop
Application.ScreenUpdating = True

End Sub

I'm getting run time error (1004) and it's highlighting :

GetValue = ExecuteExcel4Macro(arg)

Under

Execute an XLM macro


Any idea of what's causing this? Thanks.

 

[Cindy Ellis]

Is there anything unique about the 28th file? Try moving the first 20 or 25 files or so to another directory temporarily, and see if it stops on the same file, or if it makes it through a different set of 27.
If it still stops at 27, then there may be some memory allocation issues (I'm not really familiar with potential limitations of XLM macros), and we can take a different approach (opening, grabbing, and copying the data from the cells, rather than accessing them through getvalue).
If it stops at the same filename that it choked on before, then there's either a sheet that doesn't have the same name, or a merged cell at the "address" that it's looking for, or some such thing.
Cindy

 

[edurden]

RE:

I took out some files in the middle and the macro stopped on the same property, so I am just going to replace that file. Do you have any ideas about the other question I asked. I pasted it below. Thanks for the help.

Hi Cindy,

Thanks again for all the help. I'm still working on the update macro and am having a little more trouble. The macro is running out of a workbook named update.xls and it is updating files in the sub directory of the folder it is located in "valuation workbooks" I have 2 columns in the worksheet "update1" A and B. A contains a unique identifier for each property and B contains the value I need to be updated in each workbook (it’s different for each one) the cell that needs to be updated is cell C14 of the "input" tab. All of files in the sub directory are named after the unique identifier so the files are in the same order as the data. The code I am working with is:


Sub UpdateFiles()
MyDir = ActiveWorkbook.Path
DataDir = MyDir & "\Valuation Workbooks\"
ChDir (DataDir)
Nextfile = Dir("*.xls")
While Nextfile <> ""
Workbooks.Open (Nextfile)
Workbooks(Nextfile).Sheets("input").Range("A1") = "newvalue"
Workbooks(Nextfile).Save
Workbooks(Nextfile).Close
Nextfile = Dir()
Wend
End Sub


-Since the "new value" is different for each file, I am trying to get the macro the pull the value from the update workbook. The update data starts in cell B2 and ends with cell B75. Basicly I want "newvalue" to =B2 of the "update1" tab of the update.exe workbook when updating the first workbook, =B3 for the second.........

Do you think you could help me with this one too?

 

[Cindy Ellis]

Sorry I missed that question in the thread
The following is untested (not at the same computer I prototyped your data on), but ought to work.

Sub UpdateFiles() 
MyDir = ActiveWorkbook.Path 
updatecell = 2
DataDir = MyDir & "\Valuation Workbooks\" 
ChDir (DataDir) 
Nextfile = Dir("*.xls") 
While Nextfile <> "" 
newvalue = ActiveWorkbook.sheets("update1").Range("B" & updatecell).value
Workbooks.Open (Nextfile) 
Workbooks(Nextfile).Sheets("input").Range("A1") = newvalue
Workbooks(Nextfile).Save 
Workbooks(Nextfile).Close 
Nextfile = Dir() 
updatecell = updatecell + 1
Wend 
End Sub

Hope this helps (if it doesn't, please reply back),
Cindy