Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic replace of VBE code for multiple files
Automatic replace of VBE code for multiple files I have few excel files with "C:\Documents and Settings\" couple of time in the VBE code and in the sheet itself. Is it possible to loop via al the files (they are all saved in the same folder) and automatically replace "C:\Documents and Settings\" with "C:\Dan\" both in the code and on the sheets Many thanks, Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic replace of VBE code for multiple files
Dan,
Try this. You will need to change the path to where your workbooks are stored. Sub LoopThroughDirectory() Dim sh As Worksheet Application.DisplayAlerts = False 'Change this to your directory MyPath = "C:\" ActiveFile = Dir(MyPath & "*.xls") Do While ActiveFile < "" Workbooks.Open Filename:=MyPath & ActiveFile For Each sh In ActiveWorkbook.Worksheets sh.Cells.Replace What:="C:\Documents and Settings\", Replacement:="C:\Dan\", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Next ActiveWorkbook.Save ActiveWorkbook.Close ActiveFile = Dir() Loop Application.DisplayAlerts = True End Sub Mike "Dan" wrote: Automatic replace of VBE code for multiple files I have few excel files with "C:\Documents and Settings\" couple of time in the VBE code and in the sheet itself. Is it possible to loop via al the files (they are all saved in the same folder) and automatically replace "C:\Documents and Settings\" with "C:\Dan\" both in the code and on the sheets Many thanks, Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic replace of VBE code for multiple files
Dan,
I missed the bit about changing the code. I don't know how to do that so this will only change the worksheet text. Mike "Mike H" wrote: Dan, Try this. You will need to change the path to where your workbooks are stored. Sub LoopThroughDirectory() Dim sh As Worksheet Application.DisplayAlerts = False 'Change this to your directory MyPath = "C:\" ActiveFile = Dir(MyPath & "*.xls") Do While ActiveFile < "" Workbooks.Open Filename:=MyPath & ActiveFile For Each sh In ActiveWorkbook.Worksheets sh.Cells.Replace What:="C:\Documents and Settings\", Replacement:="C:\Dan\", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False Next ActiveWorkbook.Save ActiveWorkbook.Close ActiveFile = Dir() Loop Application.DisplayAlerts = True End Sub Mike "Dan" wrote: Automatic replace of VBE code for multiple files I have few excel files with "C:\Documents and Settings\" couple of time in the VBE code and in the sheet itself. Is it possible to loop via al the files (they are all saved in the same folder) and automatically replace "C:\Documents and Settings\" with "C:\Dan\" both in the code and on the sheets Many thanks, Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic replace of VBE code for multiple files
Dan,
Since you double posted the question, I'll work with you in the other post. Regards, Jie Wang Microsoft Online Community Support Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/en-us/subs...#notifications. Note: MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 2 business days is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/en-us/subs.../aa948874.aspx ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tidy up multiple find and replace code | Excel Worksheet Functions | |||
Automatic update multiple files | Links and Linking in Excel | |||
Modify macro code to export multiple cell contents to multiple Text Files | Excel Programming | |||
VBA in Microsoft Excel: Find & Replace method macro across multiple files | Excel Programming | |||
Serach and Replace across multiple files | Excel Programming |