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 edurden,
I'll start with the answer to the first question, since the info in question 2 isn't quite enough to give a real solution (for me, at least).
For your multi-file update, I've also made some assumptions:
1) Your data files are all in the same directory "test", which is a subdirectory of your "current" directory
2) They have an xls extension
3) They are the only excel files in the directory
4) The tab with the cell you want to change is called "DataTab"
5) The cell to be updated is A1
6) The new value is "newvalue"

Modify those elements in the following code appropriately to reflect where the files really are, the tab name, and the cell to be updated.

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

Hope this provides a starting point,
Cindy

 

[Fazza]

seems like you could modify Cindy's code to achieve the second goal - open each file and extract data from certain cells. BTW, it may not be necessary to open files to extract data. Fazza

 

[Cindy Ellis]

Hi Fazza,
Your suggestion was certainly in my mind...I figure if he responds back with more detail, I'll fit it in to the framework that's there.
I've never tried extracting data without opening the file...although I guess in this case if the file has to be opened to update it, it wouldn't really apply in this case (assuming they're the same files!)
For future reference though, would I just refer to the fully qualified cell references of the still-closed file?
Thanks,
Cindy

 

[Fazza]

hi Cindy.

An old Excel 4 macro function can be used as explained http://j-walk.com/ss/excel/tips/tip82.htm I think one can use functions too, such as SUM or VLOOKUP or COUNT, etc - which can be handy. Maybe query tables - via menu path, data, import external data, new database query - which work on closed files can help too. (Depending on the data setup.) And, it seems, as you suggested, that newest versions of Excel may even work with out any tricks, just the full reference...??

regards, Fazza

 

[Cindy Ellis]

Hi Fazza,
The XLM macro approach (described in the link) works well. I played around with some other possible approaches, and nothing in Excel 2000 worked.
Thanks!
Cindy

 

[edurden]

More Info

Thanks for the help. Here is a little bit of background info on what I am trying to do.

Thanks for the help. Here is a little bit of background on what I am trying to do.

I am working with a valuation template I created to value a portfolio of properties. The valuation principals are the same for all properties, but property characteristics are different. Question 1 came from a recent problem I had when I completed the valuation for all of the properties, and an "across the board” assumption changed.

After all properties are valued I then create a Summary Table that is linked to each workbook. I figured it would be easier to have the summary table automatically pull the info rather than linking it. It would also make it easier to update the table if changes are made.

I also have a third quest that is a little more complex but I'll put it out there anyway.

After I create a template to value a portfolio I then have to create a new file for each property. Is it possible to create a Macro that would copy the template x number of times (x being the number of properties) and name each file. The name would need to be pulled form a list of properties "master" in a separate workbook. For instance:

Master:

Col 1: Property Number(1)
Col 2: Address(1 Main St.)
Col 3: City(anywhere)
Col 4: State(ST)

Property file name: 1-1 Main St anywhere, ST.xls

Thanks again for the help!

 

[Cindy Ellis]

Hi edurden,
My time is really limited today...I probably won't be able to get back to this until late this evening. Did you try the "update" macro above? (I would recommend working from a copy of your files until you test it out and verify that it works for you).
Everything that you've described is do-able with a macro.
I'm not sure why "linking" to the data for your table makes it more difficult to maintain, though. Linked data can be updated in your summary table just by opening the summary workbook, then it will update the linked data without even opening up all the other files. Or...if that's slowing things down when things haven't changed, and you just want to update the table on command, that would be a good use of a macro.
If you identify some specific ranges that you want to grab for the summary, it will be easier to put together a macro that will do what you want.
Cindy

 

[edurden]

Hi Cindy,

Thank you for your time. I got the update code to work on a test run, and I am working on the "getvalue" code provided in the link above. I am having success with the getvalue code, but am wondering if there is a way to make it go to the next file and next line on my summary table automatically rather than repeating the code and changing the file name & Line number.

The macro is pulling 14 cells into the summary table and I am doing this with 74 files. This is what it looks like for the first three properties:


Sub TestGetValue2()
p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 39 To 39
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 20) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 41 To 41
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 21) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 43 To 43
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 22) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 32 To 32
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 15) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 45 To 45
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 23) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "RCNLD-Bldgs"
Application.ScreenUpdating = False
For r = 35 To 35
For c = 9 To 9
a = Cells(r, c).Address
Cells(5, 12) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "RCNLD-SI"
Application.ScreenUpdating = False
For r = 16 To 16
For c = 12 To 12
a = Cells(r, c).Address
Cells(5, 13) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 35 To 35
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 11) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 36 To 36
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 17) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1240-1109_Montee_Masson,Mascouche,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 37 To 37
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 18) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150 Boul Cartier, Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 38 To 38
For c = 3 To 3
a = Cells(r, c).Address
Cells(5, 19) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150 Boul Cartier, Laval,QC.xls"
s = "Rcnld Equipment"
Application.ScreenUpdating = False
For r = 79 To 79
For c = 13 To 13
a = Cells(r, c).Address
Cells(5, 14) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150 Boul Cartier, Laval,QC.xls"
s = "Market Rent Analysis"
Application.ScreenUpdating = False
For r = 15 To 15
For c = 6 To 6
a = Cells(r, c).Address
Cells(5, 25) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150 Boul Cartier, Laval,QC.xls"
s = "Direct Cap Summary"
Application.ScreenUpdating = False
For r = 5 To 5
For c = 5 To 5
a = Cells(r, c).Address
Cells(5, 27) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 39 To 39
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 20) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 41 To 41
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 21) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 43 To 43
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 22) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 32 To 32
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 15) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 45 To 45
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 23) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "RCNLD-Bldgs"
Application.ScreenUpdating = False
For r = 35 To 35
For c = 9 To 9
a = Cells(r, c).Address
Cells(6, 12) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "RCNLD-SI"
Application.ScreenUpdating = False
For r = 16 To 16
For c = 12 To 12
a = Cells(r, c).Address
Cells(6, 13) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 35 To 35
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 11) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 36 To 36
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 17) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 37 To 37
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 18) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 38 To 38
For c = 3 To 3
a = Cells(r, c).Address
Cells(6, 19) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Rcnld Equipment"
Application.ScreenUpdating = False
For r = 79 To 79
For c = 13 To 13
a = Cells(r, c).Address
Cells(6, 14) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Market Rent Analysis"
Application.ScreenUpdating = False
For r = 15 To 15
For c = 6 To 6
a = Cells(r, c).Address
Cells(6, 25) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1330-3150_Boul_Cartier,Laval,QC.xls"
s = "Direct Cap Summary"
Application.ScreenUpdating = False
For r = 5 To 5
For c = 5 To 5
a = Cells(r, c).Address
Cells(6, 27) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 39 To 39
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 20) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 41 To 41
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 21) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 43 To 43
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 22) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 32 To 32
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 15) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 45 To 45
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 23) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\Canada\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "RCNLD-Bldgs"
Application.ScreenUpdating = False
For r = 35 To 35
For c = 9 To 9
a = Cells(r, c).Address
Cells(7, 12) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "RCNLD-SI"
Application.ScreenUpdating = False
For r = 16 To 16
For c = 12 To 12
a = Cells(r, c).Address
Cells(7, 13) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 35 To 35
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 11) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 36 To 36
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 17) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 37 To 37
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 18) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Exec Summ"
Application.ScreenUpdating = False
For r = 38 To 38
For c = 3 To 3
a = Cells(r, c).Address
Cells(7, 19) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Rcnld Equipment"
Application.ScreenUpdating = False
For r = 79 To 79
For c = 13 To 13
a = Cells(r, c).Address
Cells(7, 14) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Market Rent Analysis"
Application.ScreenUpdating = False
For r = 15 To 15
For c = 6 To 6
a = Cells(r, c).Address
Cells(7, 25) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True

p = "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"
f = "1340-260_Boul_Concorde,Laval,QC.xls"
s = "Direct Cap Summary"
Application.ScreenUpdating = False
For r = 5 To 5
For c = 5 To 5
a = Cells(r, c).Address
Cells(7, 27) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True


End Sub

 

[Cindy Ellis]

Hi,
Your macro can be simplified substantially...but now I have some really specific questions and assumptions about your files based on what you posted.
1) Are all of your files in the directory "G:\CVC\Real Estate\EGD Properties\Valuation Workbooks"?
2) Do all of the workbooks have the same format? (In the code you posted, it doesn't look like all sheets were referenced for each property, but it could have been a cut & paste error.)
3) Assumption is that the sheet names in each workbook are "Exec Summ", "RCNLD-Bldgs", "RCNLD-SI", "Rcnld Equipment", "Market Rent Analysis", and "Direct Cap Summary"
4) The summary for each propery is on a single row.
5) There are 14 pieces of data for each property, that get put into columns K-O, Q-W,Y, and AA of the appropriate summary row for the property.
Is this pretty close?
Cindy
Edited to fix a typo in the output columns