Office Automation: Excel executng Word VBA

[mehunter]

First of all, Kudos to mana for invaluable assistance that he provided in helping me to solve my last post!

When you are in Excel VBA executing Word VBA (such as a Find & Replace) on large Word file, the Excel VBA does not know when the Word VBA has finished and can continue to next VBA instruction without allowing the last instruction to complete. I inserted wait(1 second) into my code and that solves the problem, but looking for more elegant solution.

Is there a way to monitor the Word processing to know when it is complete before proceeding with the next Excel directed Word instruction?

[Dave]

When you are in Excel VBA executing Word VBA (such as a Find & Replace) on large Word file, the Excel VBA does not know when the Word VBA has finished and can continue to next VBA instruction without allowing the last instruction to complete.

I don't agree with/understand the premise of your question. Please post the relevant code. Adding a second to the code to correct the problem seems miasmic. The way to monitor the Word processing is that when Word is done its' stuff it returns control to the next line of XL VBA code. HTH. Dave

[mehunter]

Unfortunately, since the nature of the problem requires a large Word document in order to cause the affect, posting a code segment is not practical. Imagine the code snippet that I've provided below, but expanded to 57 pages; My Word document has 57 pages.

I'm doing simple find & replace VBA such as in the following code example (credits to mana):

Sub test()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim rng As Object
    Dim s As String
     
    Application.ScreenUpdating = False
     
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Add 'Open new Word document
    wdApp.Visible = True
     
    s = vbCr & "New text Line 1"
    s = s & vbCr & "New text Line 2"
    s = s & vbCr & "New text Line 3"
    s = s & vbCr & "!# A. New Indented Text Line 1"
    s = s & vbCr & "!## B. New Indented Text Line 2"
    s = s & vbCr & "!# C. New Indented Text Line 3"
     
    Set rng = wdDoc.Range
     
    rng.Text = s
     
    With rng.Find
        .MatchWildcards = True
        .Style = wdDoc.styles("Normal")
        .Replacement.ParagraphFormat.IndentCharWidth 1
        .Replacement.Style = wdDoc.styles("List Paragraph")
         
        .Execute FindText:="\!# (*)", ReplaceWith:="\1", _
        Format:=True, Replace:=2 'wdReplaceAll

        'Works on large document if you pause here for the Word instance to complete.
         
        .Replacement.ParagraphFormat.IndentCharWidth 2
         
        .Execute FindText:="\!## (*)", ReplaceWith:="\1", _
        Format:=True, Replace:=2 'wdReplaceAll
         
    End With

    ' And pause again here if there are instructions to follow.
     
End Sub

Sample code works fine when operated on the larger document (57 pages), and as indicated in the comments, works if you either halt at breakpoint or insert large wait.

I'm thinking that a semaphore might be needed to coordinate the operation of Word execution with Excel execution.

[mehunter]

Further, in my application, the VBA code works perfectly when executed (with obvious differences due to the wdApp & wdDoc coding) as Word macro.

It does take several seconds to conduct a Find and Replace when executed as a Word macro.

Execution of subsequent steps when executed from Excel VBA on Word instance, is instanteous, which leads me to believe that the problem could be independent processes.

[mehunter]

Refer to my clarifications posted below.

[mehunter]

Dave,
Do you know for a fact that Excel waits for completion of Word Instance execution; my experiments appear otherwise.

To duplicate my findings, use large (50 page) document that you have on hand. Document should be all in style "Normal", and should have indents like in the sample code above, also in style "Normal".

I used the following code to pre-process the Word document, adding the appropriate prepended tags, "!# " for indented "A., B., C., ..." paragraphs, and "!## " for indented "1., 2., 3., ..." paragraphs. I haven't currently encountered further indents but coding would follow suit.

 

Sub ConvertList()
    Dim wdDoc As Object
    Dim rng As Object
    
    Application.ScreenUpdating = False
     
    Set wdDoc = wordDoc(0)
    wdApp.Visible = True
     
    Set rng = wdDoc.Range
    
    With rng.Find
        .MatchWildcards = True
        .Style = wdDoc.styles("Normal")
        .Replacement.ParagraphFormat.IndentCharWidth 1
        .Replacement.Style = wdDoc.styles("List Paragraph")
         
        .Execute FindText:="\!# (*)", ReplaceWith:="\1", _
        Format:=True, Replace:=wdReplaceAll
        
        'Have to allow enough time for the remote operation to finish
        wait 10000000
         
        .Replacement.ParagraphFormat.IndentCharWidth 2
         
        .Execute FindText:="\!## (*)", ReplaceWith:="\1", _
        Format:=True, Replace:=wdReplaceAll
         
        'Have to allow enough time for the remote operation to finish
        wait 10000000
                  
    End With
    
