Working with a WORD doc in VB - saving Word doc data to an Oracle database through VB

christy2931
christy2931 used Ask the Experts™
on
I have a Word document that contains VB fields (textboxes, combo boxes, etc) and I'm opening it in an OLE container in my VB project.  I've basically done that with this line of code.  OLE1.CreateEmbed "C:\\FORMS\1483525.doc"

The user can click into the OLE container, and enter text in the textboxes in the document.  My problems comes after this - I want to be able to save the data in the textboxes in separate fields in a table in my Oracle database.  I'm having trouble referencing the fields in the document from my VB code.  I can reference the OLE control in my VB code obviously, but I can't seem to be able to get access to the data in the Word doc fields, from my VB code.

I'm sure there's a way to do this - can anyone help?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Just bumping this up - can anyone help me? :)
GrahamSkanRetired
Top Expert 2012

Commented:
I would advise using OLE Automation (COM) without embedding into an OLE Container.

Set a reference to the Microsoft Word Object library, then

Dim wdApp as Word.Application
Dinm wdDoc as Word.Doc
Dim ffld as Word.FormField
Dim strText as String

Set wdApp = New Word.Application
wdApp.Visible = True 'optional
Set wdDoc = wdApp.Documents.Open("C:\MyFolder\MyFile.Doc")
set ffld = wdDoc.FormFields("Text1")
strText = ffld.Result

Author

Commented:
Thanks, Graham.  I've been trying something similar to that, so I plugged your code in to see if that ran better.  Everything is fine until I get to this line - set ffld = wdDoc.FormFields("Text1")

I replaced "Text1" with the name I've given my textbox in my Word document - is that right?  I've done that, but it's still telling me "the requested member of the collection does not exist" and then when I look at wddoc.FormFields.Count, it says the count is 0.  But I do have formfields in my document.

Do I have to do something else to see my formfields?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

GrahamSkanRetired
Top Expert 2012

Commented:
If the count is 0 then you don't have any formfields.

Did you create the document yourself? If so, how did you add them? Are they from the Forms toolbar or are they Active X controls from the Control Toolbox toolbar?

Author

Commented:
Graham, yes I created the document in Word myself, then I added the textboxes from the VB toolbar.  Should I be adding them a different way?  I added them right to the document, not through code.

Thanks!

Author

Commented:
Ok, I just checked again, and they are from the Control Toolbox toolbar!

Author

Commented:
Ok, I just changed my fields to formfields, so I can access them that way now.  They don't look nearly as pretty on my form though haha!  Is the user still able to type in the fields this way?
Retired
Top Expert 2012
Commented:
VB toolbar? Perhaps you mean the Controls Toolbox toolbar, the one with the 'Design mode' button as opposed to the Forms Toolbar with the padlock button.

They are quite different and each has advantages and disadvantages.

Form fields are designed for Word. There are only three types - text input, dropdown and checkbox. In use, the document must be protected. This prevents the user from changing anything outside the controls. They have only two events - 'entry' and 'exit'. They are easy to address in VB, as my example tried to show. Most of the Word experts know FormFields.  

The Active X controls are the same as are used on VBA forms in Excel, Outlook, Word, etc. They have a full set of events. They can be addressed directly as if on a form
Doc.TextBox1.Text = "abc"

They are more difficult  to walk through as a collection. There isn't a Textboxes or a Controls collection. They will be objects inside a Shape or an InlineShape. There are collections for these two types of object.
Doc.InLineShapes(1).OleFormat.Object.Text = "abc".
There have been reports that many ActiveX objects on a Word document can cause problems, but I don't know how many or even if that has been proved.





Author

Commented:
Thanks, Graham.  I think I'll need to use the Control toolbox to have a wider array of controls and events.

So you say the controls should be inside a shape or inline shape?  Do you mean literally put them inside a shape on the form?
GrahamSkanRetired
Top Expert 2012

Commented:
No, which collection they appear in is determined by the layout. As with most non-text objects a control can be formatted, via Format/Control... , layout tab as 'In line with text' or floating where the text is behind, in front, wrapped around, etc. By default it is in line, taking up space in the text as if it were a word with a large font.

However, it you hard-code all the control names, you needn't go searching for them in collections.

Just before you abandon form documents, I should mention a facility that might make the job easier. There is an option (Tools/Options, Save tab) to save only the data from the form fields as a CSV file.

Author

Commented:
Thanks, Graham.  I will definitely hard code all the control names

So I've embedded the document in my VB form via an OLE control.

I'm trying to use code like this.......   Doc.TextBox1.Text = "abc"

