Avatar of Declan Basile
Declan Basile
Flag 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.
Microsoft AccessVBAMicrosoft Word

Avatar of undefined
Last Comment
Declan Basile

8/22/2022 - Mon
John Tsioumpris

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?
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?
Declan Basile

ASKER
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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Declan Basile

ASKER
How do I get to the collection of Projects in a Word.Application object?
Declan Basile

ASKER
    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 Tsioumpris

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Declan Basile

ASKER
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

Declan Basile

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Declan Basile

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
John Tsioumpris

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

Declan Basile

ASKER
Thanks again for the help!