Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Inconsistencies Word/Excel
My source is Excel, in which I have 4 columns set up. In 1 of my many Rows,
all 4 columns say 'n/a', instead of a number in each. When I merge these columns into the fields in Word, I get '0.00 .00 n/a n/a' (4 fields). When the fields in Excel have a number, instead of 'n/a', everything works just perfectly. All columns in Excel are formatted exactly the same. All fields in Word are formatted the same with the only difference being the column header it is addressing. And keep in mind that when there are numbers in Excel, it all works perfectly. Any ideas for this big puzzle?? -- DM |
#2
|
|||
|
|||
Assuming you are using Word 2003/2002, the easiest way to get Word to fetch
the Excel data "as seen" is usually to change the connection method from the default (OLEDB) to the old method (DDE). To do that, check Word Tools|Options|General|Confirm convesions at open, go through the connection process again, and select the DDE option when it is offered. However, a. it doesn't work in all cases b. it starts a copy of Excel (i.e. you must have Excel on your system - OLEDB can just open the workbook without Excel), which complicates things, particularly if you need to distribute your solution. Another possibility is to export the data in some way, e.g. if you have 63 columns or less, you may find that you can copy/paste into a new Word document that you can use as the data source. Partly for the benefit of anyone else who may be reading this article... This inconsistency is /probably/ caused by the way OLEDB (and ODBC) decides what type of data is in each column in an Excel sheet. Roughly speaking (I do not know the whole story here): a. Both OLEDB and ODBC use the (Access) Jet engine to get data from Excel b. In the Windows registry there are some entries that affect the way Jet processes Excel data, under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel c. The TypeGuessRows values specifies how many Excel rows Jet will look at to try to determine the type of data. The default is 25. If you set it to 0, Jet should scan all the rows and Word's behaviour will change. But you cannot do this on a file-by-file basis. d. The ImportMixedTypes value - this can either be "Text" or "Majority Type". If it is "Majority Type", Jet will, for example, decide that if 13 cells out of the 25 it looks at in a column are numeric, then the column will be numeric and non-numeric values will be converted ("cast") to numeric values if possible, and otherwise returned as nulls. If there are an equal number of numeric and non-numeric cells, the column will be numeric. If the ImportMixedTypes value is set to "Text", when Jet finds mixed types, it chooses the text type and converts everything to that. NB, in particular, this will truncate long memo text to 255 characters. A possible exception is that where the column contains 2 or 3 date/time types, Jet may treat the column as date/time. e. When a program such as Word connects via OLEDB, it can pass information in a connection string that can override some registry settings for the current connection. For Excel, it is possible to specify "Extended Properties", e.g. Extended Properties='Excel 8.0;HDR=Yes;IMEX=1' The value of HDR is to do with whether or not there is a header row. "IMEX" stands for "IMPORT/EXPORT" - it can be 0 (IMPORT), 1 (EXPORT) or 2 (LINKED, i.e. the sheet is updatable) . If the value is 0 or 2, the "Majority Type" approach is used. If the value is 1, I believe that Jet honours the value of ImportMixedTypes in the registry. So you can probably get Word to change the way it handles mixed columns in Excel as long as you use VBA to issue an OpenDataSource call with the correct connection string (or maybe you would have to go via a .odc or .udl file) Anyway, in your situation, what I would guess is that your first and second columns probably have numeric data in the first 25 rows of the spreadsheet and your third and fourth columns probably have another n/a value in the first 25 rows. I would be interested to know if that is the case. If that isn't correct, I have no other explanation. Peter Jamieson "Duane M" wrote in message ... My source is Excel, in which I have 4 columns set up. In 1 of my many Rows, all 4 columns say 'n/a', instead of a number in each. When I merge these columns into the fields in Word, I get '0.00 .00 n/a n/a' (4 fields). When the fields in Excel have a number, instead of 'n/a', everything works just perfectly. All columns in Excel are formatted exactly the same. All fields in Word are formatted the same with the only difference being the column header it is addressing. And keep in mind that when there are numbers in Excel, it all works perfectly. Any ideas for this big puzzle?? -- DM |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unwanted bolding in merge using Word/Excel 2000 | Mailmerge | |||
13 numbers added to Word/Excel mail merge field | Mailmerge | |||
Can I download microsoft word/excel for free? | New Users |