......to access the document through my VB form, but it's not working.  I know you said I don't have to use the collection if I've hard-coded the names, right?
GrahamSkanRetired
Top Expert 2012

Commented:
Do you get an error? Have you set Doc to the correct document object?

Author

Commented:
I've embedded the document in my OLE container.  Do I have to create a document object as well?
OLE1.CreateEmbed "C:\CRMS\FORMS\14-835-vb-3.doc"

I thought this type of code...
  Set oWord = CreateObject("word.application")
  Set oDoc = oWord.Documents.Open("C:\CRMS\FORMS\14-835-vb.doc")

...was only if you wanted to open a new instance of Word instead of embedding the file.

Sorry to be asking so many questions - I'm researching this, but I'm just not finding what I need.

Author

Commented:
I used this code, and I put it after I embedded the document into my VB form:
ActiveDocument.InlineShapes(2).OLEFormat.object.Text = adoRset.Fields("WarrantSeq").Value

But then the embedded document shows, but it has no value in the field - it's empty.  However if I open the document in Word, the values are there.

Is it because I embed the document before I set that value?  But if I don't embed the document until after, how will it know what the "active document" is?
GrahamSkanRetired
Top Expert 2012

Commented:
I think you have a parallel problem with the Word document object that we see when we try to get an unnamed activeX control from the document. That's why I suggested in my first comment that you drop the container altogether and create a free-standing application and document.

I'm sure that what you are doing is possible, I just haven't tried it for years.

I'll play about with it a bit myself and see if I can come up with anything useful.
GrahamSkanRetired
Top Expert 2012

Commented:
OK This seems to work, though actually using CreateLink. I did get a number of Application-defined  errors on this line:
Doc.TextBox1.Text = "abcTextBox"
but it worked OK after Word aborted and I recreated the document.

I then tried it with CreateEmbed and the errors have returned. They may be caused by my VB6 installation which has not been repatched since the last re-installation.


Private Sub Command1_Click1()
    Dim Doc As Word.document
   
    OLE1.CreateLink "C:\Documents and Settings\User Name\My Documents\Appointments.doc"
    Set Doc = OLE1.object
    Doc.FormFields("text1").Result = "abcFormField"
    Doc.TextBox1.Text = "abcTextBox"
   '....
    Doc.Application.Visible = True 'to leave it to the user
   'or perhaps
    Doc.Save
    Doc.Close
   
 
However the OLE Container doesn't seem to give you anything more than a direct COM connection except for a keyhole read-only view of the document. In fact it seems to have a distinct disadvantage with Word. If you have a particular document layout that you want to use repeatedly, you should make a template and create a new document from it. I can't find any way of doing that directly.

Here is how to create a new document without the container:

Dim wdApp as Word.Application
Dim wdDoc as Word.Doc
Dim ffld as Word.FormField
Dim strText as String

Set wdApp = New Word.Application
wdApp.Visible = True 'optional
Set wdDoc = wdApp.Documents.Add("MyTemplate.dot")
set ffld = wdDoc.FormFields("Text1")
ffld.Result = "Some text"
wdDoc.TextBox1.Text = "Some more text"


End Sub

Author

Commented:
Yeah, I wanted to open a new Word dodument as well, but the other people involved would prefer to open the document in an OLE container.  In any case, I managed to get it working late Friday afternoon!  It's very basic right now but gives me something to build on at least.  This is the code I used:

   OLE1.CreateEmbed "C:\CRMS\FORMS\14-835-vb-3.doc"
   Set adoRset = adoCN.OpenResultSet("select * from HCS01945.TESTWORDWARRANT")
   If Not adoRset.EOF Then
     adoRset.MoveFirst
        ActiveDocument.InlineShapes(1).OLEFormat.object.Text = adoRset.Fields("WarrantSeq").Value
        ActiveDocument.InlineShapes(2).OLEFormat.object.Text = adoRset.Fields("SocialWorker").Value
        ActiveDocument.InlineShapes(3).OLEFormat.object.Text = adoRset.Fields("Board").Value
        ActiveDocument.InlineShapes(4).OLEFormat.object.Text = adoRset.Fields("Community").Value
   End If
   OLE1.CreateEmbed "C:\CRMS\FORMS\14-835-vb-3.doc"
   OLE1.Visible = True  
   Set adoRset = Nothing

I had to use the create embed statement at the beginning and end though, which I know isn't the right way to do it - I have to find a way around that.  But I've got the Word doc talking to the database, so it's a start.

Thanks so much, Graham - I really appreciate your help with this one.  The points are yours! :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial