Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2012, 04:29 PM
macropod's Avatar
macropod macropod is offline How to Replace Excel Cell Value in MS word by VBA.... Windows 7 64bit How to Replace Excel Cell Value in MS word by VBA.... Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,382
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply



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
How to Replace Excel Cell Value in MS word by VBA.... paste formated text from Word to only one Excel cell heron Word 1 12-06-2005 02:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft