|
Find&Replace on a lot of *.doc' s via an excel macro?
Need an excel macro code, that when executed will open a spesific folder
(full of *.doc files) accept a search string and a replacement from the keyboard, search all of the documents for that string and if find it in anywere on any of them files, replaces all, saves the changed *.docs. Is there such a thing? TIA --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 |
Find&Replace on a lot of *.doc' s via an excel macro?
If you write it.
You can get the code to do a replace in word by going to word, turning on the macro recorder and performing the function manually. As far as creating code to work with word, you can do that with automation. The automation help file has examples of starting and manipulating word from another application: http://support.microsoft.com/?id=253338 INFO: Office Developer Samples and Tools Available for Download http://support.microsoft.com/?id=260410 OFF2000: Microsoft Office 2000 Automation Help File Available http://support.microsoft.com/?id=167223 Microsoft Office 97 Automation Help File Available on MSL -- Regards, Tom Ogilvy "Martyn" wrote in message ... Need an excel macro code, that when executed will open a spesific folder (full of *.doc files) accept a search string and a replacement from the keyboard, search all of the documents for that string and if find it in anywere on any of them files, replaces all, saves the changed *.docs. Is there such a thing? TIA --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 |
Find&Replace on a lot of *.doc' s via an excel macro?
Hi Tom,
I've recorded a macro as you've suggested from word. Here it is down below. Two things about this macro which I couldn't figure out how to cope with: After the replacements I need to save this document with its original (previous) document name. But MSWord didn't give me the option while I was working with the macro. So I had to give it a "new" name and save it. This is why it was named "example_1.doc" before saving. Was it possible to give it its previous name (overwriting) ? Secondly. Although after creating the macro I assigned it to the Document1 file (and not to the original normal.dot of MSWord) now when I open a new blank document to work on something else it contains some elements in the VB editor modules (beleive that they are left overs from my previous work)...How can I get rid of that?. I'd be very happy if you can comment. Thanks for your previous help again. And here goes my macro _______________________________________ Sub Macro1() ' ' Macro1 Macro ' Macro recorded 08.06.2004 by Martyn ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "martyn" .Replacement.Text = "wmartyn" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll ActiveDocument.SaveAs FileName:="example_1.doc", FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False End Sub __________________________________________________ ________ "Tom Ogilvy" wrote in message ... If you write it. You can get the code to do a replace in word by going to word, turning on the macro recorder and performing the function manually. As far as creating code to work with word, you can do that with automation. The automation help file has examples of starting and manipulating word from another application: http://support.microsoft.com/?id=253338 INFO: Office Developer Samples and Tools Available for Download http://support.microsoft.com/?id=260410 OFF2000: Microsoft Office 2000 Automation Help File Available http://support.microsoft.com/?id=167223 Microsoft Office 97 Automation Help File Available on MSL -- Regards, Tom Ogilvy "Martyn" wrote in message ... Need an excel macro code, that when executed will open a spesific folder (full of *.doc files) accept a search string and a replacement from the keyboard, search all of the documents for that string and if find it in anywere on any of them files, replaces all, saves the changed *.docs. Is there such a thing? TIA --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 |
Find&Replace on a lot of *.doc' s via an excel macro?
Martyn:
Don't use SaveAs. Use ActiveDocument..Close SaveChanges:=wdSaveChanges Ed "Martyn" wrote in message ... Hi Tom, I've recorded a macro as you've suggested from word. Here it is down below. Two things about this macro which I couldn't figure out how to cope with: After the replacements I need to save this document with its original (previous) document name. But MSWord didn't give me the option while I was working with the macro. So I had to give it a "new" name and save it. This is why it was named "example_1.doc" before saving. Was it possible to give it its previous name (overwriting) ? Secondly. Although after creating the macro I assigned it to the Document1 file (and not to the original normal.dot of MSWord) now when I open a new blank document to work on something else it contains some elements in the VB editor modules (beleive that they are left overs from my previous work)...How can I get rid of that?. I'd be very happy if you can comment. Thanks for your previous help again. And here goes my macro _______________________________________ Sub Macro1() ' ' Macro1 Macro ' Macro recorded 08.06.2004 by Martyn ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "martyn" .Replacement.Text = "wmartyn" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll ActiveDocument.SaveAs FileName:="example_1.doc", FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False End Sub __________________________________________________ ________ "Tom Ogilvy" wrote in message ... If you write it. You can get the code to do a replace in word by going to word, turning on the macro recorder and performing the function manually. As far as creating code to work with word, you can do that with automation. The automation help file has examples of starting and manipulating word from another application: http://support.microsoft.com/?id=253338 INFO: Office Developer Samples and Tools Available for Download http://support.microsoft.com/?id=260410 OFF2000: Microsoft Office 2000 Automation Help File Available http://support.microsoft.com/?id=167223 Microsoft Office 97 Automation Help File Available on MSL -- Regards, Tom Ogilvy "Martyn" wrote in message ... Need an excel macro code, that when executed will open a spesific folder (full of *.doc files) accept a search string and a replacement from the keyboard, search all of the documents for that string and if find it in anywere on any of them files, replaces all, saves the changed *.docs. Is there such a thing? TIA --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 |
Find&Replace on a lot of *.doc' s via an excel macro?
Thanks Ed, I'll try that.
"Ed" wrote in message ... Martyn: Don't use SaveAs. Use ActiveDocument..Close SaveChanges:=wdSaveChanges Ed --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.701 / Virus Database: 458 - Release Date: 07.06.2004 |
Find&Replace on a lot of *.doc' s via an excel macro?
Instead of using
Activedocument.SaveAs, use Activedocument.Save The ActiveDocument.Save requires no arguments. I am not sure why you saved the recorded macro in word at all. I thought you wanted to do this from Excel. You should have recorded the macro, then copied it an pasted into notepad or into an Excel module, then closed the document in word without saving or deleted the macro. So to get rid of the elements, I guess you should go to the module, do Ctrl+A, then delete. Then save the document. If they are in a general module, then you can delete the module in the VBE using the project explorer. I am not a big Word users, so if you need help working in word, you might want to use the word newsgroups for better answers. -- Regards, Tom Ogilvy "Martyn" wrote in message ... Hi Tom, I've recorded a macro as you've suggested from word. Here it is down below. Two things about this macro which I couldn't figure out how to cope with: After the replacements I need to save this document with its original (previous) document name. But MSWord didn't give me the option while I was working with the macro. So I had to give it a "new" name and save it. This is why it was named "example_1.doc" before saving. Was it possible to give it its previous name (overwriting) ? Secondly. Although after creating the macro I assigned it to the Document1 file (and not to the original normal.dot of MSWord) now when I open a new blank document to work on something else it contains some elements in the VB editor modules (beleive that they are left overs from my previous work)...How can I get rid of that?. I'd be very happy if you can comment. Thanks for your previous help again. And here goes my macro _______________________________________ Sub Macro1() ' ' Macro1 Macro ' Macro recorded 08.06.2004 by Martyn ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "martyn" .Replacement.Text = "wmartyn" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll ActiveDocument.SaveAs FileName:="example_1.doc", FileFormat:= _ wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ SaveAsAOCELetter:=False End Sub __________________________________________________ ________ "Tom Ogilvy" wrote in message ... If you write it. You can get the code to do a replace in word by going to word, turning on the macro recorder and performing the function manually. As far as creating code to work with word, you can do that with automation. The automation help file has examples of starting and manipulating word from another application: http://support.microsoft.com/?id=253338 INFO: Office Developer Samples and Tools Available for Download http://support.microsoft.com/?id=260410 OFF2000: Microsoft Office 2000 Automation Help File Available http://support.microsoft.com/?id=167223 Microsoft Office 97 Automation Help File Available on MSL -- Regards, Tom Ogilvy "Martyn" wrote in message ... Need an excel macro code, that when executed will open a spesific folder (full of *.doc files) accept a search string and a replacement from the keyboard, search all of the documents for that string and if find it in anywere on any of them files, replaces all, saves the changed *.docs. Is there such a thing? TIA --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.700 / Virus Database: 457 - Release Date: 06.06.2004 |
Find&Replace on a lot of *.doc' s via an excel macro?
Thank you very much Tom...
You were most helpful. Cheers Martyn "Tom Ogilvy" wrote in message ... Instead of using Activedocument.SaveAs, use Activedocument.Save The ActiveDocument.Save requires no arguments. I am not sure why you saved the recorded macro in word at all. I thought you wanted to do this from Excel. You should have recorded the macro, then copied it an pasted into notepad or into an Excel module, then closed the document in word without saving or deleted the macro. So to get rid of the elements, I guess you should go to the module, do Ctrl+A, then delete. Then save the document. If they are in a general module, then you can delete the module in the VBE using the project explorer. I am not a big Word users, so if you need help working in word, you might want to use the word newsgroups for better answers. -- Regards, Tom Ogilvy --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.701 / Virus Database: 458 - Release Date: 07.06.2004 |
Find&Replace on a lot of *.doc' s via an excel macro?
Thanks for the suppot Tom.
"Tom Ogilvy" wrote in message ... Instead of using Activedocument.SaveAs, use Activedocument.Save The ActiveDocument.Save requires no arguments. I am not sure why you saved the recorded macro in word at all. I thought you wanted to do this from Excel. You should have recorded the macro, then copied it an pasted into notepad or into an Excel module, then closed the document in word without saving or deleted the macro. So to get rid of the elements, I guess you should go to the module, do Ctrl+A, then delete. Then save the document. If they are in a general module, then you can delete the module in the VBE using the project explorer. I am not a big Word users, so if you need help working in word, you might want to use the word newsgroups for better answers. -- Regards, Tom Ogilvy --- Outgoing mail is certified Virus Free. (Giden posta virüssüz olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.701 / Virus Database: 458 - Release Date: 07.06.2004 |
| All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com