#1
|
|||
|
|||
How to Replace Excel Cell Value in MS word by VBA....
Hi Experts,
I want to replace excel cell value in ms word file.... if Excel Column A value is present in ms word document then replace word doc value with Excel Column B with sort name of Column A As.. range A3 = Visual Basic and column B3 = VBA then will replace in ms word doc all "Visual Basic" keyword with "VBA" keyword with background yellow color in ms word.. if same word like "Visual Basic" is many times in ms word then all words replace with "VBA" word with yellow color but the first number of replaced word will be in read color back ground color in ms word file..... Please find attached my excel macro file which i have developed ... Pelase change the code for the same my excel VBA password is----------------- kk This is really urget... Pelase help me.... Last edited by krishnaoptif; 06-18-2012 at 10:32 AM. Reason: Provide my excel VBA password |
#2
|
|||
|
|||
Hi Experts, Please reply me ASAP... This is really urgent for me...
|
#3
|
|||
|
|||
my excel VBA password is----------------- kk
|
#4
|
||||
|
||||
A little patience wouldn't go astray - this forum is run by volunteers and some of us need to do other things sometimes (like sleeping).
Try: Code:
Sub ReplaceExcelCellValueInMswordFile() Dim wdApp As Word.Application, wdDoc As Word.Document Dim dlg As Variant, dataPath As Variant Dim iCount As Long, r As Long Dim strSearch, strReplace As String r = 3 On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err.Number <> 0 Then 'Word isn't already running Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 Set dlg = Application.FileDialog(msoFileDialogFilePicker) dlg.Title = "Select your MS word File for replace the word" dlg.AllowMultiSelect = False If dlg.Show = -1 Then dataPath = dlg.SelectedItems(1) End If Set wdDoc = wdApp.Documents.Open(dataPath, AddToRecentFiles:=False) wdApp.Visible = True strSearch = Cells(r, 1).Value While strSearch <> "" strReplace = Cells(r, 2).Value iCount = 0 wdApp.Options.DefaultHighlightColorIndex = wdYellow With wdDoc.Content.Find .Text = strSearch .Replacement.Text = strReplace .Replacement.Highlight = True .Wrap = wdFindContinue .Execute Replace:=wdReplaceAll End With strSearch = wdDoc.Range.Text iCount = (Len(strSearch) - Len(Replace(strSearch, strReplace, ""))) / Len(strReplace) If iCount > 1 Then wdApp.Options.DefaultHighlightColorIndex = wdRed With wdDoc.Content.Find .Text = strReplace .Replacement.Text = strReplace .Replacement.Highlight = True .Wrap = wdFindStop .Execute Replace:=wdReplaceOne End With End If r = r + 1 strSearch = Cells(r, 1).Value Wend MsgBox "Done" End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks macropod.... It's really great code... it works for me......
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I get excel to lock each value that is already in the cell l so I can add it | iasdfs | Excel | 1 | 12-06-2011 12:52 PM |
Can excel Replace metadata of image files? | quiff | Excel | 0 | 11-23-2011 12:39 AM |
Excel 2007 replace | Jim WV | Excel | 2 | 07-17-2011 01:44 AM |
Auto-populate an MS Word table cell with text from a diff cell? | dreamrthts | Word Tables | 0 | 03-20-2009 01:49 PM |
paste formated text from Word to only one Excel cell | heron | Word | 1 | 12-06-2005 02:37 AM |