Results 1 to 3 of 3

Thread: Converting an Excel macro to run from Word

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Oct 2014
    Posts
    18
    Location

    Converting an Excel macro to run from Word

    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?
    Last edited by lebowski; 04-09-2015 at 07:57 AM. Reason: Further clarity

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •