We help IT Professionals succeed at work.

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.
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
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?

Author

Commented:
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?

Author

Commented:
Project.JPG
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?

Author

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

Author

Commented:
    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
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
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

Author

Commented:
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

Author

Commented:
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
Word doesn't have a forms collection.  The VBproject of a Word document only has a VBComponents collection, of which some of the components may be of type "3" meaning "UserForm".  When you create a form in Word, it's defined as its own class derived from the intrinsic UserForm (ex. UserForm1), and there's no way for VBA running in a different application to know about that class, so you can't declare an object variable of type UserForm1 in code contained in another application.  You can only declare an object variable of the intrinsic type "UserForm".  So even if you could cast a variable of type "object" to a user defined type in VBA (which you can't, at least that I've seen), the application wouldn't know about the class defined in the project of a different document.  Alternatively, if you just leave the variable as type object, the show method isn't recognized.  

So I had to create a function to instantiate an instance of UserForm1 in the same VBProject that contains UserForm1 and use the show method in *that* vbProject, and from the remote VBProject contained in an Access database, I used the Application.Run method to run the function.

And John, to explain better why I'm using this approach, there are a lot of forms and always new forms that need to be created at our company.  Technology has gotten to the point that it's easy for the users to create their own forms.  Placement of textboxes, dropdown boxes, even defining simple validation has become easy with no coding necessary.  So, at least in the situation of the company I work for, taking the creation of forms out of the hands of the developers for every single form that needs to be created, and giving the users the flexibility to define their own forms is absolutely the right approach.  Also, they don't have any low level access to the application that calls for the forms to be open or have any low level access the database that it's connected to.  The only thing that's passed back and saved in the application program (which is under strict control) is a string of data so the form that they design can be opened back up and filled back in with the data.

Thank you both for your help.  Although I answered the initial question myself, you gave my answers to my questions along the way that helped me figure it out!
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:

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

Author

Commented:
Thanks again for the help!