Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2007 customising fields in the data source
If I want to customise the fields in a data source (Word 2007) I can either
open the source as a Word table and make the changes there, or I can open the main document that the data source is attached to, and use Edit Recipients. However, if I have created the data source from scratch when using the Mail merge Wizard, it's saved as an .mdb file. Is there any way to open an .mdb file INDEPENDENTLY of a main document (because the same data source is used with multiple main doocuments) and WITHOUT opening it in Access? I tried clicking on Select Recipients on the Mailings tab (with no main document open) then Edit Recipient List, but this only gives me the Data Form. What I want is the Customise Columns button (which you get if the main doc that the data source is attached to is open). The View Source button, which I've used many times in older versions of Word to display the data source in a table, does appear on the Data Form using the method described above - but it's greyed out!) Thanks -- Teresa |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2007 customising fields in the data source
If you created this .mdb via Word Mailmerge, then the Customize Columns
button should be available when you try to edit the .mdb. Since it isn't, that suggests to me that Word may be recognising the ..mdb as an Access database but not as an Office Address List, which is simply a .mdb with a very specific structure. FWIW if you try to edit an OAL directly with Access, you may in any case damage its structure. I don't know of any other way to edit the OAL (and more specifically, to change the column names etc.) available out-of-the-box in Office. There could well be free database editing utilities that can do it easily but it's not an area I'm familiar with. What you may be able to do is create a new .mdb that that copies all the data from the existing one but is recognised as an OAL by Word. You can try the following macro - I have hardly had time to test it properly but it does seem to do the job here. There are some general instructions for setting up Word macros on Graham Mayor's site at http://www.gmayor.com/installing_macro.htm In this case you should a. close any Word mail merge main documents that are linked to the OAL b. make a copy of the OAL c. when you have copied the macro code into a module in the VB Editor, - ensure that you have put any "wrapped" lines of code back together - use the VB Editor's Tools-References menu option to locate the following 2 libraries and check them: - Microsoft ActiveX Data Objects 2.7 Library - Microsoft ADO Ext. 6.0 for DDL and Security (If you don't have libraries with those names, choose the closest, e.g. Microsoft ActiveX Data Objects 2.6 Library - in strSourceOAL, specify the pathname of the copy OAL you made in step (b) - in strTargetOAL, specify the pathname of the OAL you want to create. The .mdb must not already exist, but the folder it is in must already exist d. run the macro e. if all goes well, create a new mail merge main document, attach the new data source, and check that the data you expect is there. Then try editing it and see if the Customize Columns button is now available. If it is, then you can replace your old data source and continue. '---------------------------------------------- Sub CreateOalAndCopy() ' Peter Jamieson 2009. Needs testing and error management ' Specify the OLE DB Provider. Const strProvider = "Microsoft.ACE.OLEDB.12.0" ' In the following line, specify the full path ' name of the Office Address List .mdb you are ' trying to fix Const strSourceOAL = "c:\a\source.mdb" ' In this line, specify the full path name ' of a .mdb file. The folder must exist ' but the .mdb will be created Const strTargetOAL = "c:\a\target.mdb" Dim objCatalog As ADOX.Catalog Dim objProcedure As ADOX.Procedure Dim objTable As ADOX.Table Dim objTables As ADOX.tables Dim objView As ADOX.View Dim objViews As ADOX.views Dim objCommand As ADODB.Command Dim objParameter As ADODB.Parameter Dim strCommandText As String Set objCatalog = New ADOX.Catalog Set objCatalog.ActiveConnection = objCatalog.Create( _ "Provider='" & strProvider & "';" & _ "Data Source= '" & strTargetOAL & "';" & _ "Jet OLEDB:Engine Type=5;") Debug.Print objCatalog.ActiveConnection Set objCommand = New ADODB.Command Set objCommand.ActiveConnection = objCatalog.ActiveConnection ' Office_Address_List should be the only Table ' Create a copy of it strCommandText = " SELECT *" & _ " INTO [Office_Address_List]" & _ " FROM [Office_Address_List]" & _ " IN '" & strSourceOAL & "'" ' do it objCommand.CommandText = strCommandText objCommand.Execute ' Office Address List should be the only query. ' Create it objCommand.CommandText = _ " CREATE VIEW [Office Address List] AS" & _ " SELECT *" & _ " FROM [Office_Address_List]" objCommand.Execute 'objCatalog.views.Append "Office Address List", objCommand Set objCommand = Nothing Set objCatalog.ActiveConnection = Nothing Set objCatalog = Nothing End Sub '---------------------------------------------- Peter Jamieson http://tips.pjmsn.me.uk Teresa wrote: If I want to customise the fields in a data source (Word 2007) I can either open the source as a Word table and make the changes there, or I can open the main document that the data source is attached to, and use Edit Recipients. However, if I have created the data source from scratch when using the Mail merge Wizard, it's saved as an .mdb file. Is there any way to open an .mdb file INDEPENDENTLY of a main document (because the same data source is used with multiple main doocuments) and WITHOUT opening it in Access? I tried clicking on Select Recipients on the Mailings tab (with no main document open) then Edit Recipient List, but this only gives me the Data Form. What I want is the Customise Columns button (which you get if the main doc that the data source is attached to is open). The View Source button, which I've used many times in older versions of Word to display the data source in a table, does appear on the Data Form using the method described above - but it's greyed out!) Thanks |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2007 customising fields in the data source
If the INDEPENDENCE from a mail merge main document is not an absolute
criteria, start a new mail merge main document and attach the data source to it. I suspect that the view data source button will then be enabled and after customizing the fields (I assume that you mean the data in the fields, not the fields themselves), you can then discard the mail merge main document. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Teresa" wrote in message ... If I want to customise the fields in a data source (Word 2007) I can either open the source as a Word table and make the changes there, or I can open the main document that the data source is attached to, and use Edit Recipients. However, if I have created the data source from scratch when using the merge Wizard, it's saved as an .mdb file. Is there any way to open an .mdb file INDEPENDENTLY of a main document (because the same data source is used with multiple main doocuments) and WITHOUT opening it in Access? I tried clicking on Select Recipients on the Mailings tab (with no main document open) then Edit Recipient List, but this only gives me the Data Form. What I want is the Customise Columns button (which you get if the main doc that the data source is attached to is open). The View Source button, which I've used many times in older versions of Word to display the data source in a table, does appear on the Data Form using the method described above - but it's greyed out!) Thanks -- Teresa |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2007 making changes to a data source
Following on from previous post:
It seems that the editing facilities available after clicking 'Edit Recipients' during a merge) vary depending on *how* the data source was created and I'd be really grateful if someone could confirm this: -Data source is an Excel spreadsheet - New Entry & Find available; Delete Entry and Customise Columns greyed out. -Word table: none of these buttons are available - all I get is a little 'Data Form' to enter & locate records -Office Address list - New Entry, Find, Delete Entry, Customise Columns - all available. So does this mean that if I want to add and delete FIELDS from the data source I cannot rely on 'Customise Columns' being available, and may have no option but to open the original file in Excel or Word? Are the benefits to creating new data sources as a Word table in the first place, rather than during the merge - is this more flexible? Thanks |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2007 making changes to a data source
It seems that the editing facilities available after clicking 'Edit
Recipients' during a merge) vary depending on *how* the data source was created Broadly speaking, this is the case, and Word behaves the way you describe. if you want to delve into the detail, it's probably better to think in terms of: a. the format of the data source b. the method Word uses to connect to it (could be an internal text converter, external text converter, DDE, ODBC or OLE DB) Excel is in some ways a good example because it is possible to connect to Excel workbooks using an external text converter (although that is obsolete in Word 2007, the converter available with earlier versions of Word can be used in some cases). Also, Excel can save and work with the older .xls format (Excel 2003 and earlier) and the new .xlsx/.xlsm format (Excel 2007), and there can be differences in behaviour. For example, if you connect to Excel using DDE, which a lot of people end up doing to overcome formatting and other problems, Word lets you select the Edit option, but then you probably have to realise that the data source is open in Excel and you have to go and edit it there, rather than in the Word dialog box. And at that point you could in theory add a column or some rows. But here, I find that Word doesn't like that scenario much and seems reluctant to save. If you connect using OLE DB (which is the default method in Word 2007), as you say, in Word's dialog you can modify and add Excel records, but not delete them, and you cannot modify the columns. Further, Word may impose some editing constraints that Excel would not - e.g. if Word thinks a column is numeric, you won't be able to enter a date. There are other potential problems. for example, a cell in Excel can contain a formula that gets the cell value from elsewhere. In these cases, Word will probably flag an error if you try to alter the cell value. So does this mean that if I want to add and delete FIELDS from the data source I cannot rely on 'Customise Columns' being available Correct. As far as I know, you only get that option with "Office Address List" format sources. and may have no option but to open the original file in Excel or Word? Correct - or in Access if it's an Access data source, etc. etc. Are the benefits to creating new data sources as a Word table in the first place, rather than during the merge - is this more flexible? IMO there is no "perfect" data source for Word mailmerges. For one thing, I can't think of a single type of data source that does not have a drawback of some kind. But mostly, the choice of data source is a compromise between a number of factors, including: a. who's creating it, and what skills do they have? b. who's using it, and what skills do they have? c. how long does the data source need to last (is it a throwaway, or do you need to retain the data for years?) d. does other software need to use the same data? e. should the data source be updateable, and/or usable, by multiple users at the same time? f. if the file format breaks in some way, can you get the data out and/or recreate the file? g. limits on column count, column size, row count, and performance h. how much control over sorting/filtering might you need? ....and those are just a few of the considerations. Word table format does have a couple of advantages over most other types of data source: a. it's relatively easy to maintain multiline data in a Word cell. b. if you need to maintain formatting in the data source that survives the merge (e.g. colouring text), Word format is pretty much the only option, although even this relies on a "trick". On ther other hand, Word table format is limited to 64/65 columns, connection/merge can be slow, editing Word files with large numbers of columns /directly/ is not easy, and so on. Peter Jamieson http://tips.pjmsn.me.uk Sela wrote: Following on from previous post: It seems that the editing facilities available after clicking 'Edit Recipients' during a merge) vary depending on *how* the data source was created and I'd be really grateful if someone could confirm this: -Data source is an Excel spreadsheet - New Entry & Find available; Delete Entry and Customise Columns greyed out. -Word table: none of these buttons are available - all I get is a little 'Data Form' to enter & locate records -Office Address list - New Entry, Find, Delete Entry, Customise Columns - all available. So does this mean that if I want to add and delete FIELDS from the data source I cannot rely on 'Customise Columns' being available, and may have no option but to open the original file in Excel or Word? Are the benefits to creating new data sources as a Word table in the first place, rather than during the merge - is this more flexible? Thanks |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word 2007 making changes to a data source
Peter, thanks for this comprehensive response - it has really helped to
clarify things for me. "Peter Jamieson" wrote: It seems that the editing facilities available after clicking 'Edit Recipients' during a merge) vary depending on *how* the data source was created Broadly speaking, this is the case, and Word behaves the way you describe. if you want to delve into the detail, it's probably better to think in terms of: a. the format of the data source b. the method Word uses to connect to it (could be an internal text converter, external text converter, DDE, ODBC or OLE DB) Excel is in some ways a good example because it is possible to connect to Excel workbooks using an external text converter (although that is obsolete in Word 2007, the converter available with earlier versions of Word can be used in some cases). Also, Excel can save and work with the older .xls format (Excel 2003 and earlier) and the new .xlsx/.xlsm format (Excel 2007), and there can be differences in behaviour. For example, if you connect to Excel using DDE, which a lot of people end up doing to overcome formatting and other problems, Word lets you select the Edit option, but then you probably have to realise that the data source is open in Excel and you have to go and edit it there, rather than in the Word dialog box. And at that point you could in theory add a column or some rows. But here, I find that Word doesn't like that scenario much and seems reluctant to save. If you connect using OLE DB (which is the default method in Word 2007), as you say, in Word's dialog you can modify and add Excel records, but not delete them, and you cannot modify the columns. Further, Word may impose some editing constraints that Excel would not - e.g. if Word thinks a column is numeric, you won't be able to enter a date. There are other potential problems. for example, a cell in Excel can contain a formula that gets the cell value from elsewhere. In these cases, Word will probably flag an error if you try to alter the cell value. So does this mean that if I want to add and delete FIELDS from the data source I cannot rely on 'Customise Columns' being available Correct. As far as I know, you only get that option with "Office Address List" format sources. and may have no option but to open the original file in Excel or Word? Correct - or in Access if it's an Access data source, etc. etc. Are the benefits to creating new data sources as a Word table in the first place, rather than during the merge - is this more flexible? IMO there is no "perfect" data source for Word mailmerges. For one thing, I can't think of a single type of data source that does not have a drawback of some kind. But mostly, the choice of data source is a compromise between a number of factors, including: a. who's creating it, and what skills do they have? b. who's using it, and what skills do they have? c. how long does the data source need to last (is it a throwaway, or do you need to retain the data for years?) d. does other software need to use the same data? e. should the data source be updateable, and/or usable, by multiple users at the same time? f. if the file format breaks in some way, can you get the data out and/or recreate the file? g. limits on column count, column size, row count, and performance h. how much control over sorting/filtering might you need? ....and those are just a few of the considerations. Word table format does have a couple of advantages over most other types of data source: a. it's relatively easy to maintain multiline data in a Word cell. b. if you need to maintain formatting in the data source that survives the merge (e.g. colouring text), Word format is pretty much the only option, although even this relies on a "trick". On ther other hand, Word table format is limited to 64/65 columns, connection/merge can be slow, editing Word files with large numbers of columns /directly/ is not easy, and so on. Peter Jamieson http://tips.pjmsn.me.uk Sela wrote: Following on from previous post: It seems that the editing facilities available after clicking 'Edit Recipients' during a merge) vary depending on *how* the data source was created and I'd be really grateful if someone could confirm this: -Data source is an Excel spreadsheet - New Entry & Find available; Delete Entry and Customise Columns greyed out. -Word table: none of these buttons are available - all I get is a little 'Data Form' to enter & locate records -Office Address list - New Entry, Find, Delete Entry, Customise Columns - all available. So does this mean that if I want to add and delete FIELDS from the data source I cannot rely on 'Customise Columns' being available, and may have no option but to open the original file in Excel or Word? Are the benefits to creating new data sources as a Word table in the first place, rather than during the merge - is this more flexible? Thanks |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 2003 as Data Source: Using Word Doc Form Fields as Data Sourc | Mailmerge | |||
data source with 508 fields | Mailmerge | |||
No of fields in a Data Source | Mailmerge | |||
Why will mailmerge not use all the fields from data source? | Mailmerge | |||
How can I add data fields to a data source in Word Mail Merge? | Mailmerge |