changing cells

[Jean-Paul De WInter]

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

 

[Ron de Bruin]

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

 

[Jean-Paul De WInter]

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]

Hi Jean-Paul

where to put this code???
how to run it??

Open a new workbook
Alt-F11
Insert>Module 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"

 

[Jean-Paul De WInter]

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]

Try this
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

 

[Jean-Paul De Winter]

Hi

I get an errormessage saying (in Dutch)
door de toepassing of door het object gedefinieerde fout
on line:

sh.Range("A1").Value = "ron"

You kow why?
JP

 

[Ron de Bruin]

Hi Jean-Paul

Do you have sheets that are protected? in the one of your workbooks.
Try it first with a few test files in a folder.