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

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

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

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:
(E-Mail Removed).

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