Export word data to excel

[Johnosley]

Hi,

I'm still working on the same project. It's moving slowly but at least I'm learning something and now I have more and more questions !

I will try to summarize what I need and where I am ! Not easy.

1) needs

A word document (template) to allow user to fill in easily and allow other users to add comments and/or use track changes mode

2) I did some search on internet and I came to the conclusion that using bookmarks should be a good idea

I built my word template and then created a userform. Which will be great for the user to get some help/guidance to be able to fill in properly the template.

Now, my template is done. The userform is working (almost). I got lots of help to do that and now I'm able to export those data (bookmarks) from my word doc to my excel doc. Wow :-)

3) But...

I realized that bookmarks as some limitation :
- placeholder bookmark vs enclosed
- they could be deleted by the user if the user decide to fill in the form without the userform

4) May be... I'm not doing the right thing ! and may be this is an other way to deal with the data stored in my word template.

5) where I am now?

a) you can have a look on the example attached and any thoughts or reviews are welcome
b) I'm looking for some ideas on how I can improve that using or not bookmarks
c) may be the bigger thing: the user can start to fill in the form using my userform. He can come back to do some modification or complete the form. But in order to do that, the userform has to show what was already filled in. For now, everytime you are using the userform, data are entered and instead of having "Y" to one question I could have "YYYY" if I opened 4 times my userfom. May be I'm not enough clear. Let me know.

But for now and before moving forward using bookmarks, I would like to get some ideas, tips on how I can export my data. Please note that the template could be customized differently.

Thanks

[fumei]

I do not use 2007 (hate it) so I can not help here. I can look at the file, but all code is stripped out. I am sure others will be able to help.

I can make general comments from what you posted.

Bookmarks can have data put in them dynamically that does not delete the bookmark.

Userforms can display with previous data. Previous data is stored as DOCVARIABLES.

[Johnosley]

Bookmarks can have data put in them dynamically that does not delete the bookmark.

Userforms can display with previous data. Previous data is stored as DOCVARIABLES.

Those 2 ideas seem to be interesting.

For the 1st one I didn't find anything yet on how it works. For the second one, docvariable is new for me. I'm not using word a lot (except basic things) and I've never heard about that before. seems to be promising.

I have to do some testing and see how it works with a blank doc.

But based on your experience and knowledge about word/vba, on which one I should focus ?

My goal could be summarize as follow

1) open the word template
2) launch the userform
3) the user fill out as much as possible : example: textbox1 = yadda yadda
4) word template shows "yadda yadda" in the appropriate location in the doc

Option 1:
the user can decide to launch again the userform to update the word doc
6) textbox1 shows "yadda yadda".
7) user changes that to "yadda yadda yadda"
8) word doc is updated

Option 2
the user is changing the data directly in word without opening the userform
6) in word, the text is showing "yadda yadda yadda"
7) if the user decides to open again the userform, textbox1 should be "yadda yadda yadda".

The userform is really their to help the user to fill out the form.

But now I'm confused on the best approach to do that and if it's really possible !

[fumei]

First....

"1) open the word template"

Hopefully you do not mean that. User should NEVER open a template. Do you mean use the template to clone a new document?

Moving on...you can do it all. Demo attached using a document, not a template. The userform comes up on document open.

The userform fills the textboxes with the current text at the bookmarks. Whether that has been inserted by a previous use of the userform, or manually changed by the user.

In a standard module:
Option Explicit

Sub FillBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
r.Text = strText
ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End Sub
This allows dynamic insertion of text into a bookmark. If you use it again, the bookmark is retained (technically it is recreated).

In the ThisDocument code module:
Private Sub Document_Open()
UserForm1.Show
End Sub
to simply bring up the userform on document open.

In the userform code module:
Option Explicit

Private Sub cmdOK_Click()
Call FillBM("BM1", TextBox1.Text)
Call FillBM("BM2", TextBox2.Text)
Unload Me
End Sub

Private Sub UserForm_Initialize()

TextBox1.Text = ActiveDocument.Bookmarks("BM1").Range.Text
TextBox2.Text = ActiveDocument.Bookmarks("BM2").Range.Text
End Sub
The commandbutton code writes the content of the textboxes into the bookmarks.

