Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

Opening a Form contained in a Word document via VBA automation from Access

From vba in Microsoft Access, how can I open a form saved in a word .docm document and set a textbox on the form to a value?

So far I have:

    Dim objwrd As New Word.Application
    Dim doc As Word.Document
   
    Set objwrd = CreateObject("Word.Application")
    objwrd.Visible = True
    Set doc = objwrd.Documents.Open(FileName:="C:\Users\db1\Documents\Test.docm", Visible:=True)

I know if I'm running code contained in the Word document I can do something like this

Dim frm As New UserForm1
frm.Show

But this code is automation code running in Microsoft Access and I don't see a forms collection in the Document object.
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Check this post here : http://www.vbaexpress.com/forum/showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet
I think that you are looking for the ContentControls although i have never had experience with Forms on Word.
Wouldn't be easier to create a Form on Access and just pass the data to Word?
Avatar of Declan Basile

ASKER

Ultimately, I want users to be able to create their own forms in Word with drop down lists, validation, etc. and have the access program save in a table the data entered as one string (ex. "field1:value1,field2:value2, etc."), and have the access application able to open the form back up re-populating the data.  Then forms could be created by users without developers having to create every new form in Access.

I added this code

    MsgBox doc.ContentControls.Count
    For Each cc In doc.ContentControls
        MsgBox cc.ID
    Next cc

But it displays that count is 0 and doesn't see userform1 saved in the word document as a contentcontrol.  Is there some way I can open userform1 from the VBA code run from Access?
User generated image
UserForm1 is under Project/Forms.  It's not under "This Document".  There isn't a Forms collection under document.  Is there a Forms collection somewhere else?
How do I get to the collection of Projects in a Word.Application object?
    Dim objwrd As New Word.Application
    Dim doc As Word.Document
'    Dim cc As Word.ContentControl
    
    Set objwrd = CreateObject("Word.Application")
    objwrd.Visible = True
    Set doc = objwrd.Documents.Open(FileName:="C:\Users\db1\Documents\Test.docm", Visible:=True)
'    MsgBox doc.ContentControls.Count
'    For Each cc In doc.ContentControls
'        MsgBox cc.ID
'    Next cc
    
    Dim frm As MSForms.UserForm
    
    Set frm = objwrd.VBE.ActiveVBProject.VBComponents.Item(3)
    
    frm.Show

Open in new window


This is as far as I got.  Why is the "Set frm = ..." line of code giving me a type mismatch when Item(3).Type = 3 meaning it's a Microsoft Form?

.Type ...
vbext_ct_MSForm      3      Microsoft Form
As i said i have never dealt with Word Forms ( and to my Knowledge only a handful of people are creating Word Forms...in all these years in EE i think its the first time i see such a question)...if you would share the Word probably we could provide some better help.
Putting a watch over the object ( i think you should work with the doc object) you could see the correct object reference.
Also putting the end users to perform such a low level interaction with the data is not a good idea....
Just check the user needs and create a somewhat versatile form that adapts and you should be much better.
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

hi Declan,

> "How do I get to the collection of Projects in a Word.Application object?"

oApp.ActiveDocument.VBProject

WHERE oApp is a reference to the application (not needed if code is running from Word, but in your case, it isn't)

ActiveDocument can be substituted with another document reference

If I had a Word doc handy with a form, I'd drill down more ...

~crystal
Given the code below, I know that "doc.VBProject.VBComponents.Item(2)" is a form named "UserForm1".  I get a "Type mismatch" error on the line "Set frm = doc.VBProject.VBComponents.Item(2)".  I have a reference to Microsoft Forms 2.0 Object Library.  Now that I know how to access the form, how can I open it in VBA code?  Also, I exported the form which output two files (UserForm1.frm and UserForm1.frx).  How can I open the form directly from these files instead of accessing the form contained in the Work document?

    Dim objwrd As New Word.Application
    Dim doc As Word.Document
    Dim frm As MSForms.UserForm
    
    Set objwrd = CreateObject("Word.Application")
    objwrd.Visible = True
    Set doc = objwrd.Documents.Open(FileName:="C:\Users\db1\Documents\Test.docm", Visible:=True)

    Set frm = doc.VBProject.VBComponents.Item(2)
    frm.Show

Open in new window

If I import a userform via vba and use late binding to set an object variable to it because it doesn't exist (i.e. Isn't Imported) until runtime, how would I reference the userform in a CreateObject statment?  I've tried CreateObject("UserForm1"), CreateObject("TestWord.UserForm1") <note: TestWord is the name of the Access Project>, and CreateObject("Application.UserForm1") and none of them worked.  See code below ...

    Application.VBE.ActiveVBProject.VBComponents.Import "c:\users\db1\documents\userform1.frm"
   
    Dim frm As Object

    Set frm = CreateObject("Application.UserForm1")
    frm.Controls("TextBox1") = "abc"
    frm.show
ASKER CERTIFIED SOLUTION
Avatar of Declan Basile
Declan Basile
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Interesting approach...if it works for you...then is perfectly fine...

Thanks again for the help!