+ Reply to Thread
Results 1 to 27 of 27

Find and replace in word document

  1. #1
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Find and replace in word document

    Hi everyone, I am having some issues finding and replacing a word document from excel. If anyone could help me out I would greatly appreciate it.

    I have the following code
    Please Login or Register  to view this content.
    Could anyone tell me what I am doing wrong?
    It should be looping down column A for what to find, looping down column B for what to replace, and replacing no matter if its in a header or a text box or in the main document.

    Thanks in advanc efor any help!

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    See this article about StoryRanges and the pitfalls.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Thanks for the reply!
    Is there a better way to do it? I really only need it to look in text boxes, headers/footers/and the main document. I'm pretty new to vba so this is pretty confusing for me.

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    Better way? No. This is it.

    You can test for the story ranges you're interested in.

    Please Login or Register  to view this content.
    I'm pretty new to vba so this is pretty confusing for me.
    Yeah, you picked a tough one to get started with.

  5. #5
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Thanks for your help. When I try to adapt that code so it loops through my cells to figure out what it is finding/replacing. I am getting an error that highlights the .find and says argument not optional, here:
    Please Login or Register  to view this content.
    This is what i tried to add to what you gave me.
    Please Login or Register  to view this content.
    Is it something i am doing wrong between the applications?

  6. #6
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    When working with Excel and Word, you always have to specify WHICH Range you're referring to.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    thanks so much, when i run it now it gives me a new error at
    Please Login or Register  to view this content.
    It says object variable or With block variable not set.

    This is my full code
    Please Login or Register  to view this content.
    Truly appreciate your help with this

  8. #8
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    I can't believe I missed this earlier.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    I think we're close!
    Nooooow im getting an error that makes me have to kill the process in the task manager. It gives me a message that pops up every few seconds with an OLE error, which im assuming has to do with excel trying to control word with no response. Once i kill the process in the task manager it highlights the
    Please Login or Register  to view this content.
    should i not have ALL of this?
    Please Login or Register  to view this content.
    I would like to retain the ability to choose which word file i am opening if that is possible. Thanks again for all your help, I hope to understand this well enough to apply it to other things soon!



    btw heres the total code so far
    Please Login or Register  to view this content.
    Last edited by bruizer31; 06-13-2013 at 05:30 PM. Reason: added code

  10. #10
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    should i not have ALL of this?
    I don't understand.

    Use F8 to step through the code to see if you can locate where it's hanging.

  11. #11
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Wait... it might be working, let me try it again!

  12. #12
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Okay now the error i am getting when i run it is runtime error 462 - the remote server machine does not exist or is unavailable. It highlights
    Please Login or Register  to view this content.
    which is located in this area
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    I am also occasionaly receiving an error that says Microsoft Excel is waiting for another application to complete an OLE action. And if i hit okay it will just pop up again a moment later.
    I have to go into task manager and kill the process.

  14. #14
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    Please Login or Register  to view this content.
    This is quite volatile, meaning it very unpredictable.

    One more correction to the work document object.
    Please Login or Register  to view this content.
    Also, look for a VBA addin called Smart Indent. It indents your code and makes reading very easy. If you leave a End If or a Next off somewhere, the indentation will look funny, giving you a chance to catch the error before runtime.

  15. #15
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    thanks! i will download that now. I'm still getting hung up on that OLE error, when i end the task and hit debug it highlights
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    I think the ole error happens when i run the macro after running it with an error. A dialogue box that asks if i want to open the file even though it crashed last time pops up and im assuming thats where it is getting hung up.
    When i get it to pass that part, it still runs until the
    Please Login or Register  to view this content.
    and then tells me that objext variable or With block variable not set.

    Full code here:
    Please Login or Register  to view this content.

  17. #17
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    Ok, this runs without incident on my machine. If you can upload a sample word doc (and a sample excel file too. You can zip them.) so I can test further.

    I only made a few changes. Mostly at the end of the code.

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    It's working! the last edits - Set wdDoc = wdApp.Documents.Open(newfn) - fixed the part where mystoryrange was = nothing, which i think was what was causing the error.

    Thank you!

  19. #19
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Find and replace in word document

    I didn't get a chance to post this yesterday, but here goes... A possible cause for this problem is that whenever you are automating Word from Excel and an error happens that forces you to stop the macro, an instance of Word is still open. If you come to run the same macro again, it often will catch the first instance of word and cause these kinds of problems. The only way I know of to handle this is to start the Task Manager and kill the open Word processes before re-running the macro in Excel a second time. You could automate this process, but it would also close all your open word documents without saving them and could be a nightmare if someone forgot to save and close all word files before running the macro. In that case, a few error messages are a better reminder that the user has to intervene rather than lose their work.

    Hope this helps.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  20. #20
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Find and replace in word document

    A possible cause for this problem is...
    That is so true!

    I usually create the macro in the native application, then port it over.

    Also bruizer31, when you write in the native application, it's not necessary to prefix the line with the application Name. For instance, when you write in Excel, it's unnecessary to include Excel in front of the commands. It assumed by the host. However, as I pointed out earlier, when you refer to an outside application, in this case Word, you have to specify what the object refers to.

    Good luck!

  21. #21
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Oh okay thank you. I didn't realize that - it will make my code easier to read hopefully!

    I've been running the macro without any hitches for the past few days, but I noticed when i pull in a number to word, it is ignoring the formatting from excel. If i pull in a dollar amount, number separated with commas, or even a cell that has been conditionally formatted (i have one that turns 150,000 to 150k) it ignores them. It IS working as far as equations go, pulling the value and not the actual formula in the cell.

    If either of y'all have any pointers on how to fix that I'd really appreciate it.

  22. #22
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Find and replace in word document

    Whenever I link data between and excel and word, I first convert all the numbers to text. For example a simple formula like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    would have a lot of decimal spaces I don't want to show. In Excel, I change the way it is displayed (e.g. 2 numbers after the decimal). I can also use a round, etc. The problem is when you link this cell to Word, it still shows a lot of decimal spaces. The trick is to convert the final number to a text:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps.

    abousetta

  23. #23
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Please Login or Register  to view this content.
    Works for thousands, but if i put in 1,600,000 it gives me a value error?
    Thanks for your help

  24. #24
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Find and replace in word document

    This is what you are after:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    abousetta

  25. #25
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Thanks! Works Great.

    I appreciate all your help

  26. #26
    Registered User
    Join Date
    05-14-2013
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    78

    Re: Find and replace in word document

    Actually it's not working. It is showing up as .1 m even when the number is 600,000. Also, is there a way to make it round? So that if the number is 1,899,990 it will be 1.9m?
    Thanks

  27. #27
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Find and replace in word document

    Here is an updated formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    600,000 is represented as 600.0K
    600,100 is represented as 600.1K

    6,000,000 is represented as 6.0m
    6,100,000 is represented as 6.1m

    abousetta

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1