Begging For Help Again *Love you guys*

yojeff

New Member
Joined
Jan 25, 2007
Messages
33
Ok, I have a few hundred work orders done in Excel. Each spread sheet is a seperate file but all are identical in structure (basic order form). I need to change the value in the same cell on all the forms (manager's name needs to be changed on all our forms).

Question: How do I do this without having to open and edit all five thousand forms?


This forum is batting 1000 with all my stupid question. I may have to post up my secret mango salsa recipe......... Yum..

Thanks, YoJeff.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

somesoldiers

Board Regular
Joined
Feb 23, 2008
Messages
190
change title to sommat the rules like and post a small sample of what you have and what you would like to achieve
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
They are all on seperate tabs?...or all in seperate workbooks? (and why would you do that? :) )...tab it.

Anyhow, if seperate tabs, select all sheets and change the cell you want to change (this is why you should tab it in one workbook, hehe...but I do understand...HUGE filesize can restrict that)

If each one is in a seperate workbook, are they at least in the same folder? What is the name of the folder? (or better yet, what is the file path to the folder)
 

yojeff

New Member
Joined
Jan 25, 2007
Messages
33
Well, they are in seperate workbooks located in the same folder. Don't make this stuff, just work here.

You want the folder name? Ok here is a folder name:

C:\Documents and Settings\User\Desktop\Forms We Use\Record Key Dimensions\Workorders


So I need to change one line on 500 seperate workbooks (our company's work order forms). It is the same location on all workbooks and the change to be made is the same.
 

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287

ADVERTISEMENT

Is there other stuff in the folder?
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643

ADVERTISEMENT

Code:
Sub test()
directory = "C:\Documents and Settings\User\Desktop\Forms We Use\Record Key Dimensions\Workorders"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folder = FSO.GetFolder(directory)
    
    For Each file In folder.Files
        Workbooks.Open file
        Sheets("MySheet").Range("A1").Value = "Bob Smith"  'Change accordingly
        ActiveWorkbook.Save
        Application.DisplayAlerts = False
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
    Next file

End Sub

Try that, but I would very strongly suggest making a copy of the folder in the same location, renaming the original to something else, then naming the new folder to the old name before you run this. Then you can at least "spot check" the 'new' files to make sure that's what you want and nothing was corrupted before deleting the original folder.

EDIT: By the way, you can run that from any workbook in any folder, or even a workbook that has no path...doesn't matter.
 
Last edited:

yojeff

New Member
Joined
Jan 25, 2007
Messages
33
I keep getting an formula error. I am testing it in a temp folder to see if it will work.
How do I insert your code?

This is how I have been trying to do it:
I select "Insert Function", select "code" from the drop down, then insert this into the brackets.

=CODE(Sub test()
directory = "C:\Temp1"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(directory)

For Each file In folder.Files
Workbooks.Open file
Sheets("MySheet").Range("AT4").Value = "T" 'Change accordingly
ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Next file

End Sub
)
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
Surely your worksheets aren't really called "MySheet" :).

But to your questions:

How do I insert your code?
Anywhere you like. When you open excel, if you want to put it into a brand new workbook adn run it, that's fine. It does NOT have to be in the same folder as the WB's you're working on.

I don't know where you are putting it, but it should be in a regular code module. Open a new workbook, press ALT+F11 to open the VBA editor, press CTRL+R to open the project explorer. Right click on your new book (guessing it will be "Book 1") and go to "Insert" and then "Module". That module will open, then paste the code into the white area at the right.

Go to the developer tab (which can be made visible in the Office button at the top-left of Excel), select "Macros" and run the one called "test".
 

yojeff

New Member
Joined
Jan 25, 2007
Messages
33
One more thing (I hope):
Surely your worksheets aren't really called "MySheet"

The 500 or so forms all have different names that reflect the part numbers.
Example: R555-31-2.xls, R555-31-3.xls, R555-31-4.xls (I think you get the picture).

Now the question..... What do I call "MySheet"?

I feel we are almost at the salsa recipe:biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,049
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top