Try this
pfff. ingewikkeld <BG
Valt wel mee <g Probeer deze
Sub TestFile1()
Dim mybook As Workbook
Dim sh As Worksheet
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
For Each sh In mybook.Worksheets
sh.Range("A1").Value = "ron"
Next
mybook.Close True
Next i
End If
End With
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Jean-Paul De WInter" wrote in message ...
pfff. ingewikkeld <BG
complicated
I have about 30 files in different folders and subfolders each with about 5
pages....
It would be great to have them all changed with on push on a button.
JP
"Ron de Bruin" schreef in bericht
...
Hi Jean-Paul
where to put this code???
how to run it??
Open a new workbook
Alt-F11
InsertModule from the menubar
paste the sub in there
Alt-Q to go back to Excel
If you do Alt-F8 you get a list of your macro's
Select "Test" and press Run
What with files in the c:\data subfolders
Maybe it is easier to use Application.FileSearch with
.SearchSubFolders = True
You can see on this page how to use it
http://www.rondebruin.nl/copy33.htm
What if I want to change all pages in one worksheet
If you mean worksheets then you need to loop through all sheets
First get the macro to run with one sheet and i will help you with the
next step.
What if I want to replace a text with a new one
mybook.Worksheets(1).Range("A1").Value = "Ron"
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Jean-Paul De WInter" wrote in message
...
where to put this code???
how to run it??
What with files in the c:\data subfolders
What if I want to change all pages in one worksheet
What if I want to replace a text with a new one
Thanks
JP
"Ron de Bruin" schreef in bericht
...
No you have to open them
Example to make A1 in the first sheet of every file in C:\Data bold
Sub Test()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Range("A1").Font.Bold = True
mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Jean-Paul De WInter" wrote in message
...
Hi,
Can I change all A1 cells of all xls-files in the same folder into
the
same
fond or the same colour without having to open them all individually?
Thanks
JP