Communicating between Excel and Word

[adeeshc]

I am looking to write a procedure in Excel which opens a Word document.
Challenge is that the Word document has an AutoOpen procedure resulting in the word document opening with a vbYesNo MsgBox.
I want to select Yes all the time. And then continue with the AutoOpen word procedure.

Again I cannot change the Word document code as this requires the user to give the desired input when opened independently.

So I want this input to be passed from my initial Excel procedure.


Hope someone could help me with this here. I am too tired after trying various methods throughout the day without success

 

[James006]

Hello,

Why don't you post your current VBA code ...

 

[adeeshc]

Hello,

Why don't you post your current VBA code ...

wordapp.documents.Open (ActiveWorkbook.Path + "" + PN + "_Report.docm")
wordapp.Visible = True

wordapp.documents.Save
wordapp.documents.Close


The above is what I have ....this opens the word document, runs its autoopen procedure, and prompts for a VBYesNo. I need to skip this step and automatically give the Yes and proceed.

 

[Macropod]

One way is to disable execution of the AutoOpen macro in Word (wordapp.WordBasic.DisableAutoMacros 1) and use your Excel automation to do whatever aspect of the [disabled] Word AutoOpen macro you want to perform.

Alternatively, you'll need to modify the Word code to enclose the MsgBox prompt in a If test that can be used to bypass it when the document is opened programmatically. In such a scenario, the Word code might look like:

Sub AutoOpen()
  Call Main(False)
End Sub

Sub Main(bAutomation As Boolean)
If bAutomation = True Then
  Rslt = vbYes
Else
  Rslt = MsgBox("Some Prompt", vbYesNo)
End If
'The rest of the Word code from the existing AutoOpen macro goes here.
End Sub

where bAutomation is a Boolean you'll be able to pass to Word from your Excel code. And, in Excel, you'd use something like:

With wordapp
  .WordBasic.DisableAutoMacros 1
  .Visible = True
  Set wdDoc = .Documents.Open(FileName:=ActiveWorkbook.Path + "\" + PN + "_Report.docm", _
    AddToRecentFiles:=False, Visible:=False)
  .Run Main, True
  With wdDoc
    'Do whatever you need Excel to do with the document
    .Close True
  End With
End With