Word Active Document not getting recognized

[jd1963]

I use the following code to achieve following
a) Open a word document from MS access / VBA,
b) Connect to data from a query
c) mailmerge it
d) Save it as a new document containing mailemerged letters.

The code does open the required document as I can see it open.
I put a message box to recheck that filename/path is what I mean, and to to know till which line the code runs. Code runs till that point and that word doc is seen open. I get the error message before

Then I get error message.

I was fooling around and was getting message " This command is not available because no document is open" I tried to remove few lines thinking redundancy. Before getting the message, I was also getting the error trapping message "No documents opened."

Now I am getting different message.

I am getting message " The remote Server Machine does not Exist or is unavailable"

Following is the code I have written.

I accept the limitation of self taught programmer using copy/ paste quite a bit, and limitations of understanding coming with it.

Dim xlApp As Object
Set xlApp = CreateObject("word.Application")
 
xlApp.Visible = True
fn = FormPathName("941 2014 AutoOpen.doc")


Dim wdocSource As Object
 Set wdocSource = xlApp.Documents.Open(fn)
    wdocSource.MailMerge.OpenDataSource _
            name:=getSystemPref("mdbwithPathForMailmergeDataSource"), SQLStatement:="SELECT * FROM [941Table]"

        xlApp.Documents.Open (fn)
        xlApp.Documents.Open(fn).Activate  ' This may be redundant but was part of my trial error

'-- Following loop was added thinking that I am trying to see the Active 'Document before it really opens. Just a delay
 
 Do While k < 40000
 k = k + 1
 Loop


 MsgBox (fn)
'  File Name is displayed correctly 
' The Word doc is seen open
' Connection is good as I can manually see the mailemerged data.

            If Documents.count > 0 Then
           ' MsgBox ActiveDocument.name
            Else
            MsgBox ("No documents opened.")
            End If

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
    End With
'
Dim sSaveName
sSaveName = getSystemPref("OutputFiles") & "941s Q" & q & " " & getSystemPref("CurrentYear") & ".doc"

Documents(1).SaveAs (sSaveName)

'ActiveDocument.Close

Documents("941 2014 AutoOpen.doc").Close

xlApp.Visible = True
xlApp.Activate
Set xlApp = Nothing


[\CODE]

I have used lot of information from the forum before and really appreciate the help.
Thanks

jd1963

 

[The_Doc_Man]

What version of Office? I've noted some "interesting" similar (though not identical) behavior since I upgraded to Office 2013 and have a question or two about this myself.

 

[JHB]

I observe some discrepancies in the code.
Your delay isn't really a delay, it is only a loop. To passes control to the operating system for take care of other work, you need to use DoEvents.
Then you've "Documents" and "ActiveDocument" without a reference in the front to the object, you need the reference because "Documents" and "ActiveDocument" is unknown to MS-Access.

 

[jd1963]

Thanks, Doc Man
I am using Access 2003.

 

[jd1963]

Thanks JHB,

You are very right about the loop. I changed and it worked!! I suspected that to be least of the problem.

But my surprise is that it worked without making changes suggested by you to refer to Document and Activedocuments. I did not make that change simply because I could not figure out exact syntax!

Right now, the issue is fixed, but would like to be educated about referring to Document and Activedocuments - exacty how to.

Did it work because of xlApp.Documents.Open (fn) before?
Should the Document and Activedocuments have xlApp. inserted before it to be a good code?

Will appreciate if you can include that on lower end of your priority spectrum.

Thanks again!

 

[JHB]

...
But my surprise is that it worked without making changes suggested by you to refer to Document and Activedocuments.

I think it is exactly the same as happen when an Excel sheet is open. Each second time the code runs okay, each second time the code triggers an errors.

...
I did not make that change simply because I could not figure out exact syntax!

That surprise me really because you use it here :

[COLOR=Red][B]xlApp.[/B][/COLOR]Documents.Open