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.Applica
objwrd.Visible = True
Set doc = objwrd.Documents.Open(File
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.
So far I have:
Dim objwrd As New Word.Application
Dim doc As Word.Document
Set objwrd = CreateObject("Word.Applica
objwrd.Visible = True
Set doc = objwrd.Documents.Open(File
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.
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?
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?
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:valu
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?
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?
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?
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
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.
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.
hi Declan,
> "How do I get to the collection of Projects in a Word.Application object?"
oApp.ActiveDocument.VBProj
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
> "How do I get to the collection of Projects in a Word.Application object?"
oApp.ActiveDocument.VBProj
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.VBComponent
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
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"),
Application.VBE.ActiveVBPr
Dim frm As Object
Set frm = CreateObject("Application.
frm.Controls("TextBox1") = "abc"
frm.show
Application.VBE.ActiveVBPr
Dim frm As Object
Set frm = CreateObject("Application.
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!
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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial