If statement from excel scores to change text in word with VBA

Question

I have different scores in an excel sheet directly extracted from a database that have certain values. I want to extract these scores from excel and be able to change texts in a word document with if statements (e.g. if score in cell (2, 2) > 60 (in excel) = delete/replace a certain text (in word)) with VBA. Does anyone know how I would compile this macro?

The code I tried:

Private Sub CommandButton8_Click()

Dim objExcel As New Excel.Application

Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("C:\Desktop\Testdoc.xlsx")

If score.Cells(2, 2) >= 60 Then

Function FnOpeneWordDoc()

   Dim objWord

   Dim objDoc

   Set objWord = CreateObject("Word.Application")

   Set objDoc = objWord.Documents.Open("C:\Desktop\TestDoc.docm")

   objWord.Visible = True

Selection.Find.ClearFormatting

Selection.Find.Replacement.ClearFormatting

    With Selection.Find
    .Text = "text here"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With

End Function

End If

exWb.Close

Set exWb = Nothing

End Function

Edit: New code:

Private Sub CommandButton7_Click()

Dim objExcel As New Excel.Application

Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("C:\UsersDesktop\Testdoc.xlsx")

If exWb.Sheets(1).Cells(2, 2).Value >= 60 Then Run [BLA]

If Not exWb.Sheets(1).Cells(2, 2).Value >= 60 Then Run [BLA2]

End If

exWb.Close

Set exWb = Nothing

End Sub

Function BLA()
With Selection.Find
.ClearFormatting
.Text = "Old text"
.Replacement.ClearFormatting
.Replacement.Text = ""
.Execute Replace:=wdReplaceAll, Forward:=True, _
    Wrap:=wdFindContinue

End With

End Function

Function BLA2()
With Selection.Find
.ClearFormatting
.Text = "Old text"
.Replacement.ClearFormatting
.Replacement.Text = "Old text"
.Execute Replace:=wdReplaceAll, Forward:=True, _
    Wrap:=wdFindContinue

End With

End Function

I hope this makes it a little more clear...I don't know how to do it right. — Arco Jansen
please explain what is not working, what and where? any errors (number and description)? — Kazimierz Jawor
Also, running the code line by line using "F8" or other debug options (like breakpoints) should help you understand what is going on and exactly where it is throwing errors. — simpLE MAn

Olle Sjögren Olle Sjögren · Accepted Answer · 2014-01-21T15:45:00

Your question is very hard to understand, since your code does not make sense. First, you have a function defined inside a sub. That will never compile. You need to separate the functions or subs.

Then you seem to end the sub with End Function - will also cause an error. By the way, functions return values, use Subs if you don't want to return something.

Finally, you haven't mentioned how and where you will run the code. FnOpeneWordDoc opens a Word docm by first opening Word, CommandButton8_Click opens an Excel xlsx by first opening Excel. Either you run it from Excel, then you only need to open Word since Excel is already open, or the other way around. Try the following from Excel. I don't know what you are trying to do in Word, so I commented out the code that does not compile. I hope it will set you off running in the right direction.

Option Explicit

Private Sub CommandButton8_Click()
    Dim exWb As Workbook

    Set exWb = Workbooks.Open("C:\Desktop\Testdoc.xlsx")

    If exWb.Sheets(1).Cells(2, 2).Value >= 60 Then
        Call FnOpeneWordDoc
    End If

    exWb.Close

    Set exWb = Nothing
End Sub


Sub FnOpeneWordDoc()
    Dim objWord

    Dim objDoc

    Set objWord = CreateObject("Word.Application")

    Set objDoc = objWord.Documents.Open("C:\Desktop\TestDoc.docm")

    objWord.Visible = True


    'Selection.Find.ClearFormatting

    'Selection.Find.Replacement.ClearFormatting

    'With Selection.Find
    '    .Text = "text here"
    '    .Replacement.Text = ""
    '    .Forward = True
    '    .Wrap = wdFindContinue
    '    .Format = False
    '    .MatchCase = False
    '    .MatchWholeWord = False
    '    .MatchWildcards = False
    '    .MatchSoundsLike = False
    '    .MatchAllWordForms = False
    'End With

    Set objDoc = Nothing
    Set objWord = Nothing

End Sub

EDIT: Answer to comment - sure, that's what the If exWb.Sheets(1).Cells(2, 2).Value >= 60 Then line does. If the value in B2 is equal to or greater than 60, then run FnOpeneWordDoc.

Bonus tip: you can evaluate the conditions by using the immediate window (CTRL+G) in the VBA environment by typing a question mark ? and then the condition:

?exWb.Sheets(1).Cells(2, 2).Value >= 60

will return True or False.

EDIT #2: You have two If-statements where one be better. Also, the second If statement is not correct. You either have If True Then [Command] or have the [Command] on the line below with an End If after - you mix the two.

Try the following basic code, then add the details later - remove everything else if possible).

Option Explicit

Private Sub CommandButton8_Click()
    '***** Check to see if something
    '***** is bigger than something else
    If 61 >= 60 Then
        '***** Run sub FnOpeneWordDoc
        Call FnOpeneWordDoc
    Else
        '***** Do nothing
    End If
End Sub


Sub FnOpeneWordDoc()
    '***** Do something cool
End Sub

EDIT #3: Your If statements actually work if you use the correct syntax:

'***** Use either one line without "End If"...
If exWb.Sheets(1).Cells(2, 2).Value >= 60 Then Run [BLA]

'***** ...or this syntax, where you must use "End If"
If Not exWb.Sheets(1).Cells(2, 2).Value >= 60 Then
    Run [BLA2]
    '***** You can add more lines here if you want
End If

If statement from excel scores to change text in word with VBA

1 Answers