Automatically bookmark cells in word

[hogg]

Hey all!

I'm after a VBA code to automatically bookmark each cell in a table in word. The bookmark will vary based on its position in the table (relative to the column and row). The format needs to be:

_____(Column1) (Column2)
(row1) - "1c5" - "1d5"
(row2) - "1c6" - "1d6"
(row3) - "1c7" - "1d7"

I've tried a few things but I can't get it to work effectively, any ideas?

Thanks for any help.

[lucas]

Try something like this to add the bookmark temp to row 1 and column 2 of table 1.

ActiveDocument.Bookmarks.Add Name:="temp", Range:=ActiveDocument.Tables(1).Cell(1, 2)

[fumei]

"The bookmark will vary based on its position in the table "

What does this mean? A bookmark is simply the numbers of a range. A Start, and and End.

"I'm after a VBA code to automatically bookmark each cell in a table in word."

Demo attached which does exactly this, through the following code.

Sub MakeBM()
Dim aCell As Cell
For Each aCell In ActiveDocument.Tables(1).Range.Cells
ActiveDocument.Bookmarks.Add _
Name:="Cell" & aCell.RowIndex & _
"_" & aCell.ColumnIndex, _
Range:=aCell.Range
Next
End Sub


Say (as it is in the demo doc), a table with:

Cell 1,1 Cell 1,2 Cell 1,3
Cell 2,1 Cell 2,2 Cell 2,3
Cell 3,1 Cell 3,2 Cell 3,3

the code above will create bookmarks for "each cell". They will named:

Cell1_1
Cell1_2
Cell1_3
Cell2_1
Cell2_2
....

I have to ask why you need to bookmark each cell. It may be - depending on what your actual situation is - far easier to get information out of any given cell by simply making a table object and using its methods and properties.

In other words, instead of having 9 separate bookmarks (as in the demo doc attached), you could make ONE table object (of the table) and extracting information about any cell you want.

You can fire the code by clicking "Make Cell Bookmarks" on the toolbar. Just to fully demonstrate, I suggest you check Bookmarks (Insert > Bookmarks) and see there are none. then click "Make Cell Bookmarks" and check again.

All the bookmarks will be there.

[hogg]

Thanks for the help guys.

I can now successfully populate each cell with a bookmark. The purpose of this is that each bookmark links to a named cell in an excel document. I can successfully merge the information from excel into word.

For presentation reasons I then split the tables into sections. If i re-sync the documents the data updates and the formatting remains the same (as intended).

However, and this is the only problem I have left before I've finished this project, if I re-sync the documents the tables merge back to how they were before I split them!

Anyone know why it does this!?

[lucas]

Hi Hogg, I noticed you have started a new thread on how to deal with this problem. (splitting tables)

I would suggest that you give us a little more info here in this thread and I'm pretty sure there are easier ways to import your data from excel into word tables without bookmarking each cell and having the problems you are having with splitting tables.

What exactly are you trying to do?

[hogg]

Sorry Lucas, I thought I'd deal with them as two separate problems.

I'm just trying to import data from an excel file into a word doc. The data has to go to a predetermined location. I need to re-sync the files as the excel data changes. So I have the excel file with each required cell as a named range. These cells will then be exported to the relevant bookmarks in the word file.

I've attached three documents for review:
-The excel file (sample.xls)
-The word doc before the table split (SampleDoc.doc)
-The word file after the split (SampleDoc1.doc)

The excel file is currently pointing to the "SampleDoc1" (although the code will need slight modification to find the file on a different computer). When the merge is initiated from excel the table in word re-merges (or "unsplits" for some reason.

The trouble is I can't split the table before entering the bookmarks as the bookmark entry process is automated and splitting them first would mess up the numbering. This is a big job so the automation of bookmark names/named ranges is essential (there are 14 worksheets to link to one word doc!).

I hope my explanation is making some sense! I've been working on this for a while so i've probably got "too close" to the problem to see the solution (also 2 weeks ago I'd never even heard of VBA!).

Thanks!

[hogg]

Sample.xls

[hogg]

SampleDoc1.doc

[lucas]

Does this project coincide with the thread that you have started in the excel help forum "How to improve my code"

[hogg]

Yes it does, thats how I named the cells in excel

[lucas]

You should post a link to the excel thread and in the excel thread you should post a link to this thread. I'm pretty sure you will find that this is much easier to do than the method you are trying right now.

[hogg]

This thread relates to the thread in the excel forum - If I'd known what I was doing at the start I'd only have the one! Sorry chaps!

http://www.vbaexpress.com/forum/showthread.php?t=23445

[macropod]

Hi hogg,

Why do you need vba at all? You could simply Copy & paste the selected ranges into your Word document as separate tables, using Edit|Paste Special, checking the 'link' option and specifying whatever link format you'd prefer? That way, the Word data will update automatically any time the Excel data changes - you can lock the links (or unlink them) if necessary to prevent this.

Cheers

[hogg]

Thanks macropod,

Cut and paste special is a good solution to achieve what I need. However with 15 worksheets creating a document in excess of 100 pages got a bit confusing! I was trying to find a "better" solution but each one seems only to create new problems...

[Dave]

Hi all! Just thought that I would mention that the essence of Fumei's addition is still unanswered... why not just refer to the table cells directly and skip the bookmarks... thought that was the reason for using a table? Dave

[macropod]

Hi Dave,

Fumei's advice is certainly valid (and if I needed to pump data into table cells via vba that's certainly the way I'd do it), but the problem to be solved here seems to be one for which vba isn't needed anyway.