Trying to program word to copy from an excel file

Word should open first as the primary document, not a button in excel. Word can be made to merge with excel or access. Then, when word opens, word will open excel or access by itself and establish a DDE link. You have to use the merge toolbar in word. In my case, all my data are entered into excel. Word produces documents with the data from the excel database. You can print from one excel row by typing in that excel row number in the merge toolbar in the word doc; or, you can print a range by typing in the first and last excel row numbers in the toolbar in word. Although I could type some data directly into the word doc, I don't. Anything typed into one merged word template will appear on every page when it prints; so only static data should be in the word template, the text "Date" for instance, not the actual calendar date. The text "Date" would not change from one page to the next or day to day while the calendar date changes every day. That's why it's called a template. All the merge data in each excel row will merge and appear on each word page, one word page for each excel row. I have found that the word template can do anything I can imagine without limitations.

Automating Word from Excel is quite easy, as is populating a Word document. Perhaps the most robust way of populating the document is to create a series of custom document properties in the document and use DOCPROPERTY fields wherever you need to reference them. That way, your code never has to go looking through the document content for something that might not be there (ie you can re-purpose the document, using different combinations of DOCPROPERTY fields). And, if someone edits the document by deleting a DOCPROPERTY field, the original data are still in the custom document properties. Your DOCPROPERTY fields can even be combined with IF fields to vary the output in different places according to the value in a single DOCPROPERTY custom document property. All very clever and easy to code.

The basic automation code would look something like the code in the attachment. Simply extract the workbook and template to the same folder, then run the demo code in the workbook.

Okay, now I can see what you're trying to do with combo boxes and the button at the bottom of the excel worksheet. Sample files save time. Merging will work; but, it may be too complicated and cumbersome for your environment. All the data that merge with your word doc must be on the same row with headers above. Your data are all over the place in many rows and columns. That can be dealt with by adding two rows at the top and linking consecutive cells on that row to the sundry data that are scattered thoughout the file. Then word would merge the linked data on that one row in your main document. The linked cells can even span worksheets as long as they all end up in the first sheet on the same row with headers. I merged a few of them in the attached files. We are using different versions of word and excel; so, I had to convert your files to my version. It may not work on your version and it may be too complicated anyway. The "offset" cell was added to make it link correctly because of some problem in there. I doubt you will be able to use this; so, I'm not going to try to fix the offset problem. You're going to have to find your Merge Toolbar and toggle "View Merged Data". It may say something different in your version. Toggling back and forth will alternately display the links and the data.

Macropod's post up above may be the way to go but I don't understand how. You get to DOCPROPERTIES in word with <alt F9>

As I said, this probably won't meet your requirements; but, if you want to waste some time:

Put both files in the same folder on your desktop and they should find each other when you open word. Don't open excel. Word will (should) do that by itself. If if doesn't, the merge link is broken. If it is broken, you may get a dialog box that will allow you to navigate to the excel file and reestablish the link. It worked here for me with MS Office Pro 2003. I think your button at the bottom is a better way to go if you can figure it out. In my case, I batch run hundreds of merged documents at a time; so, a button is useless. If you do get this to work, changes in excel will not update in your doc unless you refresh by going to the next merged doc and back again. Click the forward arrow one time and the back arrow one time in the Merge Toolbar to refresh the doc.

See attached document. This one uses a series of LINK fields in the Word document to import the Excel data (ie no code required to populate the document). With this approach, all you need to do to complete the document's setup is to edit the field codes to point to wherever your workbook is stored. The editing is as simple as opening the document, pressing Alt-F9 and using Find/Replace to change "C:\\Users\\Turbo\\BCA\\BCA Main Form - help.xlsx" to whatever the actual path & filename is (note the '\\' used for the path separators). In this scenario, all that is required to update the content from the Excel workbook (once the basic automation is done) is:

Please Login or Register  to view this content.

The assumption for this is that the same workbook will be used as the basis for all reports. I suspect the real case will be that a separate workbook, but the same template will be used for all the reports. In that case, change the path for the field code to the template's own folder (it doesn't really matter what the path is, or even if there's a workbook there, provided the path & workbook name in all the fields is the same) and use automation code like the following:

Please Login or Register  to view this content.

You'll need to change "C:\Users\Turbo\BCA\BCATemplate.dotx" to whatever the correct template path & name are.

The only change you'll need to make to your workbook is with the BCA Matrix. That will need to be resized to fit on the Word page. I achieved this by reducing the font size to 11, changing the wide/narrow column widths to 22/3, making all the data rows the same height (eg 30) and replacing '/' with ' / ' (to assist word wrapping).

The advantage of the approach taken is that, if someone later decides the workbook layout needs to be changed, a programmer isn't needed to revise all the Word connections - anyone can edit the field code row/column references in the Word template.