Begging For Help Again *Love you guys*

[yojeff]

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.

 

[somesoldiers]

change title to sommat the rules like and post a small sample of what you have and what you would like to achieve

 

[jproffer]

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]

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]

Is there other stuff in the folder?

 

[yojeff]

No, but I could put other stuff if it would help.

 

[jproffer]

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.

 

[yojeff]

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]

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]

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