Problem with bookmark in Word

Topic

New Reply

In an effort to release my client from dependence upon external assistance, I have set up a methodology where a number of Word templates with mailmerge fields can be copied and edited by the client to create letters and other documents. mailmerge is not too good with the concept of subforms and suchlike, so I have got round that by extracting the data from an Access database, copying it into an Excel spreadsheet and then copying the spreadsheet into the Word document. After Googling around and consulting several excellent forums (of which this one is paramount !) I have coded it to copy from the entire spreadsheet as a range into another range attached to a named bookmark in the Word document. So far. so good. The advice I received on how to create and locate the bookmark was to press Ctrl-F9, and write the name of the bookmark into the space between the curly brackets. I do this and save the file in a template directory. The operational program copies the template to a working directory and proceeds to do its business, copying the data into the work file. My problem is that by the time this happens, the bookmark appears to have disappeared. If I select Insert/Bookmark from the menu, it shows no existing bookmarks present. Can anyone advise me what is happening, and if appropriate, suggest another method for achieving my ends ?

Reply | Quote

Replies

The advice I received on how to create and locate the bookmark was to press Ctrl-F9, and write the name of the bookmark into the space between the curly brackets.

I think you have the instructions for something a bit different, perhaps how to create a REF field to copy the contents of a bookmark to a different part of the document?

To create a Bookmark, literally just a marker, in a Word document, you can use Insert > Bookmark (or pop up the same dialog using Ctrl+Shift+F5). Does that do what you need?

Reply | Quote

Common-sense would seem to dictate that if one is going to deposit external data into a Word document, a field would be the way to go. Unfortunately, when I do this, the mail-merge engine seems to want to take over and treat the field as falling within its area of operations. Because the data is not coming from the mail-merge data source, I get two eerrors, thus:

Error! Reference source not found.Error! Bookmark not defined.

Bookmarks apparently have a Range associated with them and I was trying to copy/paste from the worksheets Usedrange into the bookmarks range, but withoout much success. I am still trying to debug the process to find out exactly where it is falling over. Another option I read about was to simply jump to the bookmark and do a paste to paragraph before or paragraph after. However, as I have said, my main interest in this forum is holding the bookmark and making it visible to the client who can then write text around it.

One other thought. Could I assign a reference to the Excel spreadsheet in the bookmark (as REF) directly. Someone seems to have done this in another post to this forum. Then, perhaps the Mailmerge might ignore it if a reference is already there. Any ideas ?

Reply | Quote

How about putting the data in the data source for the mail merge? You could then use a merge field to get it into the Word document.

If the data are the same for every record in the data source, you’d have to repeat it in every record; this can easily be accomplished by a query in Access.

Reply | Quote

Hello, again, Hans. Pleased to have the benefit of your advice.

Unfortunately, your suggestion won’t work, because the Excel spredsheet I am trying to embed is of variable length. It consists of a number of running totals, each separated from the adjoining one by zero or more line-items. For your interest, I attach a sample which represents a quotation or a billing for a function. It shows some food extras between the base cost and the Total Food Cost and one non-food extra between the Total Food Cost and the Total Catering Cost. On top of these, you could have hire and delivery charges between the Total Catering Cost and the Total Function Cost. Finally, you could have prior payments and deposits listed between the Amount Payable and the Outstanding balance. This is why I felt I had to import it as a single item. The items in bold font are as they will appear in the final document.
One of the suggested methods was to use VBA code to write each cell of a Word table separately into the final document. However, this would mean that, looking at a template or editing it, the client would have no idea of where the cells were going to appear. Having written this, it occurs to me that I could write the stuff into a paragraph following a bookmark, provided that it was visible. Of course, this would bring up problems of registration (field widths, etc.)

Regards,

Jim

Reply | Quote

…because the Excel spredsheet I am trying to embed is of variable length…
…The items in bold font are as they will appear in the final document…

I’m confused now – the two quoted sentences seem to contradict each other: wouldn’t you always have the same number of bold items? Or should the line items appear in the document as well?

Reply | Quote

The bolded lines are subtotals. The appearance features are inherited from the client’s original documents (Access reports). In any given function (social occasion – not a VBA construct !), they will be separated by zero, one or more than one lines (not bolded) of items and services specified by the person/organisation who commissioned the function from my client.

A bit of the history driving this project may be of interest. The old system was a truly awful, unnormalised Access database, which required extensive programming to produce relatively simple reports and documents. Because the business is so dynamic with many short-term offerings, often seasonally based, the workload to install new menus etc. was huge – insupportable in fact. My new system normalises the old database but also includes a huge amount of metadata which drives intelligent behaviour in the front-of-house application. The client can edit the metadata and modify the offerings and their selectable components with great ease. The methodolgy under discussion for creating documents is an important component in freeing the client up from reliance on (costly) programming support for relatively trivial elements. The end-result of this present exercise will be a template with Word fields for company and client information (typical mail-merge stuff), plus a visible point in the page (a REF or bookmark) where the client can say “Aha! That is where the numbers go.” He can then lay out his text around this point. The only subsequent work for a programmer then is to produce new templates for a library of such which can be accessed by the client for any purpose.

