Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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 ?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='jim_from_oz' post='762106' date='26-Feb-09 20:24']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.[/quote]
    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?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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 ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jim_from_oz' post='762356' date='28-Feb-09 02:28'][/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.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jim_from_oz' post='762377' date='28-Feb-09 05:33']...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...[/quote]
    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?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I think you shouldn't use mail merge at all, but populate the document entirely through VBA.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think I can help you with this. I hope someone else will have a helpful suggestion.

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='jim_from_oz' post='762560' date='28-Feb-2009 20:03']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.[/quote]
    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.)

  12. #12
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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.

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='jim_from_oz' post='762730' date='01-Mar-2009 21:06']... 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 ![/quote]
    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).

  14. #14
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    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.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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:\MyFolder\MyWorkbook.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:\MyFolder\MyWorkbook.xls")
    ...
    xlWbk.Save
    ...
    xlApp.Quit

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •