changing cells

  • Thread starter Jean-Paul De WInter
  • Start date
J

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
 
R

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
 
J

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
 
R

Ron de Bruin

Hi Jean-Paul
where to put this code???
how to run it??
Click to expand...

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
Click to expand...
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
Click to expand...

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
Click to expand...

mybook.Worksheets(1).Range("A1").Value = "Ron"
 
J

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
 
R

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
 
J

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
 
R

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.
 
Top