PDA

View Full Version : Office Automation: Excel executng Word VBA



mehunter
01-23-2018, 07:21 PM
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
01-24-2018, 06:10 AM
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
01-24-2018, 07:04 AM
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
01-24-2018, 07:09 AM
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
01-24-2018, 08:35 AM
Refer to my clarifications posted below.

mehunter
01-24-2018, 08:52 AM
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
01-24-2018, 09:16 AM
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
01-24-2018, 09:26 AM
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
01-24-2018, 01:33 PM
I think you speculate too much.
57 pages isn't a large document.

Dave
01-24-2018, 06:50 PM
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
01-25-2018, 03:26 AM
Even for my Aussie humour.....(miasmic is a bit strong.!) Oh well its out there.

Dave
01-25-2018, 04:43 AM
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
01-25-2018, 07:10 AM
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
01-25-2018, 10:45 AM
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
01-25-2018, 11:01 AM
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 .......