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.

 

[edurden]

Re

Hi Cindy,

Thanks for the help. I ran the above code and nothing happened. I didn't get an error message or anything like that, it just didn't do anything. Any idea of what could have gone wrong?

 

[Cindy Ellis]

OK...I missed one thing, but that still shouldn't have caused "nothing" to happen. The reference to "A1" should be a reference to "C14".

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("C14") = newvalue 
Workbooks(Nextfile).Save 
Workbooks(Nextfile).Close 
Nextfile = Dir() 
updatecell = updatecell + 1 
Wend 
End Sub

Beyond that, if absolutely nothing appeared to happen, then it's most likely that the first parts of the macro pointed to a directory that didn't contain any .xls files. That would cause "nextfile" to be set to "" immediately and execution would jump to the end of the macro, then bail out.
Try inserting a break in the VB code window at the "While Nextfile" line and at the Workbooks.Open line (just click to the left of the lines where you want to break, so a brown dot appears). Now, when you run the code, it should stop at the first break point and display the code window. If you hover your mouse over the variable names, their values should display. (You can also insert quick watches, etc. but if the hover approach gives you the info you need, it's the simplest aproach for this size macro). First, hover your mouse over the term "nextfile". If it says 'nextfile = "" ' then you know that Excel didn't find any excel files in the directory it was looking in. Hover your mouse over the word "DataDir" and see what its value is. Look in that directory to see if there are Excel files.
If the first "nextfile" isn't empty (or "" to be exact), then click the right arrow at the top of the code window so that code execution continues to the next break point. Hover your mouse over the term "newvalue". If your filename was OK, then this variable should contain the value that range "A1" will be set to in the updated file. Click the right arrow once more, and the macro should grab the next file in the directory.

 

[afa13]

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

Hello Cindy,
I have entered the code in a notepad and saved it as a .bat in the same directory where the excel files i want to change are. However, it is not working. Can you provide a step by step guide to those not so familiar with programming?
Thanks.