End Sub


Sub wait(count As Long)
    Dim i As Long
    For i = 1 To count
        result = result + i
        i = i
    Next
 End Sub

[snb]

This code suffices

Sub M_snb()
  With CreateObject("Word.document")
    .Content = Replace("New text Line 1~New text Line 2~New text Line 3~# A. New Indented Text Line 1~## B. New Indented Text Line 2~# C. New Indented Text Line 3", "~", vbCr)
    With .Content.Find
      .Replacement.ParagraphFormat.IndentCharWidth 2
      .Replacement.Style = .Parent.Parent.Styles("List Paragraph")
      .Execute "## ", , , , , , , , True, "AA", 2
      .Replacement.ParagraphFormat.IndentCharWidth 1
      .Execute "# ", , , , , , , , True, "BB", 2
    End With
    .Windows(1).Visible = True
  End With
End Sub

[mehunter]

snb,
Problem is not with the sample code, but when applied to much larger document. My local involves 57 page document, and the subsequent replacements DO NOT complete successfully before Excel executes the next step. Refer to later posts to see clarifications.
I believe that the issue is the lack of synchronization between Excel and the Word instance that Excel created, proved by case where the wait 1000000 works, but without wait it fails.
Thanks your attention.

[snb]

I think you speculate too much.
57 pages isn't a large document.

[Dave]

Does this need to be..

Application.ScreenUpdating = True

before the changes and then False afterwards or maybe the find doesn't work then? Set the Visible to false and then code to save and close the document. U should also set your object variables to nothing before U exit the sub. Anyways, re. your pm, there are Word constants for all those commands that excel doesn't like... they are numbers that U put in to replace the commands... Google Word constants. As snb suggested, it shouldn't take that long. HTH. Dave

[Aussiebear]

Even for my Aussie humour.....(miasmic is a bit strong.!) Oh well its out there.

[Dave]

miasma

/mɪˈæzmə/

noun (pl) -mata (-mətə), -mas 1. an unwholesome or oppressive atmosphere

2. pollution in the atmosphere, esp noxious vapours from decomposing organic matter



Derived Forms
miasmal, miasmatic (ˌmiːəzˈmætɪk), miasmatical, miasmic, adjective

Hmmm... that's not quite what I meant.

The miasma theory is an obsolete medical theory that held that diseases—such as cholera, chlamydia, or the Black Death—were caused by a miasma, a noxious form of "bad air", also known as night air. The theory held that the origin of epidemics was due to a miasma, emanating from rotting organic matter.

That's more like it. Back in days of old, when people got sick with disease they blamed the swamps and drained them. It worked because the mosquitoes causing disease that lived in the swamp no longer had a place to live. So, they were correct that draining the swamp did cure disease but not for the right reason. That is miasmic.
Anyways, I noticed that I actually had the screenupdating thing wrong in my previous post... should be False at the start of the code and then returned to True at the end. Dave

[mehunter]

Can someone verify my results that Excel does not know when a command executed on an instance of word has completed execution, and therefore issues it’s next command to it’s instance of Word before Word is finished processing the previous command?

Here’s my evidence:
1. I have a series of VBA commands that execute correctly if run in Word.
2. The commands do not run correctly when run when executed from Excel on instance of Word.
3. Commands run successfully when suitable delay is inserted between execution commands in case 2.

if we can get agreement on this, I think we can proceed to figure how to implement solution, possibly involving Semaphore, Mutex or ?

[Dave]

Do U want to solve your processing time speed issue or just continue down this irrelevant path?

The commands do not run correctly when run when executed from Excel on instance of Word

No they don't if U don't code properly. Did U trial snb's suggestion or implement my previous suggestions re. not making the document visible/ turn off screen updating? Anyways, good luck with this. Dave

[snb]

Without a blink

Sub M_snb() 
  With GetObject("G:\OF\example.docx") 
    With .Content.Find 
      .Replacement.ParagraphFormat.IndentCharWidth 2 
      .Replacement.Style = .Parent.Parent.Styles("List Paragraph") 
      .Execute "## ", , , , , , , , True, "AA", 2 
      .Replacement.ParagraphFormat.IndentCharWidth 1 
      .Execute "# ", , , , , , , , True, "BB", 2 
    End With
    .Windows(1).Visible = True 
    .save
    .close 0
  End With 
End Sub

If you are not familiar with Getobject .......