none
VBA Search and Replace - XL Source RRS feed

  • Question

  • Hi friends,

    I am having problems pointing to an excel sheet.

    The Excel sheet holds my find \ Replace Strings

    this is what I have so far


    Sub SearchReplace()

    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object

    Dim i As Long
    Dim oFind As String
    Dim oReplace As String
        
        
    strworkbook = "C:\Users\PLW\Desktop\Replace.xlsx"
        
    Set xlbook = xlapp.Workbooks.Open(strworkbook)
    Set xlsheet = xlbook.Worksheets(1)

    With xlsheet.Range("A1")
        For i = 2 To .CurrentRegion.Rows.Count
            oFind = .Offset(i - 1, 0)            ' Column A
            oReplace = .Offset(i - 1, 1)     ' Column B
       
        
        With ActiveDocument.Content.Find
            .Text = oFind
            .Replacement.Text = oReplace
            .Wrap = wdFindContinue
            .Execute Replace:=wdReplaceAll
        End With
        'Set wdApp = Nothing: Set wdDoc = Nothing
        
        Next
        End With
    End Sub


    What am I missing?

    thank you
    Friday, May 27, 2016 5:18 PM

Answers

  • Prior to opening the Excel workbook, you'll need to create an instance of Excel and assign it to your object variable "xlapp"...

    Set xlapp = CreateObject("Excel.Application")

    Then, before the sub ends, you can close your wokbook and quit Excel...

    xlbook.close savechanges:=false
    xlapp.quit

    And, lastly, you can clear your objects from memory...

    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlSheet = Nothing

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Dan_CS Sunday, May 29, 2016 11:58 AM
    Sunday, May 29, 2016 2:27 AM

All replies

  • Prior to opening the Excel workbook, you'll need to create an instance of Excel and assign it to your object variable "xlapp"...

    Set xlapp = CreateObject("Excel.Application")

    Then, before the sub ends, you can close your wokbook and quit Excel...

    xlbook.close savechanges:=false
    xlapp.quit

    And, lastly, you can clear your objects from memory...

    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlSheet = Nothing

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Dan_CS Sunday, May 29, 2016 11:58 AM
    Sunday, May 29, 2016 2:27 AM
  • Hello,

    thank you that solved the problem,

    I also found another solution here

    https://social.msdn.microsoft.com/Forums/en-US/036db2ef-25c9-4c8c-85bd-03084519540f/find-and-replace-in-word-from-a-table-in-excel?forum=isvvba

    Sunday, May 29, 2016 11:58 AM