Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Macro to find/repl field codes (MS Excel 2003 SP3)

    Edited by HansV to reduce picture in size - please don't post pictures larger than 640 x 480 pixels.

    Hello,
    I am looking for a macro that will allow me to RENAME the fieldcode path in each chapter (from a spreadsheet or a word table if easier) so that the legacy graphic name is now replaced with a graphics catalog number. The process would probably go as follows (see attached screen shot too):

    1. Point to folder full of MS Word .DOC files.
    2. Review original fieldcode path in D4 and compare it with fieldcode path in E4.
    3. Find fieldcode path from D4 of spreadsheet - in folder full of documents (e.g. Chapter 1).
    4. Rename fieldcoe path (when found) to new fieldcode path listed in E4 of spreadsheet.
    5. Loop through spreadsheet and documents until all fieldcodes have been renamed in MS Word documents.

    I assume that this macro will include a variation of the following:
    --------------------------------------------------------------------------------------------------------
    Sub RenameFiles()
    ' Modify as needed but keep trailing backslash
    Const strPath = "C:Temp1"
    Dim r As Long
    Dim n As Long
    n = Cells(Rows.Count, 4).End(xlUp).Row
    For r = 5 To n
    Name strPath & Cells(r, 4) As strPath & Cells(r, 5)
    Next r
    End Sub
    -------------------------------------------------------------------------------------------------------

    Thanks in advance for the assistance.

    I appreciate it.

    Regards,

    Jim
    Attached Images Attached Images
    • File Type: jpg z.jpg (14.1 KB, 1 views)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    You've received many examples of similar code from the Lounge by now, so you should be able to write it yourself, or at least get a good start.
    The basic idea is:
    - Open a document.
    - Display field codes.
    - Loop through the rows of the spreadsheet.
    - Use Find in the document to replace the text from column D with the corresponding text in column E.
    - Hide field codes.
    - Save and close the document.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hello Hans,

    Yes, there are similar examples out there, but I have NOT seen an example of a macro that will search for a specific legacy field code in a group of word documents
    and then substitute in the new field code - from an Excel spreadsheet column cell. The macro I used in the past (from Excel) simply renamed graphics in a graphic folder,
    based on the the two spreadsheet columns.

    Yes, once again I have all these examples, but it does NOT help me to write a macro that fulfills this unique requirement.

    Overall, I am a still a novice in the world of macros and VBA.


    In looking at the Excel macro from the past, you see the following:

    ************************************************** *******************
    Sub RenameFiles()
    ' Modify as needed but keep trailing backslash
    Const strPath = "C:Temp1"
    Dim r As Long
    Dim n As Long
    n = Cells(Rows.Count, 4).End(xlUp).Row
    For r = 5 To n
    Name strPath & Cells(r, 4) As strPath & Cells(r, 5)
    Next r
    End Sub
    ************************************************** ******************

    I would assume that I need to change the strPath & Cells line (I think) to some type of code that will search from those cells into each an every word document field code
    of each document, looking for the legacy field code, which is represented by D5 and then swap it out for the new field code - represented by E5.

    This is a unique interaction between Excel column cells and the MS Word INCLUDEPICTURE field code paths.

    I am NOT looking to do this: doc.Content.Find.Execute FindText:=" ", ReplaceWith:=" ", Replace:=wdReplaceAll

    but rather something like: doc.Content.Find.Execute Find wdFieldIndex:="D5", ReplaceWith:=" E5", Replace:=wdReplaceAll


    I am now wondering if it would be easier to take the Excel columns, put it into MS Word and run it from there.

    This line of code is beyond me, so if you or anyone else can assist, then I appreciate it. If not, then I guess I will have to find another forum
    that is willing to help me come up with the lines of code needed.

    Thanks as always for any assistance.

    Regards,

    -J

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    I wrote a macro that is EXTREMELY similar to what you ask here - see the attachment to <post:=725,205>post 725,205</post:>.

    The attached text file contains a macro that uses Automation to control Word from Excel. Please study the code, and try to understand it so that you can edit it if you need a variation. I have added lots of comments to the code to explain what it does.

    As usual, make a backup of all files involved before testing.
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hi James,

    Even if you get your macro working, which shouldn't be too difficult, it's not likely to do what you want. That's because the INCLUDEPICTURE paths in your workbook aren't valid. As well, the MERGEFORMAT switch is irrelevant for an INCLUDEPICTURE field.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hello Hans,

    Thanks so very much for taking the time to look at this macro request. I really appreciate it.
    Right now, I am trying to run it, but am getting a variable error message. It looks like I need to adjust the variables - either that or there is a bug of some sort. So, I googled
    the error message and saw a reference that said: "Use early binding in Visual Basic .NET or in Visual Basic 2005"

    http://support.microsoft.com/kb/316478


    Then, I found another link that responded to the error by saying:

    ----------------------------------------------------------------------------------------------------------------------------------------------------

    Visual Basic Reference: Error Messages
    Object variable or With block variable not set

    An invalid variable is being referenced. To create an object variable, declare the object variable and then assign a valid reference to the object variable using the Set statement. Similarly, a With...End With block must be initialized by executing the With statement entry point.

    To correct this error:

    1. Make sure the object variable references a valid object, and specify or respecify a reference for the object.

    2. Make sure you did not use an object variable set to Nothing.

    3. Make sure the object library in which the object has been described has been selected in the Add References dialog box.

    4. Make sure your With block is initialized by executing the With statement entry point.


    http://msdn.microsoft.com/en-us/libr...17(VS.80).aspx
    -----------------------------------------------------------------------------------------------------------------------------------------------------

    After receiving the error message popup (see attached), I went into the editor and tried to debug it line by line, but it only kicked me back out to the spreadsheet and
    gave me the same popup error message again.


    Well, I will spend some time on this today trying to figure out what is wrong with the variable(s).

    Thanks again for you assistance. It is appreciated.

    Jim
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    I apologize, I omitted an essential line: after the line

    Do While Not strFile = ""

    I forgot to actually open the document! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    ' Open document
    Set doc = app.Documents.Open(strPath & strFile)

    I have attached the code with the missing lines added.
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hello Hans,

    No problem at all.

    That seems to be do it. The code is working wonderfully now.
    This is a fantastic code for anyone that needs to rename all the graphic names and paths through a spreadsheet.
    The only thing to watch out for is tracked changes, as it will stop on any document with tracked changes. But that is an easy fix.

    Thanks again Hans. Hopefully there are other users out there in the forum that can benefit from this code.

    Regards,

    Jim

  9. #9
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hello Paul,

    This macro does appear to be working. Right now (as I write this), the macro is processing over a hundred documents, against the spreadsheet that I have set up.
    It is actually pretty cool as you will see the macro open each document and then you can watch as the field code paths are changed. I think the macro only shows you the
    first figure it hits and the rest is processed in the background. But overall, it seems to be working as the pc is churning away.

    Thanks for your assistance.

    Regards,

    -J

  10. #10
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hello Hans,

    I would like to modify this existing macro (if possible), so that it DOES NOT look at D5, but rather a wildcard inside the D5 range.

    In other words, I would like the macro to find (starting with D5 cell):

    t_300_00_*.emf

    or maybe to simplify it: t*.emf and then replace it with whatever is in row E5.

    Also, I need to make sure that the macro DOES NOT look for any other instance to that first wildcard, but rather work from top down in ascending order as: D5 becomes E5, D6 becomes E6 and so forth.

    Some of the issues I had in the past, have left me with duplicate figures going down the figure number list and I have had a situation where I might end up duplicate numbering and a numbering sequence that was out of order, such as:

    t0300_00_003.emf
    t0300_00_005.emf
    t0300_00_003.emf
    t0300_00_008.emf

    So is there some way for the macro to work with a wildcard such as: t0300_00_*.emf, so that it renames all the .emfs going down the spreadsheet in numerical order to:

    t0300_00_001.emf
    t0300_00_002.emf
    t0300_00_003.emf
    t0300_00_004.emf

    I can always run this macro one chapter at a time and just edit the beginning of the number for each chapter, knowing that the wildcard in the D5 range will give me an ascending numerical list.

    Anyway, I appreciate your time and am open to any possible suggestions or links to previous posts.

    Regards,

    Jim
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    I don't understand what exactly you want. On the one hand you appear to want to reshuffle the existing numbering, on the other hand you want to replace text from column D with text in column E.
    Can you explain clearly and precisely what you want to accomplish?

  12. #12
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hello Hans,

    Let's see if I can simplify what I would like to do.
    I would like to use a macro in Word (MS Word 2003) to find a line containing both text and characters. When I use the Find in MS Word with wildcards, I can find either the text or the characters, but not both together on the same line.
    Can you tell me how to find both text and characters together on the same line? I have attached a screen shot to better illustrate what I would like to find.

    Thanks,

    Jim
    Attached Images Attached Images

  13. #13
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to find/repl field codes (MS Excel 2003 SP3)

    Hello Hans,

    I seemed to have the macro working now using:

    Do While .Execute(FindText:="ImportObFileDI*'")

    So, I should be all set with this macro.
    Thanks for your assistance.

    Regards,
    -J
    Attached Files Attached Files

Posting Permissions

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