macro+word 2003 version support

nancy v · #16 03-29-2012, 04:21 PM

I think I am making you confused here

, somewhere ,

Excel file I am using so that I can organize all related words in one place, and then as I used t search google and find there respective code or values I put next to respective words.

Now from here it becomes easy while working with word file editing to just copy the word from word doc and open excel first find where it is, copy the respective against value, and replace in word if it is desired, else find next and replace....
Copy the next word and search again......

This is work around I do for every word....

macropod · #17 03-29-2012, 04:42 PM

Hi Nancy,

If you make the links, then save the file as a template, then all you'll need to do is to when you want an updated document is to create one based on the template, then press Ctrl-A, F9, Ctrl-Shift-F9 to update all the links in the document, then break the links and turn the results into plain text. You can then save the document. That is much more efficient than using a macro.

As I said regarding the macro, it would need some changes to suit your needs. Since you seem to have your heart set on using a macro, try:

Code:

Sub BulkFindReplace()
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, Rslt
Dim iDataRow As Long, xlFList As String, xlRList As String, i As Long
Const StrWkSht As String = "Sheet1"
StrWkBkNm = "C:\Users\Username\Documents\Workbook Name.xls"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Can't start Excel.", vbExclamation
  Exit Sub
End If
On Error GoTo 0
With xlApp
  'Hide our Excel session
  .Visible = False
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMru:=False)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    .Quit
    Exit Sub
  End If
  ' Process the workbook.
  With xlWkBk
    'Ensure the worksheet exists
    If SheetExists(xlWkBk, StrWkSht) = True Then
      With .Worksheets(StrWkSht)
        ' Find the last-used row in column A.
        iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
        ' Capture the F/R data.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("A" & i)) <> vbNullString Then
            xlFList = xlFList & "|" & Trim(.Range("A" & i))
            xlRList = xlRList & "|" & Trim(.Range("B" & i))
          End If
        Next
      End With
    Else
      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
    End If
  .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFList = "" Then Exit Sub 
'Process each word from the List
For i = 1 To UBound(Split(xlFList, "|"))
  With ActiveDocument.Range
    With .Find
      .Text = Split(xlFList, "|")(i)
      .ClearFormatting
      .Replacement.ClearFormatting
      .MatchWholeWord = True
      .MatchCase = True
      .Wrap = wdFindStop
      .Execute
    End With
    'Replace the found text, asking first
    Do While .Find.Found
      .Duplicate.Select
      Rslt = MsgBox("Replace this instance of:" & vbCr & _
        Split(xlFList, "|")(i) & vbCr & "with:" & vbCr & _
        Split(xlRList, "|")(i), vbYesNoCancel)
      If Rslt = vbCancel Then Exit Sub
      If Rslt = vbYes Then .Text = Split(xlRList, "|")(i)
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
End Sub
 
Function SheetExists(xlWkBk As Object, SheetName As String) As Boolean
Dim i As Long: SheetExists = False
With xlWkBk
  For i = 1 To .Sheets.Count
    If .Sheets(i).Name = SheetName Then
      SheetExists = True:   Exit For
    End If
  Next
End With
End Function

You will need to change the StrWkBkNm and StrWkSht variables to point to your own workbook and worksheet, respectively.

nancy v · #18 03-29-2012, 04:44 PM

love you dear

thanks,
let me try this out will come to you back

nancy v · #19 03-29-2012, 05:41 PM

You guys are god gifted talent,
for my knowledge could you spare some time t explain here as what we did, in prgm....
so that instead of just using codes I get something to discover new things

also it will help me in putting my queries in a organised way next time, and dont waste your time too much, straight to point query

I was roaming here and there first...to explain...

is there any email id to directly mail to you or I need to again visit here every time....

Thanks,

Nancy

macropod · #20 03-29-2012, 07:19 PM

Quote:

Originally Posted by nancy v

for my knowledge could you spare some time t explain here as what we did, in prgm....
so that instead of just using codes I get something to discover new things

is there any email id to directly mail to you or I need to again visit here every time....

There are already comments in the code to explain what it does.

I don't accept private help requests. If you have further issues you need help with, please post them in the public forum, where others can contribute their expertise and/or benefit from whatever solutions are found.

nancy v · #21 03-29-2012, 07:51 PM

Ok that is good thing I forgot, other like me may get benefit from this how selfish I was, sorry for that, ok I will post here only, need to copy this web site link for further use.

Thanks, Paul

nancy v · #22 03-30-2012, 03:12 PM

Dear Paul,

hope you are doing good,

here I have a query as in

Code:

'Replace the found text, asking first
    Do While .Find.Found
      .Duplicate.Select
      Rslt = MsgBox("Replace this instance of:" & vbCr & _
        Split(xlFList, "|")(i) & vbCr & "with:" & vbCr & _
        Split(xlRList, "|")(i), vbYesNoCancel)
      If Rslt = vbCancel Then Exit Sub
      If Rslt = vbYes Then .Text = Split(xlRList, "|")(i)
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With

what is to be changed so that it just replace in one go ......not asking first etc...
as last night I was working on a doc fie and it had around 300+ same text which need to replaced, and I was ding one by one using macro.

Thanks,

Nancy

macropod · #23 03-30-2012, 03:21 PM

Hi Nancy,

You could use:

Code:

'Replace the found text, without asking first
  Do While .Find.Found
    .Text = Split(xlRList, "|")(i)
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With

Even more efficient would be to replace the whole For ... Next structure with:

Code:

'Process each word from the List
Application.ScreenUpdating = False
For i = 1 To UBound(Split(xlFList, "|"))
  With ActiveDocument.Range.Find
    .Text = Split(xlFList, "|")(i)
    .Replacement.Text = Split(xlRList, "|")(i)
    .ClearFormatting
    .Replacement.ClearFormatting
    .MatchWholeWord = True
    .MatchCase = True
    .Wrap = wdFindStop
    .Execute Replace:=wdReplaceAll
  End With
Next
Application.ScreenUpdating = True

nancy v · #24 03-31-2012, 12:08 AM

Hi Paul,

In continuation with past codes you suggest,

I need to find as per excel if the very first value got repeated next time, or next as many times.
iF YES we get a message(in bold letters so that I dont have to use my glasses

) along with usual YES NO CANCEL

I tried a paint shop example to explain better to you, also we are using here post 17 program as suggested by you for this. i.e.

Code:

Sub BulkFindReplace()
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, Rslt
Dim iDataRow As Long, xlFList As String, xlRList As String, i As Long
Const StrWkSht As String = "Sheet1"
StrWkBkNm = "C:\Users\Username\Documents\Workbook Name.xls"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Can't start Excel.", vbExclamation
  Exit Sub
End If
On Error GoTo 0
With xlApp
  'Hide our Excel session
  .Visible = False
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMru:=False)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    .Quit
    Exit Sub
  End If
  ' Process the workbook.
  With xlWkBk
    'Ensure the worksheet exists
    If SheetExists(xlWkBk, StrWkSht) = True Then
      With .Worksheets(StrWkSht)
        ' Find the last-used row in column A.
        iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
        ' Capture the F/R data.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("A" & i)) <> vbNullString Then
            xlFList = xlFList & "|" & Trim(.Range("A" & i))
            xlRList = xlRList & "|" & Trim(.Range("B" & i))
          End If
        Next
      End With
    Else
      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
    End If
  .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFList = "" Then Exit Sub 
'Process each word from the List
For i = 1 To UBound(Split(xlFList, "|"))
  With ActiveDocument.Range
    With .Find
      .Text = Split(xlFList, "|")(i)
      .ClearFormatting
      .Replacement.ClearFormatting
      .MatchWholeWord = True
      .MatchCase = True
      .Wrap = wdFindStop
      .Execute
    End With
    'Replace the found text, asking first
    Do While .Find.Found
      .Duplicate.Select
      Rslt = MsgBox("Replace this instance of:" & vbCr & _
        Split(xlFList, "|")(i) & vbCr & "with:" & vbCr & _
        Split(xlRList, "|")(i), vbYesNoCancel)
      If Rslt = vbCancel Then Exit Sub
      If Rslt = vbYes Then .Text = Split(xlRList, "|")(i)
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
End Sub
 
Function SheetExists(xlWkBk As Object, SheetName As String) As Boolean
Dim i As Long: SheetExists = False
With xlWkBk
  For i = 1 To .Sheets.Count
    If .Sheets(i).Name = SheetName Then
      SheetExists = True:   Exit For
    End If
  Next
End With
End Function

If I get this it will help in find them and so that I will put another reference names to them.

Note: picture reads as Replace this instance of: John 98 with 23 se 34............(Repeated value) i.e find value John 98 is repeated here, that is why message should say (Repeated value)

Thanks to my son who takes much interest in your code,

Nancy

nancy v · #25 03-31-2012, 12:10 AM

sorry picture didnt got attached.... here it is

macropod · #26 03-31-2012, 05:33 AM

Hi Nancy,

It is not possible to make any part of the message bold, underlined or italics.

nancy v · #27 03-31-2012, 05:51 AM

ok but can we get that message if searched word found very next time, or apart from very first every same word shows this message, that this is repeatation along with YES NO CANCEL?

nancy v · #28 03-31-2012, 09:25 AM

Dear Paul,

If this is possible then ok else leave it we will see it some other time or just leave it.
I will manage this here,

Here people already started preparing for next year carnival, 2013, so most of time people in prayer hall etc keep discussing what will come next, do come to my country...

Thanks,

Nancy

nancy v · #29 03-31-2012, 01:48 PM

we need just message nothing more, if it comes as duplicate or repeated value...

macropod · #30 03-31-2012, 02:27 PM

Hi Nancy,

That seems like over-compensating for people who aren't paying attention to what they're doing. Try:

Code:

Sub BulkFindReplace()
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, Rslt
Dim iDataRow As Long, xlFList As String, xlRList As String, i As Long
Dim StrRpt As String
Const StrWkSht As String = "Sheet1"
StrWkBkNm = "C:\Users\Username\Documents\Workbook Name.xls"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Can't start Excel.", vbExclamation
  Exit Sub
End If
On Error GoTo 0
With xlApp
  'Hide our Excel session
  .Visible = False
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMru:=False)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    .Quit
    Exit Sub
  End If
  ' Process the workbook.
  With xlWkBk
    'Ensure the worksheet exists
    If SheetExists(xlWkBk, StrWkSht) = True Then
      With .Worksheets(StrWkSht)
        ' Find the last-used row in column A.
        iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
        ' Capture the F/R data.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("A" & i)) <> vbNullString Then
            xlFList = xlFList & "|" & Trim(.Range("A" & i))
            xlRList = xlRList & "|" & Trim(.Range("B" & i))
          End If
        Next
      End With
    Else
      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
    End If
  .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFList = "" Then Exit Sub 
'Process each word from the List
For i = 1 To UBound(Split(xlFList, "|"))
  StrRpt = ""
  With ActiveDocument.Range
    With .Find
      .Text = Split(xlFList, "|")(i)
      .ClearFormatting
      .Replacement.ClearFormatting
      .MatchWholeWord = True
      .MatchCase = True
      .Wrap = wdFindStop
      .Execute
    End With
    'Replace the found text, asking first
    Do While .Find.Found
      .Duplicate.Select
      Rslt = MsgBox("Replace this instance of:" & vbCr & _
        Split(xlFList, "|")(i) & vbCr & "with:" & vbCr & _
        Split(xlRList, "|")(i), vbYesNoCancel) & StrRpt
      If Rslt = vbCancel Then Exit Sub
      If Rslt = vbYes Then .Text = Split(xlRList, "|")(i)
      StrRpt = vbTab & "(Repeat)"
      .Collapse wdCollapseEnd
      .Find.Execute
    Loop
  End With
Next
End Sub
 
Function SheetExists(xlWkBk As Object, SheetName As String) As Boolean
Dim i As Long: SheetExists = False
With xlWkBk
  For i = 1 To .Sheets.Count
    If .Sheets(i).Name = SheetName Then
      SheetExists = True:   Exit For
    End If
  Next
End With
End Function

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can Word 2007 support graphs?	burnsie	Drawing and Graphics	1	07-08-2011 07:08 AM
Word 2003 macro to Word 2007 to 2010 to...	maruapo	Word VBA	0	06-04-2010 03:43 PM
does office word 2007 support html signature!	jimijani1982	Word	2	05-04-2010 04:37 AM
[Word 2003] Macro's and a UserForm	xanuex	Word VBA	0	10-19-2009 05:42 AM
Problem with AutoClose macro and Word 2003	cholo	Word VBA	0	07-08-2009 05:48 AM