Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update WorkBooks in Folder
Hi JavyD
Try this for all files in the folder C:\Data (it change A5 and B5 of the first worksheet) Copy this code in a workbook outside the folder C:\Data and run it 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("A5,B5").Value = 1.3325 mybook.Close True FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "JavyD" wrote in message ... Hey guys, Lets see if this possible with a VBA. I have over 100 work books in a folder. There's two specific cells in each work book that I need to change. Currently the cell is multiplying a figure times 1.40, I need a VBA if possible to take each work book in that folder, and replace 1.40 with 1.3325 in those two cells. Those two cells being D17 and C25. Is this possible. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update WorkBooks in Folder
Hi
The Workbook open have a argument for that (UpdateLinks ) See the VBA help for this -- Regards Ron de Bruin http://www.rondebruin.nl "JavyD" wrote in message ... Ron, thanks, with your formula, sorry I forgot to mention before, each work book that I guess it's adjusting, it ask me to update the v look ups in the work book, can that be stopped? "Ron de Bruin" wrote: Hi JavyD Try this for all files in the folder C:\Data (it change A5 and B5 of the first worksheet) Copy this code in a workbook outside the folder C:\Data and run it 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("A5,B5").Value = 1.3325 mybook.Close True FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "JavyD" wrote in message ... Hey guys, Lets see if this possible with a VBA. I have over 100 work books in a folder. There's two specific cells in each work book that I need to change. Currently the cell is multiplying a figure times 1.40, I need a VBA if possible to take each work book in that folder, and replace 1.40 with 1.3325 in those two cells. Those two cells being D17 and C25. Is this possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through folder of workbooks and add rows | Excel Worksheet Functions | |||
Copy a cell to all workbooks within a folder. | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
Reading information from all workbooks in a folder... | Excel Programming | |||
list Workbooks in Current Folder | Excel Programming |