|
changing cells
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 |
changing cells
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 |
changing cells
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 |
changing cells
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 |
changing cells
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 |
changing cells
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 |
| All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com