Suppose the user manually changes the text in the bookmark (from "yadda yadda" to "yadda yadda yadda".

Sooooooooooooooooo, when the userform is Initialized...the textboxes are simply prefilled with whatever is the current bookmark text.

[Johnosley]

First....

"1) open the word template"

Hopefully you do not mean that. User should NEVER open a template. Do you mean use the template to clone a new document?

You are right. I mean open the template to create a new document.

Your code is really that I need. I'm doing some test with it into my example and see how it works. I need to understand that to be able to duplicate if needed. But so far so good.

Thanks a lot for your help.

I will try to clean up my example and get something available tomorrow.

I need to work on that and modify my initialize() sub and also my cancel_click() sub.

[fumei]

Not trying to be critical. just trying to help get the correct terms.

"I mean open the template to create a new document."

You do not open a template to cerate a new dcoument.

You call the template file. You do this by using File > New; or double clicking the .dot file in an Explorer window; or by code.
Documents.Add Template:="c:blah/whatever.dot"
which creates a new document and attaches the whatever.dot template to it.

Going back to the filling a userform with data on display. In the above case since you want the contents of the bookmark regardless of what was inserted from the previous userform, then the userform simple takes whatever is in the bookmark.

Just to show you how you can retrieve previous data,
Private Sub cmdOK_Click()
Call FillBM("BM1", TextBox1.Text)
Call FillBM("BM2", TextBox2.Text)
ActiveDocument.Variables("BM1").Value = Textbox1.Text
ActiveDocument.Variables("BM2").Value = Textbox2.Text

Unload Me
End Sub

Private Sub UserForm_Initialize()

TextBox1.Text = ActiveDocument.Variables("BM1").Value
TextBox2.Text = ActiveDocument.Variables("BM2").Value
End Sub
This initializes the userform textboxes with the value from the variables set by the previous commandbutton. Here is another example.
Private Sub cmdOK_Click()
Call FillBM("BM1", TextBox1.Text)
Call FillBM("BM2", TextBox2.Text)
ActiveDocument.Variables("BM1").Value = Textbox1.Text
ActiveDocument.Variables("BM2").Value = Textbox2.Text
ActiveDocument.Variables("UsedMe").Value = _
ActiveDocument.Variables("UsedMe").Value + 1
Unload Me
End Sub

Private Sub UserForm_Initialize()
Label1.Caption = "You have used this input form " & _
ActiveDocument.Variables("UsedMe").Value & " times."
TextBox1.Text = ActiveDocument.Variables("BM1").Value
TextBox2.Text = ActiveDocument.Variables("BM2").Value
End Sub
This put the text "You have used this input form X times." into a label on the userform, with the count being stored as the DOCVARIABLE "UsedMe", which is incremented every time the commandbutton is clicked.

[Johnosley]

Not trying to be critical. just trying to help get the correct terms.

"I mean open the template to create a new document."

You do not open a template to create a new document.

You call the template file. You do this by using File > New; or double clicking the .dot file in an Explorer window; or by code.
Understand. And I think using the right terms makes things clear. thanks for that. I haven't think about this "detail" but now using more and more VBA I think this is the right to do.

For the code, I'm trying to apply that to optionbutton, combobox etc.

Previously I had (I have to optionbutton call optitem1 and optitem2 for example)

Private Sub cmdOK_Click()
Dim stritem1 As String
If optitem1 = False Then stritem2 = "Y" Else stritem1 = "Y"
ActiveDocument.Bookmarks("item1").Range.Text = stritem1
End Sub
I would like to put that instead

Private Sub cmdOK_Click()
Dim stritem1 As String
If optitem1 = False Then stritem2 = "Y" Else stritem1 = "Y"
Call FillBM("item1", stritem1)
End Sub
But doing that I have to declare stritem1 in my cmdOK_click() sub. But how to initialize that ?

[Johnosley]

May be I should set up differently my document to avoid the use of optionbutton etc. Because I would like to deal with Yes or No instead of False/true value. I hope my night will bring some good idea... at least once :-)

[fumei]

Trying to follow that.

But doing that I have to declare stritem1 in my cmdOK_click() sub. But how to initialize that ?If you are going to use a variable, yes you must declare it some place.

Not sure what you mean by initialize it. You could declare it as a PUBLIC variable and set its value in Userform_Initialize. Because it is Public, you can use it in _Initialize (to give an initial value) AND in cmdOK_Click (to change I assume). Although I am not clear why. Regarding your code:Private Sub cmdOK_Click()
Dim stritem1 As String
If optitem1 = False Then stritem2 = "Y" Else stritem1 = "Y"
Call FillBM("item1", stritem1)
End Sub
First, I would get into the habit of indenting your logic statements with an explicit End If.
Private Sub cmdOK_Click()
Dim stritem1 As String
If optitem1 = False Then
stritem2 = "Y"
Else
stritem1 = "Y"
End If
Call FillBM("item1", stritem1)
End SubHowever, since you are using an explicit value for Call FillBM, I fail to see why you need a variable.
Private Sub cmdOK_Click()
If optitem1 = False Then
stritem2 = "Y"
Else
stritem1 = "Y"
End If
Call FillBM("item1", "Y")
End Sub
does the same thing. Also, if the firststatement is True, you make strItem2 = "Y". The variable striitem2 is not declared.

