If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help merging worksheets with more than 255 characters...
Hi,
I am working on a project which requires mail merging an Excel spreadsheet with several fields that contain more than 255 characters. I am using Word 2003 on an XP machine. The results from these merge fields are truncated. I found a workaround for this problem in the online Microsoft Word Support file, WD2000: Field Text Truncated When Merging or Inserting Database. It recommended I use Dynamic Data Exchange (DDE) instead of Open Database Connectivity (ODBC). However, this workaround doesn’t solve my problem, the fields remain truncated. Are there any other alternatives to resolve this problem? It seems converting my data to CSV or Tab delimited causes the data to be truncated as well. I remember reading somewhere that I could convert my Excel spreadsheet to an Access database which doesn't care about this 255 character limitation. However, I didn't have much success importing these fields in Access. Thanks in advance for any assistance you can provide. Regards, Greg |
#2
|
|||
|
|||
Help merging worksheets with more than 255 characters...
At least some aspects of this problem are described at
http://tips.pjmsn.me.uk/t0003.htm, but as you will see it is almost impossible to create a catch-all solution based on Excel unless (perhaps) you can control what is in the first 8 rows of the sheet. If you /have to/ start from an Excel sheet then importing into Access will probably require automation of Excel (you can actually create a linked table in Access that gets its data from Excel, but that will suffer from the same problems as Word does because it also uses ODBC/OLEDB to get the data). But if you are going to automate Excel, I would consider transferring the data to a table in a Word document and using that as your data source (or perhaps trying to "fix" the data in the first 8 rows). Peter Jamieson http://tips.pjmsn.me.uk Greg wrote: Hi, I am working on a project which requires mail merging an Excel spreadsheet with several fields that contain more than 255 characters. I am using Word 2003 on an XP machine. The results from these merge fields are truncated. I found a workaround for this problem in the online Microsoft Word Support file, WD2000: Field Text Truncated When Merging or Inserting Database. It recommended I use Dynamic Data Exchange (DDE) instead of Open Database Connectivity (ODBC). However, this workaround doesn’t solve my problem, the fields remain truncated. Are there any other alternatives to resolve this problem? It seems converting my data to CSV or Tab delimited causes the data to be truncated as well. I remember reading somewhere that I could convert my Excel spreadsheet to an Access database which doesn't care about this 255 character limitation. However, I didn't have much success importing these fields in Access. Thanks in advance for any assistance you can provide. Regards, Greg |
#3
|
|||
|
|||
Help merging worksheets with more than 255 characters...
Hi,
You might get a more satisfactory answer in one of the Word newsgroups, just a thought, Cheers, Shane Devenshire "Greg" wrote: Hi, I am working on a project which requires mail merging an Excel spreadsheet with several fields that contain more than 255 characters. I am using Word 2003 on an XP machine. The results from these merge fields are truncated. I found a workaround for this problem in the online Microsoft Word Support file, WD2000: Field Text Truncated When Merging or Inserting Database. It recommended I use Dynamic Data Exchange (DDE) instead of Open Database Connectivity (ODBC). However, this workaround doesn’t solve my problem, the fields remain truncated. Are there any other alternatives to resolve this problem? It seems converting my data to CSV or Tab delimited causes the data to be truncated as well. I remember reading somewhere that I could convert my Excel spreadsheet to an Access database which doesn't care about this 255 character limitation. However, I didn't have much success importing these fields in Access. Thanks in advance for any assistance you can provide. Regards, Greg |
#4
|
|||
|
|||
Help merging worksheets with more than 255 characters...
Note that the message was cross-posted to three NGs, two of which are for
Word. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA "Shane Devenshire" wrote in message ... Hi, You might get a more satisfactory answer in one of the Word newsgroups, just a thought, Cheers, Shane Devenshire "Greg" wrote: Hi, I am working on a project which requires mail merging an Excel spreadsheet with several fields that contain more than 255 characters. I am using Word 2003 on an XP machine. The results from these merge fields are truncated. I found a workaround for this problem in the online Microsoft Word Support file, WD2000: Field Text Truncated When Merging or Inserting Database. It recommended I use Dynamic Data Exchange (DDE) instead of Open Database Connectivity (ODBC). However, this workaround doesn't solve my problem, the fields remain truncated. Are there any other alternatives to resolve this problem? It seems converting my data to CSV or Tab delimited causes the data to be truncated as well. I remember reading somewhere that I could convert my Excel spreadsheet to an Access database which doesn't care about this 255 character limitation. However, I didn't have much success importing these fields in Access. Thanks in advance for any assistance you can provide. Regards, Greg |
#5
|
|||
|
|||
Help merging worksheets with more than 255 characters...
Hello Peter,
Thanks for your response it was very helpful in figuring out how to create a workaround for my particular problem! After reading through the "Word: Problems with Excel data sources" http://tips.pjmsn.me.uk/ t0003.htm weblink I had success importing my Excel document into Access. Key factors contributing to my success include keeping Excel Open while I Copy/Paste into Word. This created a Word table file which I then saved. Next I was able to import this saved Word document into Access as a Table without any errors. However, I was surprised to learn once my information was safely inside Access, the order of my records had changed. I simply created a new Access query sorting on my required fields and used this query to perform a new Mail Merge in Word. The merged results contains all information in every field including those larger than 255. Kind regards, Greg On Nov 14, 3:00*am, Peter Jamieson wrote: At least some aspects of this problem are described athttp://tips.pjmsn.me.uk/t0003.htm, but as you will see it is almost impossible to create a catch-all solution based on Excel unless (perhaps) you can control what is in the first 8 rows of the sheet. If you /have to/ start from an Excel sheet then importing into Access will probably require automation of Excel (you can actually create a linked table in Access that gets its data from Excel, but that will suffer from the same problems as Word does because it also uses ODBC/OLEDB to get the data). But if you are going to automate Excel, I would consider transferring the data to a table in a Word document and using that as your data source (or perhaps trying to "fix" the data in the first 8 rows). Peter Jamieson http://tips.pjmsn.me.uk Greg wrote: Hi, I am working on a project which requires mail merging an Excel spreadsheet with several fields that contain more than 255 characters. I am using Word 2003 on an XP machine. The results from these merge fields are truncated. I found a workaround for this problem in the online Microsoft Word Support file, WD2000: Field Text Truncated When Merging or Inserting Database. It recommended I use Dynamic Data Exchange (DDE) instead of Open Database Connectivity (ODBC). However, this workaround doesn’t solve my problem, the fields remain truncated. Are there any other alternatives to resolve this problem? It seems converting my data to CSV or Tab delimited causes the data to be truncated as well. I remember reading somewhere that I could convert my Excel spreadsheet to an Access database which doesn't care about this 255 character limitation. However, I didn't have much success importing these fields in Access. Thanks in advance for any assistance you can provide. Regards, Greg |
#6
|
|||
|
|||
Help merging worksheets with more than 255 characters...
Hi Greg,
Thanks for the feedback. FWIW I was able to copy/paste my Excel rows into the "spreadsheet view" of a new Access table directly - in Access 2007 it seems to want to use an AutoIncrement field called ID as the first column, but otherwise the process worked reasonably smoothly once you'd worked out exactly what to do. So that might be a viable manual route that cuts out the middle man (Word). However, a. haven't done that with earlier versions of Access for a while b. don't know if it will retain 255 strings in all cases c. I wouldn't really want to try automating that! Peter Jamieson http://tips.pjmsn.me.uk Greg wrote: Hello Peter, Thanks for your response it was very helpful in figuring out how to create a workaround for my particular problem! After reading through the "Word: Problems with Excel data sources" http://tips.pjmsn.me.uk/ t0003.htm weblink I had success importing my Excel document into Access. Key factors contributing to my success include keeping Excel Open while I Copy/Paste into Word. This created a Word table file which I then saved. Next I was able to import this saved Word document into Access as a Table without any errors. However, I was surprised to learn once my information was safely inside Access, the order of my records had changed. I simply created a new Access query sorting on my required fields and used this query to perform a new Mail Merge in Word. The merged results contains all information in every field including those larger than 255. Kind regards, Greg On Nov 14, 3:00 am, Peter Jamieson wrote: At least some aspects of this problem are described athttp://tips.pjmsn.me.uk/t0003.htm, but as you will see it is almost impossible to create a catch-all solution based on Excel unless (perhaps) you can control what is in the first 8 rows of the sheet. If you /have to/ start from an Excel sheet then importing into Access will probably require automation of Excel (you can actually create a linked table in Access that gets its data from Excel, but that will suffer from the same problems as Word does because it also uses ODBC/OLEDB to get the data). But if you are going to automate Excel, I would consider transferring the data to a table in a Word document and using that as your data source (or perhaps trying to "fix" the data in the first 8 rows). Peter Jamieson http://tips.pjmsn.me.uk Greg wrote: Hi, I am working on a project which requires mail merging an Excel spreadsheet with several fields that contain more than 255 characters. I am using Word 2003 on an XP machine. The results from these merge fields are truncated. I found a workaround for this problem in the online Microsoft Word Support file, WD2000: Field Text Truncated When Merging or Inserting Database. It recommended I use Dynamic Data Exchange (DDE) instead of Open Database Connectivity (ODBC). However, this workaround doesn’t solve my problem, the fields remain truncated. Are there any other alternatives to resolve this problem? It seems converting my data to CSV or Tab delimited causes the data to be truncated as well. I remember reading somewhere that I could convert my Excel spreadsheet to an Access database which doesn't care about this 255 character limitation. However, I didn't have much success importing these fields in Access. Thanks in advance for any assistance you can provide. Regards, Greg |
Thread Tools | |
Display Modes | |
|
|