|
|
Hi,
I am merging data from excel into a word document. I am getting data in
word that is showing "12:00AM", however, there is data in the excel sheet and
it is NOT a date and it is not blank - it is part of an address line, such as
"Deliver to Front Desk" or any kinds of words, but definately not a date and
not blank.
I have tried re-typing the data in excel, I have even put a " ' " in front
of it to show it is text - nothing gets rid of this . It doesn't do it on
every field or even every record on the same field, just some and seemingly
randomly.
Any idea how to fix this other than manually changing the data in the final
merged document?
Thanks ,
Onalee
|
|
It's possible that you have encountered a new problem that is not described
at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this
suggestion from that page on your Excel sheet (or one of the following
suggestions):
<<
1. Format a column as text via Data|Text to Columns...
If you have a mixture of numbers and texts in a column but numbers in the
first 8 rows, you may need to get the OLE DB provider to "see" the column as
a text column. However, selecting the column and using Format|Cells to set
the format as Text does not appear to be enough. One way that does appear to
work is
- select the column
- select the Data|Text to Columns... menu option
- click Next through the wizard until you reach Step 3 of 3, then select
Text as the Column Data Format.
[Quoted Text] >>
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com...
> Hi,
> I am merging data from excel into a word document. I am getting data in
> word that is showing "12:00AM", however, there is data in the excel sheet
> and
> it is NOT a date and it is not blank - it is part of an address line, such
> as
> "Deliver to Front Desk" or any kinds of words, but definately not a date
> and
> not blank.
>
> I have tried re-typing the data in excel, I have even put a " ' " in front
> of it to show it is text - nothing gets rid of this . It doesn't do it on
> every field or even every record on the same field, just some and
> seemingly
> randomly.
>
> Any idea how to fix this other than manually changing the data in the
> final
> merged document?
>
> Thanks ,
> Onalee
|
|
Hi,
Ok, I tried changing the format of the column as you describe, that didn't
help, so I changed the way word connects to excel to DDE as described in one
of the links. That fixed this problem but created a much larger problem.
I have 3 columns in a table in word. There is one field per column merged
from excel, the three fields are : Product 1, q1 and p1 . Now, where q1 is
supposed to be, it is putting the data from the field Product 1 and where p1
is supposed to be, it is putting the data from q1 and where Product 1 is
supposed to be it is putting the data from p1. It's got them all messed up
somehow in the mapping or something.
I think it was better the other way, at least that was just one field messed
up and only sometimes.
any other thoughts?
Thanks,
Onalee
"Peter Jamieson" wrote:
[Quoted Text] > It's possible that you have encountered a new problem that is not described > at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this > suggestion from that page on your Excel sheet (or one of the following > suggestions): > > << > 1. Format a column as text via Data|Text to Columns... > > If you have a mixture of numbers and texts in a column but numbers in the > first 8 rows, you may need to get the OLE DB provider to "see" the column as > a text column. However, selecting the column and using Format|Cells to set > the format as Text does not appear to be enough. One way that does appear to > work is > - select the column > - select the Data|Text to Columns... menu option > - click Next through the wizard until you reach Step 3 of 3, then select > Text as the Column Data Format. > >> > > > -- > Peter Jamieson > http://tips.pjmsn.me.uk> > "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... > > Hi, > > I am merging data from excel into a word document. I am getting data in > > word that is showing "12:00AM", however, there is data in the excel sheet > > and > > it is NOT a date and it is not blank - it is part of an address line, such > > as > > "Deliver to Front Desk" or any kinds of words, but definately not a date > > and > > not blank. > > > > I have tried re-typing the data in excel, I have even put a " ' " in front > > of it to show it is text - nothing gets rid of this . It doesn't do it on > > every field or even every record on the same field, just some and > > seemingly > > randomly. > > > > Any idea how to fix this other than manually changing the data in the > > final > > merged document? > > > > Thanks , > > Onalee > > |
|
It seems like you must have the mergefields in the wrong columns.
--
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
"Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com...
[Quoted Text] > Hi, > Ok, I tried changing the format of the column as you describe, that didn't > help, so I changed the way word connects to excel to DDE as described in > one > of the links. That fixed this problem but created a much larger problem. > > I have 3 columns in a table in word. There is one field per column merged > from excel, the three fields are : Product 1, q1 and p1 . Now, where q1 > is > supposed to be, it is putting the data from the field Product 1 and where > p1 > is supposed to be, it is putting the data from q1 and where Product 1 is > supposed to be it is putting the data from p1. It's got them all messed > up > somehow in the mapping or something. > > I think it was better the other way, at least that was just one field > messed > up and only sometimes. > > any other thoughts? > > Thanks, > Onalee > > "Peter Jamieson" wrote: > >> It's possible that you have encountered a new problem that is not >> described >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this >> suggestion from that page on your Excel sheet (or one of the following >> suggestions): >> >> << >> 1. Format a column as text via Data|Text to Columns... >> >> If you have a mixture of numbers and texts in a column but numbers in the >> first 8 rows, you may need to get the OLE DB provider to "see" the column >> as >> a text column. However, selecting the column and using Format|Cells to >> set >> the format as Text does not appear to be enough. One way that does appear >> to >> work is >> - select the column >> - select the Data|Text to Columns... menu option >> - click Next through the wizard until you reach Step 3 of 3, then >> select >> Text as the Column Data Format. >> >> >> >> >> -- >> Peter Jamieson >> http://tips.pjmsn.me.uk>> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... >> > Hi, >> > I am merging data from excel into a word document. I am getting data >> > in >> > word that is showing "12:00AM", however, there is data in the excel >> > sheet >> > and >> > it is NOT a date and it is not blank - it is part of an address line, >> > such >> > as >> > "Deliver to Front Desk" or any kinds of words, but definately not a >> > date >> > and >> > not blank. >> > >> > I have tried re-typing the data in excel, I have even put a " ' " in >> > front >> > of it to show it is text - nothing gets rid of this . It doesn't do it >> > on >> > every field or even every record on the same field, just some and >> > seemingly >> > randomly. >> > >> > Any idea how to fix this other than manually changing the data in the >> > final >> > merged document? >> > >> > Thanks , >> > Onalee >> >>
|
|
Hi,
No, the data is not in the wrong fields. If I go back to the the original
way to connect (OLB?) they go back to the right places on the word merge - I
did not change where they were in the excel sheet,just changed to DDE.
Apparently, this is just some cruel bug in excel that I'll just have to deal
with.
Onalee
"Doug Robbins - Word MVP" wrote:
[Quoted Text] > It seems like you must have the mergefields in the wrong columns. > > -- > 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 > > "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... > > Hi, > > Ok, I tried changing the format of the column as you describe, that didn't > > help, so I changed the way word connects to excel to DDE as described in > > one > > of the links. That fixed this problem but created a much larger problem. > > > > I have 3 columns in a table in word. There is one field per column merged > > from excel, the three fields are : Product 1, q1 and p1 . Now, where q1 > > is > > supposed to be, it is putting the data from the field Product 1 and where > > p1 > > is supposed to be, it is putting the data from q1 and where Product 1 is > > supposed to be it is putting the data from p1. It's got them all messed > > up > > somehow in the mapping or something. > > > > I think it was better the other way, at least that was just one field > > messed > > up and only sometimes. > > > > any other thoughts? > > > > Thanks, > > Onalee > > > > "Peter Jamieson" wrote: > > > >> It's possible that you have encountered a new problem that is not > >> described > >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this > >> suggestion from that page on your Excel sheet (or one of the following > >> suggestions): > >> > >> << > >> 1. Format a column as text via Data|Text to Columns... > >> > >> If you have a mixture of numbers and texts in a column but numbers in the > >> first 8 rows, you may need to get the OLE DB provider to "see" the column > >> as > >> a text column. However, selecting the column and using Format|Cells to > >> set > >> the format as Text does not appear to be enough. One way that does appear > >> to > >> work is > >> - select the column > >> - select the Data|Text to Columns... menu option > >> - click Next through the wizard until you reach Step 3 of 3, then > >> select > >> Text as the Column Data Format. > >> >> > >> > >> > >> -- > >> Peter Jamieson > >> http://tips.pjmsn.me.uk> >> > >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... > >> > Hi, > >> > I am merging data from excel into a word document. I am getting data > >> > in > >> > word that is showing "12:00AM", however, there is data in the excel > >> > sheet > >> > and > >> > it is NOT a date and it is not blank - it is part of an address line, > >> > such > >> > as > >> > "Deliver to Front Desk" or any kinds of words, but definately not a > >> > date > >> > and > >> > not blank. > >> > > >> > I have tried re-typing the data in excel, I have even put a " ' " in > >> > front > >> > of it to show it is text - nothing gets rid of this . It doesn't do it > >> > on > >> > every field or even every record on the same field, just some and > >> > seemingly > >> > randomly. > >> > > >> > Any idea how to fix this other than manually changing the data in the > >> > final > >> > merged document? > >> > > >> > Thanks , > >> > Onalee > >> > >> > > > |
|
If the arrangement of the fields is correct in Word, then I would agree that
it must be a bug in Excel. Phew!
--
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
"Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
news:B3C1FE88-09E7-4275-87B1-9E3D2182BE11[ at ]microsoft.com...
[Quoted Text] > Hi, > No, the data is not in the wrong fields. If I go back to the the original > way to connect (OLB?) they go back to the right places on the word > erge - I > did not change where they were in the excel sheet,just changed to DDE. > > Apparently, this is just some cruel bug in excel that I'll just have to > deal > with. > > Onalee > > "Doug Robbins - Word MVP" wrote: > >> It seems like you must have the mergefields in the wrong columns. >> >> -- >> 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 >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... >> > Hi, >> > Ok, I tried changing the format of the column as you describe, that >> > didn't >> > help, so I changed the way word connects to excel to DDE as described >> > in >> > one >> > of the links. That fixed this problem but created a much larger >> > problem. >> > >> > I have 3 columns in a table in word. There is one field per column >> > merged >> > from excel, the three fields are : Product 1, q1 and p1 . Now, where >> > q1 >> > is >> > supposed to be, it is putting the data from the field Product 1 and >> > where >> > p1 >> > is supposed to be, it is putting the data from q1 and where Product 1 >> > is >> > supposed to be it is putting the data from p1. It's got them all >> > messed >> > up >> > somehow in the mapping or something. >> > >> > I think it was better the other way, at least that was just one field >> > messed >> > up and only sometimes. >> > >> > any other thoughts? >> > >> > Thanks, >> > Onalee >> > >> > "Peter Jamieson" wrote: >> > >> >> It's possible that you have encountered a new problem that is not >> >> described >> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this >> >> suggestion from that page on your Excel sheet (or one of the following >> >> suggestions): >> >> >> >> << >> >> 1. Format a column as text via Data|Text to Columns... >> >> >> >> If you have a mixture of numbers and texts in a column but numbers in >> >> the >> >> first 8 rows, you may need to get the OLE DB provider to "see" the >> >> column >> >> as >> >> a text column. However, selecting the column and using Format|Cells to >> >> set >> >> the format as Text does not appear to be enough. One way that does >> >> appear >> >> to >> >> work is >> >> - select the column >> >> - select the Data|Text to Columns... menu option >> >> - click Next through the wizard until you reach Step 3 of 3, then >> >> select >> >> Text as the Column Data Format. >> >> >> >> >> >> >> >> >> -- >> >> Peter Jamieson >> >> http://tips.pjmsn.me.uk>> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... >> >> > Hi, >> >> > I am merging data from excel into a word document. I am getting >> >> > data >> >> > in >> >> > word that is showing "12:00AM", however, there is data in the excel >> >> > sheet >> >> > and >> >> > it is NOT a date and it is not blank - it is part of an address >> >> > line, >> >> > such >> >> > as >> >> > "Deliver to Front Desk" or any kinds of words, but definately not a >> >> > date >> >> > and >> >> > not blank. >> >> > >> >> > I have tried re-typing the data in excel, I have even put a " ' " in >> >> > front >> >> > of it to show it is text - nothing gets rid of this . It doesn't do >> >> > it >> >> > on >> >> > every field or even every record on the same field, just some and >> >> > seemingly >> >> > randomly. >> >> > >> >> > Any idea how to fix this other than manually changing the data in >> >> > the >> >> > final >> >> > merged document? >> >> > >> >> > Thanks , >> >> > Onalee >> >> >> >> >> >> >>
|
|
<<
Apparently, this is just some cruel bug in excel that I'll just have to deal
with.
[Quoted Text] >>
Could be.
1. Which column is causing the problem (when you do not use DDE) ?
2. Can you give some examples of the kind of data you have in it? (That's
what I didn't understand the first time you asked)
3. When you save the sheet, are you saving as .xls or .xlsx?
NB is your sheet simple enough to (a) save the data using e.g. .csv format
and (b) recreate the sheet from scratch by opening the .csv, making a few
changes, and saving it as .xls (or .xlsx) ?
Another thing you can try is to copy/paste your Excel data into Word and use
that as the data source for a merge.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com...
> Hi,
> Ok, I tried changing the format of the column as you describe, that didn't
> help, so I changed the way word connects to excel to DDE as described in
> one
> of the links. That fixed this problem but created a much larger problem.
>
> I have 3 columns in a table in word. There is one field per column merged
> from excel, the three fields are : Product 1, q1 and p1 . Now, where q1
> is
> supposed to be, it is putting the data from the field Product 1 and where
> p1
> is supposed to be, it is putting the data from q1 and where Product 1 is
> supposed to be it is putting the data from p1. It's got them all messed
> up
> somehow in the mapping or something.
>
> I think it was better the other way, at least that was just one field
> messed
> up and only sometimes.
>
> any other thoughts?
>
> Thanks,
> Onalee
>
> "Peter Jamieson" wrote:
>
>> It's possible that you have encountered a new problem that is not
>> described
>> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this
>> suggestion from that page on your Excel sheet (or one of the following
>> suggestions):
>>
>> <<
>> 1. Format a column as text via Data|Text to Columns...
>>
>> If you have a mixture of numbers and texts in a column but numbers in the
>> first 8 rows, you may need to get the OLE DB provider to "see" the column
>> as
>> a text column. However, selecting the column and using Format|Cells to
>> set
>> the format as Text does not appear to be enough. One way that does appear
>> to
>> work is
>> - select the column
>> - select the Data|Text to Columns... menu option
>> - click Next through the wizard until you reach Step 3 of 3, then
>> select
>> Text as the Column Data Format.
>> >>
>>
>>
>> --
>> Peter Jamieson
>> http://tips.pjmsn.me.uk
>>
>> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
>> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com...
>> > Hi,
>> > I am merging data from excel into a word document. I am getting data
>> > in
>> > word that is showing "12:00AM", however, there is data in the excel
>> > sheet
>> > and
>> > it is NOT a date and it is not blank - it is part of an address line,
>> > such
>> > as
>> > "Deliver to Front Desk" or any kinds of words, but definately not a
>> > date
>> > and
>> > not blank.
>> >
>> > I have tried re-typing the data in excel, I have even put a " ' " in
>> > front
>> > of it to show it is text - nothing gets rid of this . It doesn't do it
>> > on
>> > every field or even every record on the same field, just some and
>> > seemingly
>> > randomly.
>> >
>> > Any idea how to fix this other than manually changing the data in the
>> > final
>> > merged document?
>> >
>> > Thanks ,
>> > Onalee
>>
>>
|
|
Hi,
Answers to questions:
1. Which column is causing the problem (when you do not use DDE) ?
The column is called Address2, it is column "j" if that matters.
[Quoted Text] > 2. Can you give some examples of the kind of data you have in it? (That's
> what I didn't understand the first time you asked)
Most are blank - some have things like "Apt: 202" or "Suite 101", things lik
that in them.
>
> 3. When you save the sheet, are you saving as .xls or .xlsx?
xlsx
>
> NB is your sheet simple enough to (a) save the data using e.g. .csv format
> and (b) recreate the sheet from scratch by opening the .csv, making a few
> changes, and saving it as .xls (or .xlsx) ?
Yes, it is pretty straight forward, only one or two formulas, I could try
that.
>
> Another thing you can try is to copy/paste your Excel data into Word and use
> that as the data source for a merge.
I think I'll try the csv download - upload thing first. The field that is
messed up is not used that often -when it is blank, nothing shows on the
merge, if something is in it however, it shows 12:00AM . I have tried
converting to text (per previous posts here), I have tried deleting that
column and reinserting it, I've tried typing over the data in it, none of
that has changed it.
>
I'll give the download to csv and upload to a different sheet a try.
thank you,
Onalee
"Peter Jamieson" wrote:
> <<
> Apparently, this is just some cruel bug in excel that I'll just have to deal
> with.
> >>
>
> Could be.
>
> 1. Which column is causing the problem (when you do not use DDE) ?
>
> 2. Can you give some examples of the kind of data you have in it? (That's
> what I didn't understand the first time you asked)
>
> 3. When you save the sheet, are you saving as .xls or .xlsx?
>
> NB is your sheet simple enough to (a) save the data using e.g. .csv format
> and (b) recreate the sheet from scratch by opening the .csv, making a few
> changes, and saving it as .xls (or .xlsx) ?
>
> Another thing you can try is to copy/paste your Excel data into Word and use
> that as the data source for a merge.
>
> --
> Peter Jamieson
> http://tips.pjmsn.me.uk
>
> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com...
> > Hi,
> > Ok, I tried changing the format of the column as you describe, that didn't
> > help, so I changed the way word connects to excel to DDE as described in
> > one
> > of the links. That fixed this problem but created a much larger problem.
> >
> > I have 3 columns in a table in word. There is one field per column merged
> > from excel, the three fields are : Product 1, q1 and p1 . Now, where q1
> > is
> > supposed to be, it is putting the data from the field Product 1 and where
> > p1
> > is supposed to be, it is putting the data from q1 and where Product 1 is
> > supposed to be it is putting the data from p1. It's got them all messed
> > up
> > somehow in the mapping or something.
> >
> > I think it was better the other way, at least that was just one field
> > messed
> > up and only sometimes.
> >
> > any other thoughts?
> >
> > Thanks,
> > Onalee
> >
> > "Peter Jamieson" wrote:
> >
> >> It's possible that you have encountered a new problem that is not
> >> described
> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this
> >> suggestion from that page on your Excel sheet (or one of the following
> >> suggestions):
> >>
> >> <<
> >> 1. Format a column as text via Data|Text to Columns...
> >>
> >> If you have a mixture of numbers and texts in a column but numbers in the
> >> first 8 rows, you may need to get the OLE DB provider to "see" the column
> >> as
> >> a text column. However, selecting the column and using Format|Cells to
> >> set
> >> the format as Text does not appear to be enough. One way that does appear
> >> to
> >> work is
> >> - select the column
> >> - select the Data|Text to Columns... menu option
> >> - click Next through the wizard until you reach Step 3 of 3, then
> >> select
> >> Text as the Column Data Format.
> >> >>
> >>
> >>
> >> --
> >> Peter Jamieson
> >> http://tips.pjmsn.me.uk
> >>
> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com...
> >> > Hi,
> >> > I am merging data from excel into a word document. I am getting data
> >> > in
> >> > word that is showing "12:00AM", however, there is data in the excel
> >> > sheet
> >> > and
> >> > it is NOT a date and it is not blank - it is part of an address line,
> >> > such
> >> > as
> >> > "Deliver to Front Desk" or any kinds of words, but definately not a
> >> > date
> >> > and
> >> > not blank.
> >> >
> >> > I have tried re-typing the data in excel, I have even put a " ' " in
> >> > front
> >> > of it to show it is text - nothing gets rid of this . It doesn't do it
> >> > on
> >> > every field or even every record on the same field, just some and
> >> > seemingly
> >> > randomly.
> >> >
> >> > Any idea how to fix this other than manually changing the data in the
> >> > final
> >> > merged document?
> >> >
> >> > Thanks ,
> >> > Onalee
> >>
> >>
>
>
|
|
Does the column have blanks or just numbers in its first 8 rows?
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
news:604B042C-7238-4D9C-8596-640837ABADB2[ at ]microsoft.com...
[Quoted Text] > Hi, > Answers to questions: > > 1. Which column is causing the problem (when you do not use DDE) ? > > The column is called Address2, it is column "j" if that matters. > >> 2. Can you give some examples of the kind of data you have in it? (That's >> what I didn't understand the first time you asked) > Most are blank - some have things like "Apt: 202" or "Suite 101", things > lik > that in them. >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? > > xlsx >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv >> format >> and (b) recreate the sheet from scratch by opening the .csv, making a few >> changes, and saving it as .xls (or .xlsx) ? > > Yes, it is pretty straight forward, only one or two formulas, I could try > that. >> >> Another thing you can try is to copy/paste your Excel data into Word and >> use >> that as the data source for a merge. > > I think I'll try the csv download - upload thing first. The field that is > messed up is not used that often -when it is blank, nothing shows on the > merge, if something is in it however, it shows 12:00AM . I have tried > converting to text (per previous posts here), I have tried deleting that > column and reinserting it, I've tried typing over the data in it, none of > that has changed it. >> > > I'll give the download to csv and upload to a different sheet a try. > > thank you, > Onalee > > > "Peter Jamieson" wrote: > >> << >> Apparently, this is just some cruel bug in excel that I'll just have to >> deal >> with. >> >> >> >> Could be. >> >> 1. Which column is causing the problem (when you do not use DDE) ? >> >> 2. Can you give some examples of the kind of data you have in it? (That's >> what I didn't understand the first time you asked) >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv >> format >> and (b) recreate the sheet from scratch by opening the .csv, making a few >> changes, and saving it as .xls (or .xlsx) ? >> >> Another thing you can try is to copy/paste your Excel data into Word and >> use >> that as the data source for a merge. >> >> -- >> Peter Jamieson >> http://tips.pjmsn.me.uk>> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... >> > Hi, >> > Ok, I tried changing the format of the column as you describe, that >> > didn't >> > help, so I changed the way word connects to excel to DDE as described >> > in >> > one >> > of the links. That fixed this problem but created a much larger >> > problem. >> > >> > I have 3 columns in a table in word. There is one field per column >> > merged >> > from excel, the three fields are : Product 1, q1 and p1 . Now, where >> > q1 >> > is >> > supposed to be, it is putting the data from the field Product 1 and >> > where >> > p1 >> > is supposed to be, it is putting the data from q1 and where Product 1 >> > is >> > supposed to be it is putting the data from p1. It's got them all >> > messed >> > up >> > somehow in the mapping or something. >> > >> > I think it was better the other way, at least that was just one field >> > messed >> > up and only sometimes. >> > >> > any other thoughts? >> > >> > Thanks, >> > Onalee >> > >> > "Peter Jamieson" wrote: >> > >> >> It's possible that you have encountered a new problem that is not >> >> described >> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this >> >> suggestion from that page on your Excel sheet (or one of the following >> >> suggestions): >> >> >> >> << >> >> 1. Format a column as text via Data|Text to Columns... >> >> >> >> If you have a mixture of numbers and texts in a column but numbers in >> >> the >> >> first 8 rows, you may need to get the OLE DB provider to "see" the >> >> column >> >> as >> >> a text column. However, selecting the column and using Format|Cells to >> >> set >> >> the format as Text does not appear to be enough. One way that does >> >> appear >> >> to >> >> work is >> >> - select the column >> >> - select the Data|Text to Columns... menu option >> >> - click Next through the wizard until you reach Step 3 of 3, then >> >> select >> >> Text as the Column Data Format. >> >> >> >> >> >> >> >> >> -- >> >> Peter Jamieson >> >> http://tips.pjmsn.me.uk>> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... >> >> > Hi, >> >> > I am merging data from excel into a word document. I am getting >> >> > data >> >> > in >> >> > word that is showing "12:00AM", however, there is data in the excel >> >> > sheet >> >> > and >> >> > it is NOT a date and it is not blank - it is part of an address >> >> > line, >> >> > such >> >> > as >> >> > "Deliver to Front Desk" or any kinds of words, but definately not a >> >> > date >> >> > and >> >> > not blank. >> >> > >> >> > I have tried re-typing the data in excel, I have even put a " ' " in >> >> > front >> >> > of it to show it is text - nothing gets rid of this . It doesn't do >> >> > it >> >> > on >> >> > every field or even every record on the same field, just some and >> >> > seemingly >> >> > randomly. >> >> > >> >> > Any idea how to fix this other than manually changing the data in >> >> > the >> >> > final >> >> > merged document? >> >> > >> >> > Thanks , >> >> > Onalee >> >> >> >> >> >>
|
|
Hi,
Right now it has blanks in the first 8 rows - the spread sheet changes daily
as I add rows (I add to the top the most recent data). None of the rows are
'just numbers', if there is anything in them, it is letters AND numbers
usually. When I merge, I don't merge all rows, I pick out the particular
ones I need with a filter in word. If there is nothing in that column, then
nothing prints in word, it's only if there is something (words) in that
column it prints 12:00AM.
Onalee
"Peter Jamieson" wrote:
[Quoted Text] > Does the column have blanks or just numbers in its first 8 rows? > > -- > Peter Jamieson > http://tips.pjmsn.me.uk> > "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > news:604B042C-7238-4D9C-8596-640837ABADB2[ at ]microsoft.com... > > Hi, > > Answers to questions: > > > > 1. Which column is causing the problem (when you do not use DDE) ? > > > > The column is called Address2, it is column "j" if that matters. > > > >> 2. Can you give some examples of the kind of data you have in it? (That's > >> what I didn't understand the first time you asked) > > Most are blank - some have things like "Apt: 202" or "Suite 101", things > > lik > > that in them. > >> > >> 3. When you save the sheet, are you saving as .xls or .xlsx? > > > > xlsx > >> > >> NB is your sheet simple enough to (a) save the data using e.g. .csv > >> format > >> and (b) recreate the sheet from scratch by opening the .csv, making a few > >> changes, and saving it as .xls (or .xlsx) ? > > > > Yes, it is pretty straight forward, only one or two formulas, I could try > > that. > >> > >> Another thing you can try is to copy/paste your Excel data into Word and > >> use > >> that as the data source for a merge. > > > > I think I'll try the csv download - upload thing first. The field that is > > messed up is not used that often -when it is blank, nothing shows on the > > merge, if something is in it however, it shows 12:00AM . I have tried > > converting to text (per previous posts here), I have tried deleting that > > column and reinserting it, I've tried typing over the data in it, none of > > that has changed it. > >> > > > > I'll give the download to csv and upload to a different sheet a try. > > > > thank you, > > Onalee > > > > > > "Peter Jamieson" wrote: > > > >> << > >> Apparently, this is just some cruel bug in excel that I'll just have to > >> deal > >> with. > >> >> > >> > >> Could be. > >> > >> 1. Which column is causing the problem (when you do not use DDE) ? > >> > >> 2. Can you give some examples of the kind of data you have in it? (That's > >> what I didn't understand the first time you asked) > >> > >> 3. When you save the sheet, are you saving as .xls or .xlsx? > >> > >> NB is your sheet simple enough to (a) save the data using e.g. .csv > >> format > >> and (b) recreate the sheet from scratch by opening the .csv, making a few > >> changes, and saving it as .xls (or .xlsx) ? > >> > >> Another thing you can try is to copy/paste your Excel data into Word and > >> use > >> that as the data source for a merge. > >> > >> -- > >> Peter Jamieson > >> http://tips.pjmsn.me.uk> >> > >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... > >> > Hi, > >> > Ok, I tried changing the format of the column as you describe, that > >> > didn't > >> > help, so I changed the way word connects to excel to DDE as described > >> > in > >> > one > >> > of the links. That fixed this problem but created a much larger > >> > problem. > >> > > >> > I have 3 columns in a table in word. There is one field per column > >> > merged > >> > from excel, the three fields are : Product 1, q1 and p1 . Now, where > >> > q1 > >> > is > >> > supposed to be, it is putting the data from the field Product 1 and > >> > where > >> > p1 > >> > is supposed to be, it is putting the data from q1 and where Product 1 > >> > is > >> > supposed to be it is putting the data from p1. It's got them all > >> > messed > >> > up > >> > somehow in the mapping or something. > >> > > >> > I think it was better the other way, at least that was just one field > >> > messed > >> > up and only sometimes. > >> > > >> > any other thoughts? > >> > > >> > Thanks, > >> > Onalee > >> > > >> > "Peter Jamieson" wrote: > >> > > >> >> It's possible that you have encountered a new problem that is not > >> >> described > >> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying this > >> >> suggestion from that page on your Excel sheet (or one of the following > >> >> suggestions): > >> >> > >> >> << > >> >> 1. Format a column as text via Data|Text to Columns... > >> >> > >> >> If you have a mixture of numbers and texts in a column but numbers in > >> >> the > >> >> first 8 rows, you may need to get the OLE DB provider to "see" the > >> >> column > >> >> as > >> >> a text column. However, selecting the column and using Format|Cells to > >> >> set > >> >> the format as Text does not appear to be enough. One way that does > >> >> appear > >> >> to > >> >> work is > >> >> - select the column > >> >> - select the Data|Text to Columns... menu option > >> >> - click Next through the wizard until you reach Step 3 of 3, then > >> >> select > >> >> Text as the Column Data Format. > >> >> >> > >> >> > >> >> > >> >> -- > >> >> Peter Jamieson > >> >> http://tips.pjmsn.me.uk> >> >> > >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... > >> >> > Hi, > >> >> > I am merging data from excel into a word document. I am getting > >> >> > data > >> >> > in > >> >> > word that is showing "12:00AM", however, there is data in the excel > >> >> > sheet > >> >> > and > >> >> > it is NOT a date and it is not blank - it is part of an address > >> >> > line, > >> >> > such > >> >> > as > >> >> > "Deliver to Front Desk" or any kinds of words, but definately not a > >> >> > date > >> >> > and > >> >> > not blank. > >> >> > > >> >> > I have tried re-typing the data in excel, I have even put a " ' " in > >> >> > front > >> >> > of it to show it is text - nothing gets rid of this . It doesn't do > >> >> > it > >> >> > on > >> >> > every field or even every record on the same field, just some and > >> >> > seemingly > >> >> > randomly. > >> >> > > >> >> > Any idea how to fix this other than manually changing the data in > >> >> > the > >> >> > final > >> >> > merged document? > >> >> > > >> >> > Thanks , > >> >> > Onalee > >> >> > >> >> > >> > >> > > |
|
Sorry if you have already done this, but are the first 8 cells in the column
formatted as "dates" in Excel?
If so, can you change the formatting of the cells to "General", save the
Excel sheet, go through the connectionprocess again from Word, and see what
happens - in the first instance, don't do any filtering.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message
news:934443D4-3DAD-4BD3-8958-706569F00C83[ at ]microsoft.com...
[Quoted Text] > Hi, > Right now it has blanks in the first 8 rows - the spread sheet changes > daily > as I add rows (I add to the top the most recent data). None of the rows > are > 'just numbers', if there is anything in them, it is letters AND numbers > usually. When I merge, I don't merge all rows, I pick out the particular > ones I need with a filter in word. If there is nothing in that column, > then > nothing prints in word, it's only if there is something (words) in that > column it prints 12:00AM. > > Onalee > > "Peter Jamieson" wrote: > >> Does the column have blanks or just numbers in its first 8 rows? >> >> -- >> Peter Jamieson >> http://tips.pjmsn.me.uk>> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> news:604B042C-7238-4D9C-8596-640837ABADB2[ at ]microsoft.com... >> > Hi, >> > Answers to questions: >> > >> > 1. Which column is causing the problem (when you do not use DDE) ? >> > >> > The column is called Address2, it is column "j" if that matters. >> > >> >> 2. Can you give some examples of the kind of data you have in it? >> >> (That's >> >> what I didn't understand the first time you asked) >> > Most are blank - some have things like "Apt: 202" or "Suite 101", >> > things >> > lik >> > that in them. >> >> >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? >> > >> > xlsx >> >> >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv >> >> format >> >> and (b) recreate the sheet from scratch by opening the .csv, making a >> >> few >> >> changes, and saving it as .xls (or .xlsx) ? >> > >> > Yes, it is pretty straight forward, only one or two formulas, I could >> > try >> > that. >> >> >> >> Another thing you can try is to copy/paste your Excel data into Word >> >> and >> >> use >> >> that as the data source for a merge. >> > >> > I think I'll try the csv download - upload thing first. The field that >> > is >> > messed up is not used that often -when it is blank, nothing shows on >> > the >> > merge, if something is in it however, it shows 12:00AM . I have tried >> > converting to text (per previous posts here), I have tried deleting >> > that >> > column and reinserting it, I've tried typing over the data in it, none >> > of >> > that has changed it. >> >> >> > >> > I'll give the download to csv and upload to a different sheet a try. >> > >> > thank you, >> > Onalee >> > >> > >> > "Peter Jamieson" wrote: >> > >> >> << >> >> Apparently, this is just some cruel bug in excel that I'll just have >> >> to >> >> deal >> >> with. >> >> >> >> >> >> >> Could be. >> >> >> >> 1. Which column is causing the problem (when you do not use DDE) ? >> >> >> >> 2. Can you give some examples of the kind of data you have in it? >> >> (That's >> >> what I didn't understand the first time you asked) >> >> >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? >> >> >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv >> >> format >> >> and (b) recreate the sheet from scratch by opening the .csv, making a >> >> few >> >> changes, and saving it as .xls (or .xlsx) ? >> >> >> >> Another thing you can try is to copy/paste your Excel data into Word >> >> and >> >> use >> >> that as the data source for a merge. >> >> >> >> -- >> >> Peter Jamieson >> >> http://tips.pjmsn.me.uk>> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> >> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... >> >> > Hi, >> >> > Ok, I tried changing the format of the column as you describe, that >> >> > didn't >> >> > help, so I changed the way word connects to excel to DDE as >> >> > described >> >> > in >> >> > one >> >> > of the links. That fixed this problem but created a much larger >> >> > problem. >> >> > >> >> > I have 3 columns in a table in word. There is one field per column >> >> > merged >> >> > from excel, the three fields are : Product 1, q1 and p1 . Now, >> >> > where >> >> > q1 >> >> > is >> >> > supposed to be, it is putting the data from the field Product 1 and >> >> > where >> >> > p1 >> >> > is supposed to be, it is putting the data from q1 and where Product >> >> > 1 >> >> > is >> >> > supposed to be it is putting the data from p1. It's got them all >> >> > messed >> >> > up >> >> > somehow in the mapping or something. >> >> > >> >> > I think it was better the other way, at least that was just one >> >> > field >> >> > messed >> >> > up and only sometimes. >> >> > >> >> > any other thoughts? >> >> > >> >> > Thanks, >> >> > Onalee >> >> > >> >> > "Peter Jamieson" wrote: >> >> > >> >> >> It's possible that you have encountered a new problem that is not >> >> >> described >> >> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying >> >> >> this >> >> >> suggestion from that page on your Excel sheet (or one of the >> >> >> following >> >> >> suggestions): >> >> >> >> >> >> << >> >> >> 1. Format a column as text via Data|Text to Columns... >> >> >> >> >> >> If you have a mixture of numbers and texts in a column but numbers >> >> >> in >> >> >> the >> >> >> first 8 rows, you may need to get the OLE DB provider to "see" the >> >> >> column >> >> >> as >> >> >> a text column. However, selecting the column and using Format|Cells >> >> >> to >> >> >> set >> >> >> the format as Text does not appear to be enough. One way that does >> >> >> appear >> >> >> to >> >> >> work is >> >> >> - select the column >> >> >> - select the Data|Text to Columns... menu option >> >> >> - click Next through the wizard until you reach Step 3 of 3, then >> >> >> select >> >> >> Text as the Column Data Format. >> >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> Peter Jamieson >> >> >> http://tips.pjmsn.me.uk>> >> >> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> >> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... >> >> >> > Hi, >> >> >> > I am merging data from excel into a word document. I am getting >> >> >> > data >> >> >> > in >> >> >> > word that is showing "12:00AM", however, there is data in the >> >> >> > excel >> >> >> > sheet >> >> >> > and >> >> >> > it is NOT a date and it is not blank - it is part of an address >> >> >> > line, >> >> >> > such >> >> >> > as >> >> >> > "Deliver to Front Desk" or any kinds of words, but definately >> >> >> > not a >> >> >> > date >> >> >> > and >> >> >> > not blank. >> >> >> > >> >> >> > I have tried re-typing the data in excel, I have even put a " ' " >> >> >> > in >> >> >> > front >> >> >> > of it to show it is text - nothing gets rid of this . It doesn't >> >> >> > do >> >> >> > it >> >> >> > on >> >> >> > every field or even every record on the same field, just some and >> >> >> > seemingly >> >> >> > randomly. >> >> >> > >> >> >> > Any idea how to fix this other than manually changing the data in >> >> >> > the >> >> >> > final >> >> >> > merged document? >> >> >> > >> >> >> > Thanks , >> >> >> > Onalee >> >> >> >> >> >> >> >> >> >> >> >>
|
|
Hi Onalee and Peter,
Was this ever resolved? I am just experiencing this problem today. It
worked fine 4 days ago and nothing changed except for 2 things today:
1. I added another row of data in the spreadsheet.
2. Earlier today I merged a different Word doc with a different Excel
spreadsheet.
I restarted my computer.
Nothing changed.
It seems to randomly go back and forth between knowing some of the fields
and thinking their 12:00AM.
After I added text to the first row of data to the fields that were doing
it, it seems to be fixed but who knows in the next few days.
Please let me know if either of you have more updates.
This is my exact problem - I'm "glad" to know someone else is encountering
it and it's not just my computer!
Take care,
Kelly
"Peter Jamieson" wrote:
[Quoted Text] > Sorry if you have already done this, but are the first 8 cells in the column > formatted as "dates" in Excel? > > If so, can you change the formatting of the cells to "General", save the > Excel sheet, go through the connectionprocess again from Word, and see what > happens - in the first instance, don't do any filtering. > > -- > Peter Jamieson > http://tips.pjmsn.me.uk> > "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > news:934443D4-3DAD-4BD3-8958-706569F00C83[ at ]microsoft.com... > > Hi, > > Right now it has blanks in the first 8 rows - the spread sheet changes > > daily > > as I add rows (I add to the top the most recent data). None of the rows > > are > > 'just numbers', if there is anything in them, it is letters AND numbers > > usually. When I merge, I don't merge all rows, I pick out the particular > > ones I need with a filter in word. If there is nothing in that column, > > then > > nothing prints in word, it's only if there is something (words) in that > > column it prints 12:00AM. > > > > Onalee > > > > "Peter Jamieson" wrote: > > > >> Does the column have blanks or just numbers in its first 8 rows? > >> > >> -- > >> Peter Jamieson > >> http://tips.pjmsn.me.uk> >> > >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> news:604B042C-7238-4D9C-8596-640837ABADB2[ at ]microsoft.com... > >> > Hi, > >> > Answers to questions: > >> > > >> > 1. Which column is causing the problem (when you do not use DDE) ? > >> > > >> > The column is called Address2, it is column "j" if that matters. > >> > > >> >> 2. Can you give some examples of the kind of data you have in it? > >> >> (That's > >> >> what I didn't understand the first time you asked) > >> > Most are blank - some have things like "Apt: 202" or "Suite 101", > >> > things > >> > lik > >> > that in them. > >> >> > >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? > >> > > >> > xlsx > >> >> > >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv > >> >> format > >> >> and (b) recreate the sheet from scratch by opening the .csv, making a > >> >> few > >> >> changes, and saving it as .xls (or .xlsx) ? > >> > > >> > Yes, it is pretty straight forward, only one or two formulas, I could > >> > try > >> > that. > >> >> > >> >> Another thing you can try is to copy/paste your Excel data into Word > >> >> and > >> >> use > >> >> that as the data source for a merge. > >> > > >> > I think I'll try the csv download - upload thing first. The field that > >> > is > >> > messed up is not used that often -when it is blank, nothing shows on > >> > the > >> > merge, if something is in it however, it shows 12:00AM . I have tried > >> > converting to text (per previous posts here), I have tried deleting > >> > that > >> > column and reinserting it, I've tried typing over the data in it, none > >> > of > >> > that has changed it. > >> >> > >> > > >> > I'll give the download to csv and upload to a different sheet a try. > >> > > >> > thank you, > >> > Onalee > >> > > >> > > >> > "Peter Jamieson" wrote: > >> > > >> >> << > >> >> Apparently, this is just some cruel bug in excel that I'll just have > >> >> to > >> >> deal > >> >> with. > >> >> >> > >> >> > >> >> Could be. > >> >> > >> >> 1. Which column is causing the problem (when you do not use DDE) ? > >> >> > >> >> 2. Can you give some examples of the kind of data you have in it? > >> >> (That's > >> >> what I didn't understand the first time you asked) > >> >> > >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? > >> >> > >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv > >> >> format > >> >> and (b) recreate the sheet from scratch by opening the .csv, making a > >> >> few > >> >> changes, and saving it as .xls (or .xlsx) ? > >> >> > >> >> Another thing you can try is to copy/paste your Excel data into Word > >> >> and > >> >> use > >> >> that as the data source for a merge. > >> >> > >> >> -- > >> >> Peter Jamieson > >> >> http://tips.pjmsn.me.uk> >> >> > >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> >> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... > >> >> > Hi, > >> >> > Ok, I tried changing the format of the column as you describe, that > >> >> > didn't > >> >> > help, so I changed the way word connects to excel to DDE as > >> >> > described > >> >> > in > >> >> > one > >> >> > of the links. That fixed this problem but created a much larger > >> >> > problem. > >> >> > > >> >> > I have 3 columns in a table in word. There is one field per column > >> >> > merged > >> >> > from excel, the three fields are : Product 1, q1 and p1 . Now, > >> >> > where > >> >> > q1 > >> >> > is > >> >> > supposed to be, it is putting the data from the field Product 1 and > >> >> > where > >> >> > p1 > >> >> > is supposed to be, it is putting the data from q1 and where Product > >> >> > 1 > >> >> > is > >> >> > supposed to be it is putting the data from p1. It's got them all > >> >> > messed > >> >> > up > >> >> > somehow in the mapping or something. > >> >> > > >> >> > I think it was better the other way, at least that was just one > >> >> > field > >> >> > messed > >> >> > up and only sometimes. > >> >> > > >> >> > any other thoughts? > >> >> > > >> >> > Thanks, > >> >> > Onalee > >> >> > > >> >> > "Peter Jamieson" wrote: > >> >> > > >> >> >> It's possible that you have encountered a new problem that is not > >> >> >> described > >> >> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying > >> >> >> this > >> >> >> suggestion from that page on your Excel sheet (or one of the > >> >> >> following > >> >> >> suggestions): > >> >> >> > >> >> >> << > >> >> >> 1. Format a column as text via Data|Text to Columns... > >> >> >> > >> >> >> If you have a mixture of numbers and texts in a column but numbers > >> >> >> in > >> >> >> the > >> >> >> first 8 rows, you may need to get the OLE DB provider to "see" the > >> >> >> column > >> >> >> as > >> >> >> a text column. However, selecting the column and using Format|Cells > >> >> >> to > >> >> >> set > >> >> >> the format as Text does not appear to be enough. One way that does > >> >> >> appear > >> >> >> to > >> >> >> work is > >> >> >> - select the column > >> >> >> - select the Data|Text to Columns... menu option > >> >> >> - click Next through the wizard until you reach Step 3 of 3, then > >> >> >> select > >> >> >> Text as the Column Data Format. > >> >> >> >> > >> >> >> > >> >> >> > >> >> >> -- > >> >> >> Peter Jamieson > >> >> >> http://tips.pjmsn.me.uk> >> >> >> > >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> >> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... > >> >> >> > Hi, > >> >> >> > I am merging data from excel into a word document. I am getting > >> >> >> > data > >> >> >> > in > >> >> >> > word that is showing "12:00AM", however, there is data in the > >> >> >> > excel > >> >> >> > sheet > >> >> >> > and > >> >> >> > it is NOT a date and it is not blank - it is part of an address > >> >> >> > line, > >> >> >> > such > >> >> >> > as > >> >> >> > "Deliver to Front Desk" or any kinds of words, but definately > >> >> >> > not a > >> >> >> > date > >> >> >> > and > >> >> >> > not blank. > >> >> >> > > >> >> >> > I have tried re-typing the data in excel, I have even put a " ' " > >> >> >> > in > >> >> >> > front > >> >> >> > of it to show it is text - nothing gets rid of this . It doesn't > >> >> >> > do > >> >> >> > it > >> >> >> > on > >> >> >> > every field or even every record on the same field, just some and > >> >> >> > seemingly > >> >> >> > randomly. > >> >> >> > > >> >> >> > Any idea how to fix this other than manually changing the data in > >> >> >> > the > >> >> >> > final > >> >> >> > merged document? > >> >> >> > > >> >> >> > Thanks , > >> >> >> > Onalee > >> >> >> > >> >> >> > >> >> > >> >> > >> > >> > > |
|
Hello Kelly,
You will find an explanation of how Word/Excel ODBC/OLE DB connections
behave at http://tips.pjmsn.me.uk/t0003.htm . But I do not claim it is the
last Word - i.e. there may well be additional problems.
However, unless you know exactly what /format/ Excel is using for each cell
in the column, it is difficult to predict, solely from what you see in the
cells in Excel, what data will get through to Word.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Kelly Dennis" <Kelly Dennis[ at ]discussions.microsoft.com> wrote in message
news:A92CDF4A-DB9F-4337-B583-D854E58ED0FB[ at ]microsoft.com...
[Quoted Text] > Hi Onalee and Peter, > > Was this ever resolved? I am just experiencing this problem today. It > worked fine 4 days ago and nothing changed except for 2 things today: > > 1. I added another row of data in the spreadsheet. > 2. Earlier today I merged a different Word doc with a different Excel > spreadsheet. > > I restarted my computer. > > Nothing changed. > > It seems to randomly go back and forth between knowing some of the fields > and thinking their 12:00AM. > > After I added text to the first row of data to the fields that were doing > it, it seems to be fixed but who knows in the next few days. > > Please let me know if either of you have more updates. > > This is my exact problem - I'm "glad" to know someone else is encountering > it and it's not just my computer! > > Take care, > Kelly > > "Peter Jamieson" wrote: > >> Sorry if you have already done this, but are the first 8 cells in the >> column >> formatted as "dates" in Excel? >> >> If so, can you change the formatting of the cells to "General", save the >> Excel sheet, go through the connectionprocess again from Word, and see >> what >> happens - in the first instance, don't do any filtering. >> >> -- >> Peter Jamieson >> http://tips.pjmsn.me.uk>> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> news:934443D4-3DAD-4BD3-8958-706569F00C83[ at ]microsoft.com... >> > Hi, >> > Right now it has blanks in the first 8 rows - the spread sheet changes >> > daily >> > as I add rows (I add to the top the most recent data). None of the >> > rows >> > are >> > 'just numbers', if there is anything in them, it is letters AND numbers >> > usually. When I merge, I don't merge all rows, I pick out the >> > particular >> > ones I need with a filter in word. If there is nothing in that column, >> > then >> > nothing prints in word, it's only if there is something (words) in that >> > column it prints 12:00AM. >> > >> > Onalee >> > >> > "Peter Jamieson" wrote: >> > >> >> Does the column have blanks or just numbers in its first 8 rows? >> >> >> >> -- >> >> Peter Jamieson >> >> http://tips.pjmsn.me.uk>> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> >> news:604B042C-7238-4D9C-8596-640837ABADB2[ at ]microsoft.com... >> >> > Hi, >> >> > Answers to questions: >> >> > >> >> > 1. Which column is causing the problem (when you do not use DDE) ? >> >> > >> >> > The column is called Address2, it is column "j" if that matters. >> >> > >> >> >> 2. Can you give some examples of the kind of data you have in it? >> >> >> (That's >> >> >> what I didn't understand the first time you asked) >> >> > Most are blank - some have things like "Apt: 202" or "Suite 101", >> >> > things >> >> > lik >> >> > that in them. >> >> >> >> >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? >> >> > >> >> > xlsx >> >> >> >> >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv >> >> >> format >> >> >> and (b) recreate the sheet from scratch by opening the .csv, making >> >> >> a >> >> >> few >> >> >> changes, and saving it as .xls (or .xlsx) ? >> >> > >> >> > Yes, it is pretty straight forward, only one or two formulas, I >> >> > could >> >> > try >> >> > that. >> >> >> >> >> >> Another thing you can try is to copy/paste your Excel data into >> >> >> Word >> >> >> and >> >> >> use >> >> >> that as the data source for a merge. >> >> > >> >> > I think I'll try the csv download - upload thing first. The field >> >> > that >> >> > is >> >> > messed up is not used that often -when it is blank, nothing shows on >> >> > the >> >> > merge, if something is in it however, it shows 12:00AM . I have >> >> > tried >> >> > converting to text (per previous posts here), I have tried deleting >> >> > that >> >> > column and reinserting it, I've tried typing over the data in it, >> >> > none >> >> > of >> >> > that has changed it. >> >> >> >> >> > >> >> > I'll give the download to csv and upload to a different sheet a try. >> >> > >> >> > thank you, >> >> > Onalee >> >> > >> >> > >> >> > "Peter Jamieson" wrote: >> >> > >> >> >> << >> >> >> Apparently, this is just some cruel bug in excel that I'll just >> >> >> have >> >> >> to >> >> >> deal >> >> >> with. >> >> >> >> >> >> >> >> >> >> Could be. >> >> >> >> >> >> 1. Which column is causing the problem (when you do not use DDE) ? >> >> >> >> >> >> 2. Can you give some examples of the kind of data you have in it? >> >> >> (That's >> >> >> what I didn't understand the first time you asked) >> >> >> >> >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? >> >> >> >> >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv >> >> >> format >> >> >> and (b) recreate the sheet from scratch by opening the .csv, making >> >> >> a >> >> >> few >> >> >> changes, and saving it as .xls (or .xlsx) ? >> >> >> >> >> >> Another thing you can try is to copy/paste your Excel data into >> >> >> Word >> >> >> and >> >> >> use >> >> >> that as the data source for a merge. >> >> >> >> >> >> -- >> >> >> Peter Jamieson >> >> >> http://tips.pjmsn.me.uk>> >> >> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> >> >> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... >> >> >> > Hi, >> >> >> > Ok, I tried changing the format of the column as you describe, >> >> >> > that >> >> >> > didn't >> >> >> > help, so I changed the way word connects to excel to DDE as >> >> >> > described >> >> >> > in >> >> >> > one >> >> >> > of the links. That fixed this problem but created a much larger >> >> >> > problem. >> >> >> > >> >> >> > I have 3 columns in a table in word. There is one field per >> >> >> > column >> >> >> > merged >> >> >> > from excel, the three fields are : Product 1, q1 and p1 . Now, >> >> >> > where >> >> >> > q1 >> >> >> > is >> >> >> > supposed to be, it is putting the data from the field Product 1 >> >> >> > and >> >> >> > where >> >> >> > p1 >> >> >> > is supposed to be, it is putting the data from q1 and where >> >> >> > Product >> >> >> > 1 >> >> >> > is >> >> >> > supposed to be it is putting the data from p1. It's got them all >> >> >> > messed >> >> >> > up >> >> >> > somehow in the mapping or something. >> >> >> > >> >> >> > I think it was better the other way, at least that was just one >> >> >> > field >> >> >> > messed >> >> >> > up and only sometimes. >> >> >> > >> >> >> > any other thoughts? >> >> >> > >> >> >> > Thanks, >> >> >> > Onalee >> >> >> > >> >> >> > "Peter Jamieson" wrote: >> >> >> > >> >> >> >> It's possible that you have encountered a new problem that is >> >> >> >> not >> >> >> >> described >> >> >> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying >> >> >> >> this >> >> >> >> suggestion from that page on your Excel sheet (or one of the >> >> >> >> following >> >> >> >> suggestions): >> >> >> >> >> >> >> >> << >> >> >> >> 1. Format a column as text via Data|Text to Columns... >> >> >> >> >> >> >> >> If you have a mixture of numbers and texts in a column but >> >> >> >> numbers >> >> >> >> in >> >> >> >> the >> >> >> >> first 8 rows, you may need to get the OLE DB provider to "see" >> >> >> >> the >> >> >> >> column >> >> >> >> as >> >> >> >> a text column. However, selecting the column and using >> >> >> >> Format|Cells >> >> >> >> to >> >> >> >> set >> >> >> >> the format as Text does not appear to be enough. One way that >> >> >> >> does >> >> >> >> appear >> >> >> >> to >> >> >> >> work is >> >> >> >> - select the column >> >> >> >> - select the Data|Text to Columns... menu option >> >> >> >> - click Next through the wizard until you reach Step 3 of 3, >> >> >> >> then >> >> >> >> select >> >> >> >> Text as the Column Data Format. >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> -- >> >> >> >> Peter Jamieson >> >> >> >> http://tips.pjmsn.me.uk>> >> >> >> >> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message >> >> >> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... >> >> >> >> > Hi, >> >> >> >> > I am merging data from excel into a word document. I am >> >> >> >> > getting >> >> >> >> > data >> >> >> >> > in >> >> >> >> > word that is showing "12:00AM", however, there is data in the >> >> >> >> > excel >> >> >> >> > sheet >> >> >> >> > and >> >> >> >> > it is NOT a date and it is not blank - it is part of an >> >> >> >> > address >> >> >> >> > line, >> >> >> >> > such >> >> >> >> > as >> >> >> >> > "Deliver to Front Desk" or any kinds of words, but definately >> >> >> >> > not a >> >> >> >> > date >> >> >> >> > and >> >> >> >> > not blank. >> >> >> >> > >> >> >> >> > I have tried re-typing the data in excel, I have even put a " >> >> >> >> > ' " >> >> >> >> > in >> >> >> >> > front >> >> >> >> > of it to show it is text - nothing gets rid of this . It >> >> >> >> > doesn't >> >> >> >> > do >> >> >> >> > it >> >> >> >> > on >> >> >> >> > every field or even every record on the same field, just some >> >> >> >> > and >> >> >> >> > seemingly >> >> >> >> > randomly. >> >> >> >> > >> >> >> >> > Any idea how to fix this other than manually changing the data >> >> >> >> > in >> >> >> >> > the >> >> >> >> > final >> >> >> >> > merged document? >> >> >> >> > >> >> >> >> > Thanks , >> >> >> >> > Onalee >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>
|
|
Thank you! I will read it and definitely marked that reply as helpful.
Take care and happy holidays,
Kelly
"Peter Jamieson" wrote:
[Quoted Text] > Hello Kelly, > > You will find an explanation of how Word/Excel ODBC/OLE DB connections > behave at http://tips.pjmsn.me.uk/t0003.htm . But I do not claim it is the > last Word - i.e. there may well be additional problems. > > However, unless you know exactly what /format/ Excel is using for each cell > in the column, it is difficult to predict, solely from what you see in the > cells in Excel, what data will get through to Word. > > -- > Peter Jamieson > http://tips.pjmsn.me.uk> > "Kelly Dennis" <Kelly Dennis[ at ]discussions.microsoft.com> wrote in message > news:A92CDF4A-DB9F-4337-B583-D854E58ED0FB[ at ]microsoft.com... > > Hi Onalee and Peter, > > > > Was this ever resolved? I am just experiencing this problem today. It > > worked fine 4 days ago and nothing changed except for 2 things today: > > > > 1. I added another row of data in the spreadsheet. > > 2. Earlier today I merged a different Word doc with a different Excel > > spreadsheet. > > > > I restarted my computer. > > > > Nothing changed. > > > > It seems to randomly go back and forth between knowing some of the fields > > and thinking their 12:00AM. > > > > After I added text to the first row of data to the fields that were doing > > it, it seems to be fixed but who knows in the next few days. > > > > Please let me know if either of you have more updates. > > > > This is my exact problem - I'm "glad" to know someone else is encountering > > it and it's not just my computer! > > > > Take care, > > Kelly > > > > "Peter Jamieson" wrote: > > > >> Sorry if you have already done this, but are the first 8 cells in the > >> column > >> formatted as "dates" in Excel? > >> > >> If so, can you change the formatting of the cells to "General", save the > >> Excel sheet, go through the connectionprocess again from Word, and see > >> what > >> happens - in the first instance, don't do any filtering. > >> > >> -- > >> Peter Jamieson > >> http://tips.pjmsn.me.uk> >> > >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> news:934443D4-3DAD-4BD3-8958-706569F00C83[ at ]microsoft.com... > >> > Hi, > >> > Right now it has blanks in the first 8 rows - the spread sheet changes > >> > daily > >> > as I add rows (I add to the top the most recent data). None of the > >> > rows > >> > are > >> > 'just numbers', if there is anything in them, it is letters AND numbers > >> > usually. When I merge, I don't merge all rows, I pick out the > >> > particular > >> > ones I need with a filter in word. If there is nothing in that column, > >> > then > >> > nothing prints in word, it's only if there is something (words) in that > >> > column it prints 12:00AM. > >> > > >> > Onalee > >> > > >> > "Peter Jamieson" wrote: > >> > > >> >> Does the column have blanks or just numbers in its first 8 rows? > >> >> > >> >> -- > >> >> Peter Jamieson > >> >> http://tips.pjmsn.me.uk> >> >> > >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> >> news:604B042C-7238-4D9C-8596-640837ABADB2[ at ]microsoft.com... > >> >> > Hi, > >> >> > Answers to questions: > >> >> > > >> >> > 1. Which column is causing the problem (when you do not use DDE) ? > >> >> > > >> >> > The column is called Address2, it is column "j" if that matters. > >> >> > > >> >> >> 2. Can you give some examples of the kind of data you have in it? > >> >> >> (That's > >> >> >> what I didn't understand the first time you asked) > >> >> > Most are blank - some have things like "Apt: 202" or "Suite 101", > >> >> > things > >> >> > lik > >> >> > that in them. > >> >> >> > >> >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? > >> >> > > >> >> > xlsx > >> >> >> > >> >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv > >> >> >> format > >> >> >> and (b) recreate the sheet from scratch by opening the .csv, making > >> >> >> a > >> >> >> few > >> >> >> changes, and saving it as .xls (or .xlsx) ? > >> >> > > >> >> > Yes, it is pretty straight forward, only one or two formulas, I > >> >> > could > >> >> > try > >> >> > that. > >> >> >> > >> >> >> Another thing you can try is to copy/paste your Excel data into > >> >> >> Word > >> >> >> and > >> >> >> use > >> >> >> that as the data source for a merge. > >> >> > > >> >> > I think I'll try the csv download - upload thing first. The field > >> >> > that > >> >> > is > >> >> > messed up is not used that often -when it is blank, nothing shows on > >> >> > the > >> >> > merge, if something is in it however, it shows 12:00AM . I have > >> >> > tried > >> >> > converting to text (per previous posts here), I have tried deleting > >> >> > that > >> >> > column and reinserting it, I've tried typing over the data in it, > >> >> > none > >> >> > of > >> >> > that has changed it. > >> >> >> > >> >> > > >> >> > I'll give the download to csv and upload to a different sheet a try. > >> >> > > >> >> > thank you, > >> >> > Onalee > >> >> > > >> >> > > >> >> > "Peter Jamieson" wrote: > >> >> > > >> >> >> << > >> >> >> Apparently, this is just some cruel bug in excel that I'll just > >> >> >> have > >> >> >> to > >> >> >> deal > >> >> >> with. > >> >> >> >> > >> >> >> > >> >> >> Could be. > >> >> >> > >> >> >> 1. Which column is causing the problem (when you do not use DDE) ? > >> >> >> > >> >> >> 2. Can you give some examples of the kind of data you have in it? > >> >> >> (That's > >> >> >> what I didn't understand the first time you asked) > >> >> >> > >> >> >> 3. When you save the sheet, are you saving as .xls or .xlsx? > >> >> >> > >> >> >> NB is your sheet simple enough to (a) save the data using e.g. .csv > >> >> >> format > >> >> >> and (b) recreate the sheet from scratch by opening the .csv, making > >> >> >> a > >> >> >> few > >> >> >> changes, and saving it as .xls (or .xlsx) ? > >> >> >> > >> >> >> Another thing you can try is to copy/paste your Excel data into > >> >> >> Word > >> >> >> and > >> >> >> use > >> >> >> that as the data source for a merge. > >> >> >> > >> >> >> -- > >> >> >> Peter Jamieson > >> >> >> http://tips.pjmsn.me.uk> >> >> >> > >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> >> >> news:5D93ECB5-C82A-44AA-B50D-5096102442DF[ at ]microsoft.com... > >> >> >> > Hi, > >> >> >> > Ok, I tried changing the format of the column as you describe, > >> >> >> > that > >> >> >> > didn't > >> >> >> > help, so I changed the way word connects to excel to DDE as > >> >> >> > described > >> >> >> > in > >> >> >> > one > >> >> >> > of the links. That fixed this problem but created a much larger > >> >> >> > problem. > >> >> >> > > >> >> >> > I have 3 columns in a table in word. There is one field per > >> >> >> > column > >> >> >> > merged > >> >> >> > from excel, the three fields are : Product 1, q1 and p1 . Now, > >> >> >> > where > >> >> >> > q1 > >> >> >> > is > >> >> >> > supposed to be, it is putting the data from the field Product 1 > >> >> >> > and > >> >> >> > where > >> >> >> > p1 > >> >> >> > is supposed to be, it is putting the data from q1 and where > >> >> >> > Product > >> >> >> > 1 > >> >> >> > is > >> >> >> > supposed to be it is putting the data from p1. It's got them all > >> >> >> > messed > >> >> >> > up > >> >> >> > somehow in the mapping or something. > >> >> >> > > >> >> >> > I think it was better the other way, at least that was just one > >> >> >> > field > >> >> >> > messed > >> >> >> > up and only sometimes. > >> >> >> > > >> >> >> > any other thoughts? > >> >> >> > > >> >> >> > Thanks, > >> >> >> > Onalee > >> >> >> > > >> >> >> > "Peter Jamieson" wrote: > >> >> >> > > >> >> >> >> It's possible that you have encountered a new problem that is > >> >> >> >> not > >> >> >> >> described > >> >> >> >> at http://tips.pjmsn.me.uk/t0003.htm, but it may be worth trying > >> >> >> >> this > >> >> >> >> suggestion from that page on your Excel sheet (or one of the > >> >> >> >> following > >> >> >> >> suggestions): > >> >> >> >> > >> >> >> >> << > >> >> >> >> 1. Format a column as text via Data|Text to Columns... > >> >> >> >> > >> >> >> >> If you have a mixture of numbers and texts in a column but > >> >> >> >> numbers > >> >> >> >> in > >> >> >> >> the > >> >> >> >> first 8 rows, you may need to get the OLE DB provider to "see" > >> >> >> >> the > >> >> >> >> column > >> >> >> >> as > >> >> >> >> a text column. However, selecting the column and using > >> >> >> >> Format|Cells > >> >> >> >> to > >> >> >> >> set > >> >> >> >> the format as Text does not appear to be enough. One way that > >> >> >> >> does > >> >> >> >> appear > >> >> >> >> to > >> >> >> >> work is > >> >> >> >> - select the column > >> >> >> >> - select the Data|Text to Columns... menu option > >> >> >> >> - click Next through the wizard until you reach Step 3 of 3, > >> >> >> >> then > >> >> >> >> select > >> >> >> >> Text as the Column Data Format. > >> >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> >> -- > >> >> >> >> Peter Jamieson > >> >> >> >> http://tips.pjmsn.me.uk> >> >> >> >> > >> >> >> >> "Onalee" <Onalee[ at ]discussions.microsoft.com> wrote in message > >> >> >> >> news:90971EA1-6A31-42BF-A7B3-3B80DA90F9DA[ at ]microsoft.com... > >> >> >> >> > Hi, > >> >> >> >> > I am merging data from excel into a word document. I am > >> >> >> >> > getting > >> >> >> >> > data > >> >> >> >> > in > >> >> >> >> > word that is showing "12:00AM", however, there is data in the > >> >> >> >> > excel > >> >> >> >> > sheet > >> >> >> >> > and > >> >> >> >> > it is NOT a date and it is not blank - it is part of an > >> >> >> >> > address > >> >> >> >> > line, > >> >> >> >> > such > >> >> >> >> > as > >> >> >> >> > "Deliver to Front Desk" or any kinds of words, but definately > >> >> >> >> > not a > >> >> >> >> > date > >> >> >> >> > and > >> >> >> >> > not blank. > >> >> >> >> > > >> >> >> >> > I have tried re-typing the data in excel, I have even put a " > >> >> >> >> > ' " > >> >> >> >> > in > >> >> >> >> > front > >> >> >> >> > of it to show it is text - nothing gets rid of this . It > >> >> >> >> > doesn't > >> >> >> >> > do > >> >> >> >> > it > >> >> >> >> > on > >> >> >> >> > every field or even every record on the same field, just some |
|
|