[Johnosley]

Can I do that instead ?


Private Sub cmdOK_Click()
If optitem1 = False Then
Call FillBM("item1", "Y")
Else
Call FillBM("item2", "Y")
End If
End Sub


My question related to initialize() was related to how set the value properly for optitem1 and 2 ?

For now I have

Private Sub UserForm_Initialize()

' default value
optitem1.Value = True

End Sub

But if the user removes the "Y" related to item1 it means that he put the "Y" on item2. And my userform should reflect that.

I know that it's a bit weird... but knowing who will use that I can expect such stupid things (using word doc instead of userform !).

Thanks again

[fumei]

Private Sub cmdOK_Click()
If optitem1 = False Then
Call FillBM("item1", "Y")
Else
Call FillBM("item2", "Y")
End If
End Sub
Can I do that instead ?

Yes. You could also do it as:
Private Sub cmdOK_Click()
Select Case optitem1
Case False
Call FillBM("item1", "Y")
Case True
Call FillBM("item2", "Y")
End Select
End Sub In both cases you are testing a Value, and if it is true do one thing, if it is False do something else.

As for: "how set the value properly for optitem1 and 2 ?"

These can be set in design mode. In other words, you can make them be selected, or not before the userform even opens. yes, you can also set them programmatically. I wonder why you want to do it programmatically.

"But if the user removes the "Y" related to item1 it means that he put the "Y" on item2. "

I do not know what you mean by th euser removing the "Y". The user (as far as I can see) is actioning the option. They either have it selected, or not. They are not removing any "Y". The"Y" is determined by your code logic.

Normally, if you have two optionbuttons they are mutaully exclusive. If one is True, the other is False.

This actually brings up an important point. By default when you put two optionbuttons on a userform, and show the userform BOTH are blank (False). I think this is NOT a best practice, and in fact, I do not do this. I make one of them True. In other words, on userform.Show they are NOT both blank (False). I do this in design mode. In the Properties window for the optionbutton I change one .Value to true.

[Johnosley]

Please see attached what I did using your example.

I haven't thought about the by default option for button. That's great and will sanitized my code.

I added some check box to show my problem with the initialize thing.

You launch the userform and then select whatever you want. Your result appears on the word doc on the appropriate location based on bookmark.

If I decide to modify that I have to open again the userform but the userform is like a blank one (except optionbutton1 which is set by default to True). I'm not able to see what I selected first. is it clear ?

[fumei]

" I'm not able to see what I selected first."

Do you mean that if you RE-open the userform you want it to be set for what you did previously?

If so, you need to check out using DOCVARIABLES.

As for: "I added some check box to show my problem with the initialize thing."

No, it does not show your problem. I do not know what your problem is. Tell me.

Private Sub UserForm_Initialize()
' option button
' ???? = ActiveDocument.Bookmarks("BM1").Range.Text
' = ActiveDocument.Bookmarks("BM2").Range.Text
' check box
' ???? = ActiveDocument.Bookmarks("BM3").Range.Text
' = ActiveDocument.Bookmarks("BM4").Range.Text
' = ActiveDocument.Bookmarks("BM5").Range.Text
' = ActiveDocument.Bookmarks("BM6").Range.Text
End SubObviously ActiveDocument.Bookmarks("BM4").Range.Text has nothing to do with optionbuttons OR checkboxes. But it does not show what your porblem is...I do not know what you want to happen. What are you trying to do??

I think I was pretty clear in the example code containing:

ActiveDocument.Bookmarks("BM1").Range.Text

it was to put the text into a textbox, which it did. Nothing to do with optionbuttons or checkboxes.

[Johnosley]

" I'm not able to see what I selected first."

Do you mean that if you RE-open the userform you want it to be set for what you did previously?

yes


If so, you need to check out using DOCVARIABLES.

I'll check that then.


I think I was pretty clear in the example code containing:

ActiveDocument.Bookmarks("BM1").Range.Text

it was to put the text into a textbox, which it did. Nothing to do with optionbuttons or checkboxes.

Yes it was clear but I tried to extrapolate that to the optionbutton and checkbox... and this is why it's not working. I'm still wondering if I'm doing the right think or if I will be able to do it !

thanks for your patience... I definitively need a book for dummies !

[fumei]

"Yes it was clear but I tried to extrapolate that to the optionbutton and checkbox... and this is why it's not working."

Again...tell us what you DO want to work. It may not be working (and obviously it is not as tjhere is nothing there), but I have no idea what would be working for you. Tell us what you want to happen.

[Johnosley]

"Yes it was clear but I tried to extrapolate that to the optionbutton and checkbox... and this is why it's not working."

