Exporting a Word table to Access or Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Useko Netsumi - 26 Sep 2003 21:44 GMT

Hello ~

I need to export a MSWord table to an Access table. The original Word table
contains multiple lines of text in each cell, separated with
carriage-return(line return).
.
I would like to retain these multiple lines within in each destination field
in Access or Excel

I have the following structure in my Word table:

Column1: PhotoID (alphanumeric,single line)
Column2: Image of the photos(jpeg/bmp)
Column3: Description(text, multiline)
Column4: Code#(alphanumeric, single line)
Column5: Notes(text, multiline)

I look forward to your recommendations.

Thanks

PS: I'm using OfficeXP.

Reply to this Message

Pat Garard - 27 Sep 2003 07:34 GMT

Open Excel
Open the Word Document in Word and click INSIDE
the table
Table>Select>Table
EITHER
Edit>Copy
Switch to Excel
Edit>Paste
OR
Point into the selection - Right-Click and HOLD
Drag down to Taskbar, and hover over the Excel Icon
Windows will switch to Excel
Drag up to (say) A1, and DROP.

Where there are multiple lines, you may need to adjust Column widths.

Signature

Hope this helps!

Pat Garard
Australia.
apgarardATbigpondDOTnetDOTau

> Hello ~
>
[quoted text clipped - 18 lines]
>
> PS: I'm using OfficeXP.

Reply to this Message

Useko Netsumi - 27 Sep 2003 17:42 GMT

Thanks Pat. Next is that I'd like to import that table(with multi-line
cells) into an Access Database but the multiple lines give me trouble.

Please advise.

> Open Excel
> Open the Word Document in Word and click INSIDE
[quoted text clipped - 35 lines]
> >
> > PS: I'm using OfficeXP.

Reply to this Message

Pat Garard - 28 Sep 2003 00:00 GMT

Try using a field with Data Type "Memo"

Signature

Hope this helps!

Pat Garard
Australia.
apgarardATbigpondDOTnetDOTau

Reply to this Message

Pat Garard - 28 Sep 2003 01:32 GMT

Try this:
Run Excel
Create a Workbook with a small amount of data, typical
of that (multi-lined) you wish to get into Access.
Having a Header Row would be Good!!
Close Excel.

In Access, create a 'Test' Database.

In the table window, Right-Click and select 'Link Tables...'
In Files of Type: select 'Microsoft Excel'.
Navigate to your Excel Workbook, select and 'Link'.

Your data is now available as a Table in Access.
You can run Queries on it, including Append Queries to
add the data to an existing Table.

Don't forget to make columns in the native Table, that are
going to receive multi-lines, type Memo.

Signature

Hope this helps!

Pat Garard
Australia.
apgarardATbigpondDOTnetDOTau

Reply to this Message

Useko Netsumi - 30 Sep 2003 01:17 GMT

Thanks Pat.

But it still did not work with the multi-line(just to make sure. It is not a
wrap-around multi-line cell but multi-line with a hard CR/LF). Each line
becomes a record therefore it messes up the import.

Lets me try to illustrate:

The below table was created on MS Word 2002

A B C D E

1 N-1 (image1) this is a $1.00 Another
2 multiline multiline
3 cell. cell
4 N-2 (image2) single line $2.00 one liner
5 N-3 (image3) line1 $5.00 lst line
6 line2 2nd line
7 line3.
8
9

Then, I open Excel 2002 workbook, and copy-paste
the above table. I got exactly the above.

A B C D E

1 N-1 (image1) this is a $1.00 Another
2 multiline multiline
3 cell. cell
4 N-2 (image2) single line $2.00 one liner
5 N-3 (image3) line1 $5.00 lst line
6 line2 2nd line
7 line3.
8
9

Therefore when I import them into the Access 2002 database,
each line is a record.

A B C D E

1 N-1 (image1) this is a $1.00 Another <-
record 1
2 multiline multiline
<- record 2
3 cell. cell
<- record 3
4 N-2 (image2) single line $2.00 one liner <-
record 4
5 N-3 (image3) line1 $5.00 lst line <-
record 5
6 line2 2nd line
<- record 6
7 line3.
<- record 7
8
9

What I expect to get is that the following in the Excel/Acess table:

A B C D E

1 N-1 (image1) this is a $1.00 Another }
multiline
multiline } -> record 1
cell.
cell }
2 N-2 (image2) single line $2.00 one liner ->
record 2
3 N-3 (image3) line1 $5.00 lst line }
line2 2nd
} -> record 3

}
4
5
6
7
8
9

Your help is greatly appreciated. I wonder if Microsoft MSCE is watching
and give us the answer.

Thanks.

> Try this:
> Run Excel
[quoted text clipped - 15 lines]
> Don't forget to make columns in the native Table, that are
> going to receive multi-lines, type Memo.

Reply to this Message

Useko Netsumi - 30 Sep 2003 01:41 GMT

hanks Pat.

But it still did not work with the multi-line(just to make sure. It is not a
wrap-around multi-line cell but multi-line with a hard CR/LF). Each line
becomes a record therefore it messes up the import.

Lets me try to illustrate:

The below table was created on MS Word 2002

A B C D E

1 N-1 (image1) this is a $1.00 Another
2 multiline multiline
3 cell. cell
4 N-2 (image2) single line $2.00 one liner
5 N-3 (image3) line1 $5.00 lst line
6 line2 2nd line
7 line3.
8
9

Then, I open Excel 2002 workbook, and copy-paste
the above table. I got exactly the above.

A B C D E

1 N-1 (image1) this is a $1.00 Another
2 multiline multiline
3 cell. cell
4 N-2 (image2) single line $2.00 one liner
5 N-3 (image3) line1 $5.00 lst line
6 line2 2nd line
7 line3.
8
9

Therefore when I import them into the Access 2002 database,
each line is a record.

A B C D E

1 N-1 (image1) this is a $1.00 Another <-rec1
2 multiline multiline
<-rec2
3 cell. cell
<-rec3
4 N-2 (image2) single line $2.00 one liner <-rec4
5 N-3 (image3) line1 $5.00 lst line <-rec5
6 line2 2nd line
<-rec6
7 line3.
<- rec7
8
9

What I expect to get is that the following in the Excel/Acess table:

A B C D E

1 N-1 (image1) this is a $1.00 Another }
2 multiline
multiline }rec1
3 cell.
cell }
4 N-2 (image2) single line $2.00 one liner <rec1
5 N-3 (image3) line1 $5.00 lst line }
6 line2 2nd line }rec3
7 line3. }
8
9

Your help is greatly appreciated. I wonder if Microsoft MSCE is watching
and give us the answer.

Thanks.

> Try this:
> Run Excel
[quoted text clipped - 15 lines]
> Don't forget to make columns in the native Table, that are
> going to receive multi-lines, type Memo.

Reply to this Message

Doug Robbins - Word MVP - 28 Sep 2003 03:24 GMT

Hi Useko,

Use the second method in the article at
http://www.mvps.org/word/FAQs/TblsFldsFms/WordToAccess.htm

Signature

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

> Thanks Pat. Next is that I'd like to import that table(with multi-line
> cells) into an Access Database but the multiple lines give me trouble.
[quoted text clipped - 39 lines]
> > >
> > > PS: I'm using OfficeXP.

Reply to this Message