![]() In the Production records.xlsm file we will now link the macro to the button on the control sheet Save the file as a Word File Shipping Template.docx (not as a Word Template *.dotx) You can view the field values using the Preview Results Button, see below You can format the fields as required, select the entire field and change the font, colors etc to suit. The other fields Destination, Form and Date have already been pre-entered and are shown in Blue Insert Fields as per the following table and highlights above: Locationįield Name (from Production Records.xlsm) Move to the 4 missing Field Locations as per the following table and insert the Field Names using the ![]() Navigate to the Production Records.xlsm file after which it will prompt you for the table which in our case is Transfer$ ie: the Sheet Name with a $ sign at the end (as discussed above) Goto Mailings, Select Recipients, Use Existing List… Use mail merge to open the data source ( Production records.xlsm) Leave gaps for the fields which we will add next. Now setup the file in word with all the text graphics, lines colors etc required for your form/report. If it didn’t prompt you above or you answered No to the “Opening this file will run the following SQL File …” prompt we will connect again later anyway. It will then prompt you for the Data Table which in our case is Transfer$ ie: the Sheet Name with a $ sign at the end You can either accept that and then follow the links to connect the file to the Production records.xlsm file “Opening this file will run the following SQL File …” If this is the first time you have opened the example file it may prompt you Open the example file ( Shipping Template.docx) Leave gaps where your fields values will go. Setup in word a file which will be used as a template for the import. In the sample file I have made a simple retrieval of a matching records and associated fields, but the Transfer sheet could have just as easily sumarised multiple rows of data from your data source. The second row uses an Index(Match( )) combination to retrieve the relevant records from the Data sheet. Notice on the Transfer sheet that the Top Row is a list of field names ![]() The format or layout doesn’t matter as this is controlled in Word. The order of the fields isn’t important as the field names are used for the transfer not the order.Īlso you don’t have to use all the fields in Word, but if the Field isn’t made here you can’t retrieve it later. You need to setup sufficient fields to ensure that all records required in Word are setup or retrieved. The cells will contain sufficient formulas to extract the relevant records from the Data sheet using the Data validation on the Control Sheet. Row 2: Has a list of the records which will be transferred to Word. Row 1: Has a list of field names, These will be used in Word later so use something meaningful. The Produce Word Report button is linked to the MergeMe VBA subroutine. You can make yours as simple or as complex as you need to extract the data from your data source. In the example above there is simply a Data Validation cell which is linked to a list of shipment numbers and a Button to run the reports VBA subroutine. It can be as simple or as complex as you need to make it. The control sheet is a simple data validation or selection tool and a button which will run a macro. + Data: The database Setup a Control Sheet + Transfer: The transfer sheet, the entry or summation here will be transferred to word + Control: The master sheet which allows selection of your filter or summation criteria and a button to execute a macro Notice that there are 3 worksheets in the workbook: Open the example workbook (Production records.xlsm from the above links) or your own data file. The 1997/03 Sample has been tested on Office XP (and I make no claim that it will work in prior versions but it might/should) The 2007/10 Sample has been tested on both Office 2007 & 2010 This tutorial will only be using the 2007/10 files as examples but feel free to follow along if you are using previous versions. This process will be explained step by step with the help of a worked example: 2007/10 Sample or 1997/03 Sample + A Word file (to be used as a template, not to be confused with a Word template) In these cases although you may be able to setup an Excel file to look exactly like its Word equivalent. Some organisations will only accept files in Word format and may have specific formats which are used internationally (International shipping I’m looking at you!) This post will detail the process of establishing a simple database in excel and then linking that to a Standard Report in word and saving the data as a new Word file. Printing Excel Reports via a Word Document
0 Comments
Leave a Reply. |