Opening word document from Access.

[kacey8]

Not sure where to put this so here it goes, Yes it is the wonderful me again and once again I hit a little wall. :banghead:

I have a basic button, the button opens a word document

(Code here)

Private Sub Letter1label_Click()
   Dim LWordDoc As String
   Dim oApp As Object
   
   'Path to the word document
   LWordDoc = "X:\Access Database - XXXX XXXXX\Letter 1.docx"
   
   If Dir(LWordDoc) = "" Then
      MsgBox "Labels not found"
      
   Else
      'Create an instance of MS Word
      Set oApp = CreateObject(Class:="Word.Application")
      oApp.Visible = True
      
      'Open the Document
      oApp.Documents.Open FileName:=LWordDoc
   End If
End Sub

Now the document is a mail merge file to print labels. it works of a query from the same DB and when I open the file on it's own it prompts to update/run the SQL Query and all is fine.

However when I use the button, the file opens but no prompts and the mail merge filters are greyed out (basically as if it hasn't had a source selected


Any advice on this one?

 

[vbaInet]

Try releasing the control back to windows:

    oApp.Documents... etc
    [COLOR="Blue"]oApp.UserControl = True[/COLOR]
End If

Set oApp = Nothing

and remember to release the object too (if you're not already doing that) - i.e. the code after End If.

 

[kacey8]

I get an error "Usercontrol" is a read only property and whilst the merge file loads, the SQL box doesn't appear

Full code here

   Dim LWordDoc As String
   Dim oApp As Object
 
   'Path to the word document
   LWordDoc = "X:\Access Database - Lettings Canvassing\Letter 1.docx"
 
   If Dir(LWordDoc) = "" Then
      MsgBox "Labels not found"
 
   Else
      'Create an instance of MS Word
      Set oApp = CreateObject(Class:="Word.Application")
      oApp.Visible = True
 
      'Open the Document
      oApp.Documents.Open FileName:=LWordDoc
      oApp.UserControl = True
   End If
Set oApp = Nothing
 
End Sub

 

[vbaInet]

Interesting! Read only in Word and Read/Write in Excel.

How is this dialog box called? There should be some method that does that. You can call that function/sub from the oApp object.

 

[kacey8]

Sorry, Which dialog box? the one when I open the letter normally in word (not using access) it's this one.

If I hit yes here, it'll run the query and only import the data from the query (which is perfect)

When I use it in Excel I get no box, it opens the document minimised on the start bar.

 

[vbaInet]

So what is the code that fires up this action?

 

[kacey8]

So what is the code that fires up this action?

Nothing I can see... This comes up because the document is a Mail Merge item.

Basically I created a mail merge were the source of the merge was the DB, and the query, I did not create the pop up, Word does that automatically.

Can I even trace the code for that?

I can't attach the word file as it is a .docx and it does say invalid file.

 

[vbaInet]

Oh I see. It's been ages I did a mail merge in Word.

The problem could be that you're running the code from the same Access db that's the source of your mail merge.

To rule out this theory, can you create a blank db, close your main Access db and run the code from the new db.

 

[kacey8]

I'll try with a totally separate DB tomorrow however I did try with two different files as the DB is split with a BE I was running two different front ends, one executing the request, the second handled the SQL query (different accdb files)

 

[vbaInet]

Any new blank DB will do. Just copy and paste the code and run it from there.

Will wait for the result tomorrow.

 

[kacey8]

Any new blank DB will do. Just copy and paste the code and run it from there.

Will wait for the result tomorrow.

Okay,

Tried it on a fresh blank DB, both with

oApp.UserControl = True

and without.

No difference / change

 

[vbaInet]

This is quite unique. Can you upload a sample file with two mail merge fields for me to test.

 

[kacey8]

Ok so I made a sample DB, Sample Letter and entered some sample Data,

The Sample has to be split DB (to allow word/Access access the DB at the same time) so you'll need to remap the tables whereever you save it.

Also in the Letter / Merge file you'll need to go to

Mailings > Select Recipients > Use Existing List

and point it to the Database (Front end) and select Query 1.

Thats it I think. any problems let me know.

 

[vbaInet]

It looks like there's more to this than meets the eye. This feature was disabled by default if you open a mail merge document in code. The reasons and workaround are explained in this article from Microsoft support page:

http://support.microsoft.com/kb/825765

Of course changing the registry value is at your own risk.

For a workaround you can look at (MVP) Albert Kallal's page and search for "Super Easy Word Merge":

http://www.kallal.ca/msaccess/msaccess.html

 

[vbaInet]

And here's another piece of code:

http://support.microsoft.com/kb/285332

 

[kacey8]

Thank you very much, especially for all your effort.

 

[vbaInet]

No problem!

I sent you the wrong Microsoft article. I've updated the link.