CODEWITHSUNDEEP
excelvbams-word
ShinyRhino
ShinyRhino

Reputation: 1

Replace string in column in MS Excel from MS Word Macro

I am trying to replace strings in a Excel column with a sub routine in an MS Word VBA macro, but I get the following error: "Compile error: Method or data member not found."

I was trying to modify this MS Word Macro to replace the values in the "Done" column ("TRUE" -> "Resolved", "FALSE" -> "Open") since I find the TRUE/FALSE values too technical. I have recorded a Macro as a Sub routine in Excel to do that but I don't know how to fit it into the code because it requires interactive communication between MS Word and MS Excel. If I just add Call RelpaceInDone in the macro I get the following error: "Compile error: Method or data member not found."

Sub ReplaceInDone()
'
' ReplaceInDone Macro
'
Columns("G:G").Select
Selection.Replace What:="TRUE", Replacement:="Resolved", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.Replace What:="FALSE", Replacement:="Open", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
    Repl`aceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub`

Any advice?

Upvotes: 0

Views: 79

Answers (1)

taller
taller

Reputation: 18778

Step1: Get / create Excel Application.

Step2: Get / open Excel workbook.

Step3: Replace data in column G, all Excel const should be replace with integer

Let's assume the Excel file named Data.xlsx is stored in the same directory as the Word document.

Sub ReplaceInDone()
    ' Word VBA code
    Dim xlApp As Object, xlWK As Object
    Dim oRange As Object, sPath
    sPath = ThisDocument.Path & "\"
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        Set xlWK = xlApp.Workbooks.Open(sPath & "Data.xlsx")
    Else
        On Error Resume Next
        Set xlWK = xlApp.Workbooks(sPath & "Data.xlsx")
        On Error GoTo 0
        If xlWK Is Nothing Then Set xlWK = xlApp.Workbooks.Open("Data.xlsx")
    End If
    Set oRange = xlApp.Activesheet.Range("G:G")
    oRange.Replace What:="TRUE", Replacement:="Resolved", LookAt:=2, _
        SearchOrder:=2, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=1
    oRange.Replace What:="FALSE", Replacement:="Open", LookAt:=2, _
        SearchOrder:=2, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=1
    xlWK.Save
    xlWK.Close
    xlApp.Quit
End Sub

Upvotes: 0

Related Questions