Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    VBA Code - Find & Replace ((xl97,2000,2002))

    I would like to automated the VBE's find & replace (edit...replace) so I can modify 100+ workbooks containing various subroutines. The changes to code would be in userforms and standard modules, changing the path from our network to the local drive (open/close/save files, links, etc.). Doing this manual works great (find = r:, replace=c:, search= current project) but I can't seem to find anything in the VBProject object to let me do this?

    All suggestions are welcome 'cause I don't want to do this manually.

    Thanks - John

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    <P ID="edit" class=small>(Edited by Kevin on 27-Feb-03 11:27. Forgot the replace part...)</P>Well, in Word you can do it with

    VBE.ActiveVBProject.VBComponents(strMod).CodeModul e.Find()

    but I don't know if XL supports that method.

    Oh, I forgot:

    To do the replace you'll have to use the .ReplaceLine method
    or DeleteLine/InsertLinet
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Take a look at Jan Karel's Flexible Find before you spend a lot of time doing that.
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Kevin,

    I got as far as the find part but was looking for something simpler than .replaceline since each line that I want to change is going to be different. Don't know if I need an API call or something along the lines of copying the code to a worksheet (or Word), running a find/replace and reinserting the code.

    John

  5. #5
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Legare,

    Thanks for the link but I only see flexifind working in the worksheet & not in the VBE.

    John

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Too bad you're not using VS.Net. There's are really nice IDE macro facility there.

    The following code does bring the house down ('cause there's the edit toolbar button for this)
    but it shows what you can do: (notice the Word-like object names)

    <pre>Sub CommentRegion()
    Dim selection As TextSelection = DTE.ActiveDocument.Selection()
    Dim start As EditPoint = selection.TopPoint.CreateEditPoint()
    Dim endpt As TextPoint = selection.BottomPoint
    Dim commentStart As String = Utilities.LineOrientedCommentStart()

    DTE.UndoContext.Open("Comment Region")
    Try
    Do While (start.LessThan(endpt))
    start.Insert(commentStart)
    start.LineDown()
    start.StartOfLine()
    Loop
    Finally
    'If an error occured, then need to make
    'sure that the undo context is cleaned up.
    'Otherwise, the editor can be left in a perpetual undo context
    DTE.UndoContext.Close()
    End Try
    End Sub
    </pre>

    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Yep, what you need is in there:

    DTE.Find.FindReplace() ---> as in
    <pre>Sub FindReplaceExample()
    Dim objTextDoc As TextDocument
    Dim objEditPt As EditPoint
    Dim iCtr As Integer
    Dim objFind As Find

    ' Create a new text file.
    DTE.ItemOperations.NewFile("GeneralText File")

    ' Get a handle to the new document and create an EditPoint.
    objTextDoc = DTE.ActiveDocument.Object("TextDocument")
    objEditPt = objTextDoc.StartPoint.CreateEditPoint
    objFind = objTextDoc.DTE.Find

    ' Insert ten lines of text.
    For iCtr = 1 To 10
    objEditPt.Insert("This is a test." & Chr(13))
    Next iCtr
    objEditPt.StartOfDocument()
    objFind.FindReplace(vsFindAction.vsFindActionRepla ceAll, "test", _
    vsFindOptions.vsFindOptionsMatchWholeWord, "NEW THING", _
    vsFindTarget.vsFindTargetOpenDocuments, , , vsFindResultsLocation.vsFindResultsNone)
    End Sub
    </pre>


    Well, you now have something to look forward to when you move to VS.

    Hey, maybe VSTO will have the same IDE macro design facility as VS.Net.

    As soon as I get my beta 2, I'll check that out.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Sorry, but my flexfind is only aimed at worksheet cells.

    I guess he would benefit from downloading mzTools from

    http://www.mztools.com/

    It includes a more advanced find tool.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Kevin & Jan,

    Thanks for your input. Guess I've got a little digging to do. I'll get back to this thread for other possibilities if I can't make any progress.

    Thanks Again,
    John

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Just a thought (and I don't even know if it would work!) - select the contents of an entire module, copy it into a Word document, run your replacements on what is now straight text, then select all and copy it back, replacing the original?
    Beryl M


  11. #11
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Yes - that is an option IF it can be done programatically (how do I copy the userform or module code & paste it back in?).

    John

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    If you're thinking about that route, then consider reading the entire project into
    a regular expression. You should then be able to find/replace on that large
    string variable object, the spit it back out into the project. You may have to do
    it a module at a time.

    Another idea: take a look at Payne Consulting's CodeCleaner (for Word and Excel)
    that util exports the entire project to text files. They do this in order to clean up
    bloated VBA code modules. You could do it to run a grep util (at the OS command level, maybe)
    on the exported text.

    Even another idea: Chris Greaves (of this forum) has a nice utility he calls ProStripper. As I recall,
    it searches through code modules looking for procedures that are not cross-referenced
    (are not actually being called by any other code). You may find something there to help
    with the VBE coding. You'll have to contact him for that.

    Don't give up!
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  13. #13
    Lounger
    Join Date
    Dec 2001
    Location
    Cromwell, Connecticut, USA
    Posts
    40
    Thanks
    2
    Thanked 2 Times in 2 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    Thanks Kevin.

    Never say never !! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    I've no idea whatsoever *how* to do it - but vague memories of posts here and/or other sources where this sort of thing was done/discussed wafted through the back of my mind on reading your posts ...!

    <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23> Sorry, I seem to be much better at thinking up ideas than making them work (as you can see from the number of times I have to beg help from our friends here!).
    Beryl M


  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code - Find & Replace ((xl97,2000,2002))

    MZTools is an excellent help in VBA programming, highly recommended!

    Alas: it can only find accross all open projects, not replace. One for his wish list?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •