lebowski
04-09-2015, 07:43 AM
I've had help on another thread creating the code below to change specific text to bold in Excel.
Dim rCell As Range, sToFind As String, iSeek As Long
Dim Text(1 To 2) As String
Dim i As Integer
'Find search string within text
Text(1) = "Apples
Text(2) = "Oranges"
For i = LBound(Text) To UBound(Text)
For Each rCell In Range("A1:A500")
sToFind = Text(i)
iSeek = InStr(1, rCell.Value, sToFind)
Do While iSeek > 0
rCell.Characters(iSeek, Len(sToFind)).Font.Bold = True
iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
Loop
Next rCell
Next i
I now need to run this code from Word. I have binding set-up to Excel from Word with the following code:
Dim XL_App As Excel.Application
Dim XL_WrkBk As Excel.Workbook
Dim XL_WrkSht As Excel.Worksheet
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'Make file dialog visible
'Change display name open file dialog
Application.FileDialog(msoFileDialogOpen).Title = _
"Open Excel file"
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'Determine user choice
If intChoice <> 0 Then
'Store selected file path
strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
Else
'User cancelled file selection
End
End If
Set XL_App = New Excel.Application
Set XL_WrkBk = XL_App.Workbooks.Open(strPath)
XL_App.Visible = True
XL_App.WindowState = xlMinimized
I thought I might be able to prefix "Range" and "rCell" with "XL_App.Sheets(1)." with the following but it is clearly not as straightforward as that.
Can anyone please advise on modifying the code to make text bold in order to function from Word?
Dim rCell As Range, sToFind As String, iSeek As Long
Dim Text(1 To 2) As String
Dim i As Integer
'Find search string within text
Text(1) = "Apples
Text(2) = "Oranges"
For i = LBound(Text) To UBound(Text)
For Each rCell In Range("A1:A500")
sToFind = Text(i)
iSeek = InStr(1, rCell.Value, sToFind)
Do While iSeek > 0
rCell.Characters(iSeek, Len(sToFind)).Font.Bold = True
iSeek = InStr(iSeek + 1, rCell.Value, sToFind)
Loop
Next rCell
Next i
I now need to run this code from Word. I have binding set-up to Excel from Word with the following code:
Dim XL_App As Excel.Application
Dim XL_WrkBk As Excel.Workbook
Dim XL_WrkSht As Excel.Worksheet
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'Make file dialog visible
'Change display name open file dialog
Application.FileDialog(msoFileDialogOpen).Title = _
"Open Excel file"
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'Determine user choice
If intChoice <> 0 Then
'Store selected file path
strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
Else
'User cancelled file selection
End
End If
Set XL_App = New Excel.Application
Set XL_WrkBk = XL_App.Workbooks.Open(strPath)
XL_App.Visible = True
XL_App.WindowState = xlMinimized
I thought I might be able to prefix "Range" and "rCell" with "XL_App.Sheets(1)." with the following but it is clearly not as straightforward as that.
Can anyone please advise on modifying the code to make text bold in order to function from Word?