Reply | Quote

I think you shouldn’t use mail merge at all, but populate the document entirely through VBA.

Reply | Quote

At a purely practical level, I would tend to agree with you. Mail merge sometimes appears to have a mind of its own. However, one problem is transparency. The users need to associate a selection query with the document they are generating and I cannot quite see how you can associate fields from a data source with locations in a document without using Mail Merge. If I can solve the problem of associating my Excel worksheet with a location (field or bookmark) in a document,I suppose I could do exactly the same with all of the other items such as address, greeting and so forth. In fact, one of the samples I picked up in my Googling does something very similar, writing each row of a spreadsheet into a paragraph of a Word document after jumping to a start location identified by a bookmark. It gets really messy if you need to format the items across the row, though. Actually, the method I am trying to use is based on a sample where the author was pasting a picture into a Word document.
It might be of assistance if I posted some of the code, so for your interest, if nothing else, I attach a Word document into which I have pasted the relevant functions from my VBA modules. It is work in progress of course, but I have got it working down to the point where Debug.Print “After Word file opened” is displayed. This is where i need to pin-point the location in the Word file.

I am using the preview function for development. The print function is identical except for the lines displaying or printing the data. My naming convention prefaces local variables with This and global ones with Current.

Regards,

Jim.

Reply | Quote

I don’t think I can help you with this. I hope someone else will have a helpful suggestion.

Reply | Quote

I have got it working down to the point where Debug.Print “After Word file opened” is displayed. This is where i need to pin-point the location in the Word file.

Does the Paste work? I think you might need to make “Bookmark” plural in this line, as you are retrieving an item from a collection:

Code:

Set wdBMark = wdDoc.Bookmarks("ExcelCostTable")

(Because I don’t do merges, I won’t try to assist with that part.)

Reply | Quote

Thanks for your interest, colleagues all.

Yes, the paste does work and I only need the one bookmark, because there is a single range being pasted into another single range. However, I am not quite done yet. The Lines from the Excel spreadsheet appear in the Word document OK, but there are two issues. One is that (sometimes) there are a number of blank rows at the bottom of the table. I used the UsedRange parameter to define the data to be copied, believing that it would encompass only those lines I had written into the worksheet. If this is not the case, I will need to define the copied range as an explicit number of lines (that is OK, my code for generating the data in the worksheet does it line by line anyway). Then Sadly, I am getting a error (“Error! Bookmark not defined.”) AFTER it has loaded the data in exactly the right place. A bit more debugging is ahead, it would appear. This may be simply another bookmark stuck in the document after an earlier trial – but then again, it might not !

I appreciate the interest and comments offered, even though there has (so far) been no cut-and-dried result. Th good thing is that this is a technological feature which will be written once and used many times, as it is largely parameter driven.

Reply | Quote

… Then Sadly, I am getting a error (“Error! Bookmark not defined.”) AFTER it has loaded the data in exactly the right place. A bit more debugging is ahead, it would appear. This may be simply another bookmark stuck in the document after an earlier trial – but then again, it might not !

Press Alt+F9 and look for a REF field that tries to copy the content of the bookmark. When you insert to the range of a bookmark, the bookmark often is destroyed, so that would explain why a REF field would return an error. Since you are displaying the entire content anyway, you probably do not want the REF field (it would simply duplicate the content).

Reply | Quote

Spot on. I found an old REF field which, as I mentioned earlier, I could not use because MailMerge wanted to hijack it. Thanks a lot. In case you think I am an absolute dill for not knowing my finger tools, I should explain that I recently retired after 20-odd years of developing enterprise databases (Informix, Oracle, Sybase) on Unix systems using dedicated database languages or languages such as C and C++. Microsoft stuff has until now been strictly “use only when absolutely necessary”. That is why I place such a high value on this forum and all you clever people !
If I might pass on to another MS programming issue in my current project, can anyone comment on the Visible property. If I am intending merely to print out stuff and then delete the files, do I need it ? The reason I ask, is that if I don’t, Excel runs as a process rather than an application and even though I use a Quit command, it still locks the files as being in use and sometimes persists after a reboot. In that case, I can only get rid of it after a total Power Off.

Reply | Quote

If you only need to manipulate a file using code, without intervention by the user, there is no need to make the application object visible.

A common reason for files remaining locked even after you quit the application is using unqualified references. Here is an example:

Dim xlApp As Excel.Application
Set xlApp = CreateObject(“Excel.Application”)
xlApp.Workbooks.Open “C:MyFolderMyWorkbook.xls”
…
ActiveWorkbook.Save
…
xlApp.Quit

The line in bold uses ActiveWorkbook without referring to xlApp. It may cause an extra instance of Excel to be created that keeps the workbook open even after xlApp has been closed. The bold line should have been

xlApp.ActiveWorkbook.Save

