PDA

View Full Version : Solved: How to export bookmarks from word to excel



Johnosley
07-21-2010, 02:20 PM
Hi,

My team is using a word document to track some data related to different project. The list of project is extensive and we need for an other purpose to upload all data written in the word doc into an excel spreadsheet. We are using word mainly for the track changes function.

I did some search and finally the most promising solution seems to use "userform" and bookmark in word (and build a template rather than a form). See example attached. Keep in mind, that each word file has a different name and location in our server when saving.

The word doc will use a userform to complete the document and it will allow other user to write some comments and/or track changes. My previous thought was to use the form function but using that it seems that you can enable the track changes function because the word doc is protected.

The excel spreadsheet attached shows an example on how I need to upload the data into different spreadsheet: the project 1 will be uploaded into the spreadsheet "project1" (I would like to do it with a button for example), project2.doc into project2 tab etc.

I was able to find different solution using VBA to link the excel data to the word doc but not the opposite !

Hope it was clear. Let me know if you have any question and for sure, any help will be awesome. Thanks

John.

Tinbendr
07-21-2010, 07:20 PM
Here's my effort.

This lets you choose the file, preview the data before importing and select the sheet name to import.

Hope this helps.

David

Johnosley
07-22-2010, 07:35 AM
Wow ! awesome.

Thank you so much. You did much more than I expected. I will continue to build my word template and excel summary and if I have any question about your code I'll let you know.

I'm a newbie in VBA but I think I understood what you did but need to do some research to make sure I understand all parameter.

thanks again
John

Johnosley
07-26-2010, 02:13 PM
Here's my effort.

This lets you choose the file, preview the data before importing and select the sheet name to import.

Hope this helps.

David

I used your code to my project and it worked fine. But now, I'm using 2 sort of bookmarks (enclosed bookmark such as my initial example) and placeholder bookmarks.

I tried to modify the code to take into account the placeholder bookmarks but it's not working at all. The macro is not returning any data.

Any thoughts?

Tinbendr
07-26-2010, 08:54 PM
Basically you find the placeholder bookmark, expand the range until some delimiter is found. (end of sentence, next bookmark, etc.) Then you parse out the data you need.

You're going to have to be more specific as to how the bookmarks are used. Another sample document is always helpful. Until then, I would just be guessing.

'Example One
Set wRng = oDoc.Bookmarks("PhBkmrk1").Range
wRng.Expand WdSentence

'Example Two
Dim BM1 as Long
Dim BM2 as Long

BM1 = oDoc.Bookmark("BM1").Range.Start
BM2 = oDoc.Bookmark("BM2").Range.End
'wRng holds the range between the two bookmarks.
Set wRng = oDoc.Range(BM1, BM2)

Johnosley
07-27-2010, 07:57 AM
[quote=Tinbendr]Basically you find the placeholder bookmark, expand the range until some delimiter is found. (end of sentence, next bookmark, etc.) Then you parse out the data you need.

You're going to have to be more specific as to how the bookmarks are used. Another sample document is always helpful. Until then, I would just be guessing.
[/quote

Thanks Tinbendr. I will start a new discussion (I guess in word forum) with the version of my doc I have so far (including the userform I developed). I'm still wondering if the bookmark is the best way to do what I need to do.

But see the attached I made. May be you will have some thoughts. Thanks

Tinbendr
07-27-2010, 10:02 AM
Since you're using tables, you don't need bookmarks, just the table/cell reference. For Example, Item 12.
Sub temp()
Dim Ans12 As String
Ans12 = ActiveDocument.Tables(6).Cell(3, 4).Range.Text
'Trim the end-of-cell marker.
Ans12 = Left(Ans12, Len(Ans12) - 1)
MsgBox Ans12
End Sub
Had you given this table info first, my first program would be very different.

But if you're intending other people to fill in this form, I'd go with formfields. Then you can protect it and call the specific formfield by name.

There are a dozen ways to accomplish this task, but we need details, else we're just chasing our tail.

Johnosley
07-27-2010, 12:06 PM
Had you given this table info first, my first program would be very different.

I know. But experience is everything. I tested my first though and then came up with something different and realized that may be I could (with help :)) do something better and easier.


But if you're intending other people to fill in this form, I'd go with formfields. Then you can protect it and call the specific formfield by name.

That was my first idea. But with the form the key is the review mode in word (track changes and comments). The document before I will be able to upload data to excel has to be reviewed and commented by many people. And it's always the case for each document.

If the document is protected nobody can track changes.


There are a dozen ways to accomplish this task, but we need details, else we're just chasing our tail.

Yes I realized that.

Ans12 = Left(Ans12, Len(Ans12) - 1)

Why the "-1" is important in this situation ?

You can see my other discussion here (http://www.vbaexpress.com/forum/showthread.php?p=220719#post220719).

I will evaluate your code and the one I did before using bookmarks and userform.

Johnosley
07-28-2010, 09:04 AM
I tried to populate your previous code (from import word data.xls) using your last example code.

But I got an error : Word caused a problem. Object doesn't support this property or method.

I changed that in your previous code

ufDataForm.lblProjectName = oDoc.Bookmarks("line200").Range.Text

By that

ufDataForm.lblProjectName = oDoc.ActiveDocument.Tables(1).Cell(1, 3).Range.Text

I thought this project would be easy ! Nope

Tinbendr
07-28-2010, 09:54 AM
The document before I will be able to upload data to excel has to be reviewed and commented by many people. And it's always the case for each document.Ahh, ok, then scrap the formfields idea.

Ans12 = Left(Ans12, Len(Ans12) - 1)


Why the "-1" is important in this situation ?
Read the comment above that line.
'Remove the end-of-cell marker.

I'm uncertain as to why you getting the error. If the tables are different than your uploaded example, it will cause that error.

I think you're right though, we should move this to the Word section, or the New Projects section. You can always post a link back to this.

Johnosley
07-28-2010, 10:02 AM
I'm uncertain as to why you getting the error. If the tables are different than your uploaded example, it will cause that error.

but at least for you the syntax is correct?

Johnosley
07-28-2010, 03:31 PM
Finally I removed the ActiveDocument and it works. So now the code is:

ufDataForm.lblProjectName = oDoc.Tables(1).Cell(1, 3).Range.Text
It works but would like to know why !

I attached your initial import word data.xls with that change but now I have an error due to the "userform" if I can say that.

I removed some labels from the userform to show a selected one (project name in my example). But that generates an error if I would like to import more than 1 data !

I'm trying to understand why I have a such error. But I'm stuck with the ufDataForm. can't figure !

And if I would like to remove the last character for each text string should I build a sub procedure for each data I would like to parse ?

At the beginning of the week I thought it will done easily ! nope :)

kissabajsa
11-13-2012, 03:45 AM
OT
How do i see attachments? Read i needed to make first post. That didnt help though. Dont want to spam the forum to get past a number of post either.
Tried to search but didnt find any answers.

JonesZoid
11-14-2014, 03:44 PM
This thread has been very helpful - thanks to all.