VBA Word macro goes to breakmode

1
votes

I'm trying to open two documents from excel with vba and call a word macro from this particular excel file.

The macro is working fine in Word and I also get the documents to open and the word macro to start. However when there is a switch from one document to the other the word macro goes to break-mode (which does not happen when I run it from Word instead of Excel).

I use the following code from excel:

Set wordApp = CreateObject("Word.Application")
worddoc = "H:\Word Dummy's\Dummy.docm"
wordApp.Documents.Open worddoc
wordApp.Visible = True

wordApp.Run macroname:="update_dummy", varg1:=client, varg2:=m_ultimo, varg3:=y

In word I have a sub with the parameters defined between breakets and the following code:

worddoc2 = "H:\Word Dummy's\texts.docx"

Word.Application.Activate
Documents.Open worddoc2, ReadOnly:=True
ThisDocument.Activate
Set bmks = ThisDocument.Bookmarks

Can anyone tell me why it does not run from excel and how I can fix this?

Thanks in advance.

3
vbaexcelms-word
where is the break happening? In excel or in word after opening from excel? – ghostJago
In word after opening worddoc2 and going to thisdocument.activate – Tessa De Jonge
please show your complete code that makes it easier to help you... – Kazimierz Jawor
From your code, it seems that you don't need the ThisDocument.Activate. What happens if you comment that line out? – MP24
Still the same error. I also tried to add EnableEvents=true and documents(worddoc).activate instead of thisdocument.activate but i keep getting the error: can't execute in breakmode. Think it is because the excelmacro goes to breakmode when starting the wordmacro but i don't know how to solve this. – Tessa De Jonge

3 Answers

1
votes

I finally found the answer myself after a lot of searching on Google.
I needed to add :

application.EnableEvents=false

To the excel macro.

That was all. Now it works.

0
votes

My complete code is huge (the macro in excel also opens two other workbooks and runs a macro in them). This part of the code is working for now (so I left it out), but I just want to add the part that it opens a worddoc and adds specific texts in it depending on what client has been chosen in the excel userform. But to show you a better idea how my code looks like, this is in excel (where the client is defined by a userform in another module):

Sub open_models (client as string)

Application.DisplayStatusBar = True

‘determine datatypes
Dim m_integer As Integer
Dim m_ultimo As String
Dim m_primo As String

Dim y As String
Dim y_integer As Integer
Dim y_old As String
Dim y_last As String

Dim wordApp As Object
Dim worddoc As String

'Determine current month and year and previous
m_integer = Format(Now, "mm")
y_integer = Format(Now, "yyyy")

If m_integer <= 9 Then
    m_ultimo = "0" & m_integer - 1
    m_primo = "0" & m_integer - 2
 Else
    m_ultimo = m_integer - 1
    m_primo = m_integer - 2
End If

If m_integer = 1 Then
    y = y_integer - 1
  Else
    y = y_integer
End If

On Error Resume Next

'open word dummy
Set wordApp = CreateObject("Word.Application")
worddoc = "H:\RAPORTAG\" & y & "\" & y & m_ultimo & "\Dummy.docm"

wordApp.Documents.Open worddoc
wordApp.Visible = True

wordApp.Run macroname:="update_dummy", varg1:=client, varg2:=m_ultimo, varg3:=y, varg4:= worddoc)

On Error GoTo 0

ThisWorkbook.Activate

'reset statusbar and close this workbook
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

ThisWorkbook.Close False

End Sub

  And this is the code in word I am using:

Sub update_dummy(client As String, m_ultimo As String, y As String, worddoc as string)

Dim wordapp As Object
Dim rngStart As Range
Dim rngEnd As Range
Dim worddoc As String
Dim worddoc2 As String
Dim dekkingsgraad As String
Dim bmks As Bookmarks
Dim bmRange As Range
Dim rng As Range
Dim i As Boolean

On Error Resume Next

worddoc2 = "H:\RAPORTAG\" & y & "\" & y & m_ultimo & "\dummytexts.docx"

'open other word
Documents.Open worddoc2, ReadOnly:=True
Documents(worddoc).Activate
Set bmks = Documents(worddoc).Bookmarks

'management summary
If client <> "PMT" Then
i = True
Set rngStart = Documents(worddoc2).Bookmarks("bn0_1_start").Range
Set rngEnd = Documents(worddoc2).Bookmarks("bn0_1_end").Range
End If

If i = True Then
    Set rng = Documents(worddoc2).Range(rngStart.Start, rngEnd.End)
    rng.Copy

    Set bmRange = Documents(worddoc).Bookmarks("bmManagementsummary").Range
    bmRange.PasteAndFormat (wdPasteDefault)
End If

i = False

On Error GoTo 0

End Sub

I have 20 more bookmarks that are defined but the code for them is all the same.

0
votes

I have seen and solved this problem a few times before, the solution I found was odd.

  1. Copy paste all your code into a text editor, 1 for word, 1 for excel

  2. Delete all the macros in word or excel or better yet, just create new files.

  3. Paste all the code into word/excel from your text editor.

I've definitely had this 3 or 4 times in Excel and Access. Especially if you previously had a breakpoint at that location.

It sounds stupid but try it and see if that works, this has saved me from insanity a few times.