or even better, the code could have looked like this:

Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Set xlApp = CreateObject(“Excel.Application”)
Set xlWbk = xlApp.Workbooks.Open(“C:MyFolderMyWorkbook.xls”)
…
xlWbk.Save
…
xlApp.Quit

Reply | Quote

Many thanks again, Hans. As usual, you are right on the button. I originally wanted to save all files automatically as an archive, but my client decided that was not necessary. I now have two buttons, one to Review and one to Print. The Print button cleans up after itself invisibly, the Review exposes the file to view (not the original file but a copy usually called Document1 or Letter1). When closing, the user has the option to save the file in normal Word usage, but will usually not bother. However, I presume that I must still quit the Word App formally because it controls the original Template/mailmerge file.
One further question, if I may. To delete the working files, there are two options that I can see. One requires the document to be declared as a FileSystemObject, which is not what I am doing here (I think). The other is simply a Delete action on the application object (Microsoft support advice). However, when I try to do this, I am getting messages to the effect that “this action is not supported”. This happens at the Document level in Word and the Workbook level in Excel. Am I missing something here ? Would it be preferable to close the App and redeclare the file as a FileSystemObject in a separate cleanup operation.

Regards,

Jim,

Reply | Quote

To delete a file, you can use the Kill instruction:

Kill “C:DocumentsMyFile.doc”

This is not a Word (or Excel) instruction, it’s general Visual Basic.

You must close the document before deleting it, otherwise you’ll get an error message.

Reply | Quote

Thanks a lot, Hans. The KISS principle still rules ! I presume that I must also kill the App lurking in the process list as it may forbid the delete.

I have one more little problem (I hope!) in this project. Technically it is Excel but it has been thrown up in the course of this discussion. Feel free to move it to the Excel area if you think it appropriate.

Here is a code snippet:

Dim xlApp As Excel.Application, xlWkBook As Excel.Workbook, xlWkSheet As Excel.Worksheet
. . . .
Set xlApp = CreateObject(“Excel.Application”)
Set xlWkBook = xlApp.Workbooks.Open(ThisFileName)
Set xlWkSheet = xlWkBook.Sheets(1)
xlWkBook.Activate
Debug.Print “Create Style”
With ActiveWorkbook.Styles.Add(Name:=”ExcelCostTable”)
.Borders(xlEdgeTop).LineStyle = xlLineStyleNone
.Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
.Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
.Borders(xlEdgeRight).LineStyle = xlLineStyleNone
.Borders(xlInsideVertical).LineStyle = xlLineStyleNone
.Font = “Arial”
.Font.Size = 11
End With
Debug.Print “Style done”

Somewhere between the debug statements, the program bombs with the message

Error 462: The remote server machine does not exist or is unavailable

The code is a direct snip from a MS support site.

My program is running on a laptop which is connected to my ISP but not to any other network.

Can you advise ?

Regards,

Jim

Reply | Quote

In the line

With ActiveWorkbook.Styles.Add(Name:=”ExcelCostTable”)

ActiveWorkbook does *not* refer to the xlApp object, so it causes problems. You must use

With xlApp.ActiveWorkbook.Styles.Add(Name:=”ExcelCostTable”)

Moreover, you must use Font.Name to assign a font:

.Font.Name = “Arial”

Reply | Quote

I had already picked up the font error Hans, but I am pleased to have your advice re the ActiveWorkbook. As I understand from your post, if I had used xlWkBook in the line, that would have been OK, because I would be referring to a declared object which exists in its own right, whereas the ActiveWorkbook is really only a discriminator identifying one workbook among whatever workbooks happen to exist in xlApp. My involvement with object-oriented design began about twenty years ago when it was all new and at that time we were so alive to all of the ramifications. Now it has become so commonplace, there are endless opportunities for sloppy thinking !!
My project is now working quite nicely, thanks to you and your colleagues at Ask Woody, but I still have one tine little problem. If you look at the Borders button in Excel, you can expose a number of options for applying borders to cells. I would really like to put a line across the top edge of those cells containing subtotals, but (on my machine at least) there is no option for this. I can manually apply a border to the bottom of the preceding line which has the same effect, so this is not a showstopper. However, when I try and program it in VBa I get a peculiar effect. The thick (black) bold line I ask for comes out as a white line, visually wiping out the thin boundary line for the cell. When I transfer it to Word, it does not appear at all. I have noted that other Excel properties (font, weight, format, etc.) do translate into what appears to have beome a Word table, but not this one. Any thoughts, anyone ?
For what it is worth, all of the trouble you fellows have gone to to instruct me in this project has paid off in spades. My client has expressed a huge sense of liberation in not needing a programmer to generate his letters for him. Many thanks !

Reply | Quote

As I understand from your post, if I had used xlWkBook in the line, that would have been OK, because I would be referring to a declared object which exists in its own right, whereas the ActiveWorkbook is really only a discriminator identifying one workbook among whatever workbooks happen to exist in xlApp.

That’s exactly what I already pointed out in [post=”762968″]Post 762968[/post] higher up in this thread.

Reply | Quote