Converting an Excel macro to run from Word

[lebowski]

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?

[lebowski]

Can anyone advise on how to get the following Excel VBA code to run from a Word macro?


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

[Yongle]

This works for me. But you may need to modify it if you are not on Excel 2010.
Suggest you test this as follows, just to prove it will work for you before trying it out on your proper data.
If you name all the macros and the files as per my suggestions, it will make it easier for me to help if, for any reason, this fails to work for you.

1 Create a test Excel file with one simple macro in it like:


Sub ExcelMacro()
Range("A1").Value = Range("A1").Value + 1
ActiveWorkbook.Save
End Sub


2 Save the file as macro enabled with name ExcelFile.xlsm
3 Close the file and then open it again enabling macros (should only need to do this once - depending on your settings you may not even need to do it at all)
4 Add the following macro which it will run automatically the file opens next time

Private Sub Workbook_Open()
Dim answer As Integer
answer = MsgBox("Run Macro from Word?", vbYesNo + vbQuestion, "You can say NO")
If answer = vbYes Then
Application.Run "ExcelMacro"
Else
'do nothing
End If
End Sub
This image shows you where it needs to go.
13143
5 Save the file and see a message box pop up when you open it again. Click on yes to run the macro.
6 Close the file
7 Create test Word File (saving it as a macro enabled document with name WordFile) and create the following macro - amending the Path to where Excel.exe is on your system and File to match the folder that ExcelFile.xlsm was saved to


Sub WordMacro()
Dim x As Variant
Dim Path As String
Dim File As String
Path = "C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"
File = "D:\Documents\VBAForum\ExcelFile.xlsm"
x = Shell(Path + " " + File, vbNormalFocus)
End Sub
8 Run the macro and let me know if it worked or not and then I will help you make the real macro work.