Again...tell us what you DO want to work. It may not be working (and obviously it is not as tjhere is nothing there), but I have no idea what would be working for you. Tell us what you want to happen.

What I would like is fully interaction between the userform and the word document.

May be I should explain a bit more about the context.

The end product is a software develop by a third party to submit regulatory forms. The forms in the software can be filled out manually or the data can be imported from excel.

because I'm dealing with so many data/forms I used an excel template. But using the excel template has some limitation for the user such as the review mode (track changes etc.). Also, most of the data are words. So using word makes more sense.

The form itself contains questions (yes/no), list of item you have to check and open questions such as description of the project for example.

My first idea was to create a word template divided into table. each table contains questions and answer (see attached for an example).

tables(1).cells(1,3) = question 1
tables(1).cells(1,3) = answer 1
etc...

then I decided to build a userform to help the user to fill out the form.
- easier to follow
- default answer for some questions
- nice user interface
etc...

Given that, I created bookmarks in my document. It works fine. And the code you gave me is great when I'm dealing with text and textbox.

With optionbutton and/or checkbox, the value is true or false. But I need to see yes or No in my document.

With your help, I was able to update the userform with information contained in the document (if the user launches again the userform, the userform shows that was already filled out). But this is not working for optionbutton/checkbox.

Everytime I open the userform, checkboxes and optionbutton are set to their default value instead of the last value entered.

At the end, I have to export all answers to specific cell in my excel workbook. And do again the same process for the next form.

A workbook can have lots of form (1 form = 1 worksheet).

For the docvariables things, I have to search. Never used that before so I don't know anything about the pros and cons using that instead of bookmark. But may be, bookmarks is not the right thing to use anyway.

[fumei]

Or, if I may sum up.

If checkbox_Blah is checked on previous use of the userform, I want checkbox_Blah to be checked on a new opening of the userform.

Hows that? Is that what you want? Easy to do.

Oh, and: "For the docvariables things, I have to search. Never used that before so I don't know anything about the pros and cons using that instead of bookmark. "

Never said anything about "instead" of bookmark.

[Johnosley]

Or, if I may sum up.

If checkbox_Blah is checked on previous use of the userform, I want checkbox_Blah to be checked on a new opening of the userform.

Hows that? Is that what you want? Easy to do.

Yes this is what I'm looking for.


Never said anything about "instead" of bookmark.

yes I know. Should say, another approach :)

[fumei]

OK. DOCVARIABLES are variables stored in the document. They are strings. Their sole purposes is to do exactly what you want to do - store a value from code, after the procedure that set that value has terminated.

Normally, when a procedure terminates any variables in that procedure are destroyed. There are other factors, and you should read up onSCOPE.

So. When the userform is loaded (in memory) you can write values of controls, or other variables, into variables that are in the document, not the userform. Thus, when you open the userform again, it is possible to get those values from the document.

In the userform somewhere:
ActiveDocument.Variables("Chk1").Value = 1
ActiveDocument.Variables("Chk2").Value = 1
ActiveDocument.Variables("Chk3").Value = 0
ActiveDocument.Variables("Chk4").Value = 0
YOU are defining 1 to mean True, and 0 to mean False. DOCVARIABLE, to repeat, are strings, not boolean. So you can not store a boolean True/False. You could as easily make it:
ActiveDocument.Variables("Chk1").Value = "True"
ActiveDocument.Variables("Chk2").Value = "True"
ActiveDocument.Variables("Chk3").Value = "False"
ActiveDocument.Variables("Chk4").Value = "False"
It does not matter. the point being is you decide what you are going to use to determine the later state of the checkboxes. Like this:
Sub UserForm1_Initialize()
If ActiveDocument.Variables("Chk1").Value = "True" Then
Me.Checkbox1.Value = True
End If
' etc etc for the others.

Two points to remember.

1. checkboxes are independent of each other. There is no connection between checkbox1 being checked and checkbox2 being checked, or unchecked. This is unlike optionbuttons. Generally, if OptionButton1 is True, then OptionButton2 MUST BE false. Thus, for checkboxes, you must test each individually.
[/vba]

2. the above code assumes that you have NOT changed the default state of the checkboxes. The default state is False, unchecked. When the userform opens if the default is False, all the checkboxes are unchecked. Thus you do not need an Else:
If ActiveDocument.Variables("Chk1").Value = "True" Then
Me.Checkbox1.Value = True
Else
Me.Checkbox1.Value = False
End If
Are you following?

[Johnosley]

Are you following?

Yes. During the week-end I did some trial and error using your recomandation and finally my userform is working !

And I did a search on docvariable and found a very good article (http://gregmaxey.mvps.org/Interactive_UserForms.htm) that was very helpful for me to be able to build my userform.

I still have some issues but I have to dig into the VBA forum first.

thanks again for your help Gerry