Import Word To Excel Within Access

[Danny Moore via AccessMonster.com]

I have a word document that I need to import to Access which is giving me
trouble. I was wondering if within Access there is a way to get the Word
document, convert it to Excel and then import the Excel version into Access?


Thanks,
Danny

 

[John Nurick]

Usually there's a way, but using Excel may not be the best one. Can you
tell us more about the Word document and the form in which you want to
get it, or its contents, or part of its contents, into an Access table?

 

[Danny Moore via AccessMonster.com]

I'm importing a questionnaire in a word document. The documents are on the
server. They consist of logo, company name, address, paragraph section and
at the bottom of the letter is the set of questions and numbers 1, 2, 3, 4
for them to circle the level of satisfaction.

This letter/questionnaire need to be imported into Access so that I can use
it to post sendRef#'s on it and print it with the sendRef# on it and keep the
data in the table.

If it's better and easier to import word into Access, then that's great. But,
I haven't a clue on how to do it.
I've only worked with importing excel files.

I thank you for any help you can give me to accomplish this task.

Thanks,
Danny

 

[John Nurick]

Hi Danny,

I don't understand. How can the respondents "circle the level of
satisfaction" on a Word document? Are these scanned images of paper
documents?

For that matter, what is a sendRef# and what does it have to do with the
documents?

 

[Danny Moore via AccessMonster.com]

ok. The word document is a canned survey on the server. I need to import
this canned survey that has text and logos, address, body text and
questionnaire questions with level of satisfaction. I need to upload this
questionnaire into Access, print the sendref# on it to indicate that this
survey was sent on this day,etc. Then print it out with this sendref# on it.
Then the printed questionnaire is mailed out. The person circles the
questionnare and mails it back in. I'm able to see from the sendref# that it
was sent out and returned on a certain date. Then I'm able to enter the
results in the database.

The most import part of the process, which I need help with, is importing the
word document that is located on the server into the Access database with
letterhead info and logos.

Is there a way to import a word document and capture this info? If so,
please help me with the process/code needed to import the word document
capturing the letterhead, logos, address, body text and questionnaire info.

Thanks much,
Danny

Hi Danny,

I don't understand. How can the respondents "circle the level of
satisfaction" on a Word document? Are these scanned images of paper
documents?

For that matter, what is a sendRef# and what does it have to do with the
documents?

I'm importing a questionnaire in a word document. The documents are on the
server. They consist of logo, company name, address, paragraph section and

[quoted text clipped - 29 lines]

 

[John Nurick]

I have no idea what you mean by "a canned survey".

From your first message it seemed that you wanted to extract data from
Word documents and get it into the database, but it now seems clear that
you just want to store the Word document containing the letter that is
sent out to the respondents, and then to enter the responses manually
later.

It's possible to store Word documents in an Access table using an OLE
Object field. This works reasonably well for a moderate number of
documents, but the way Access stores them causes the size of the
database to "bloat" out of all proportion and it's not suitable for
large numbers of documents. Often it's better to leave the Word files on
the server where they are, and to store just their names and locations
in the database.

Either way, it sounds as if you'll be sending out the same survey on
multiple occasions with different sendref#'s. In that case, if you leave
the documents on the server, all you'll need is a table to store

SurveyName
SendRef

(Although Access accepts characters such as # in field names, they make
things more complicated and it's best only to use alphanumeric
characters and underscores in the names of fields and other objects.)

If you envisage dissecting the Word documents and store their various
parts (logo, address, paragraphs, questions etc.) in the database so
they can be reassembled - it can be done, but it needs quite advanced
programming skills. If the documents vary in their layout and structure
it can get very complicated.

ok. The word document is a canned survey on the server. I need to import
this canned survey that has text and logos, address, body text and
questionnaire questions with level of satisfaction. I need to upload this
questionnaire into Access, print the sendref# on it to indicate that this
survey was sent on this day,etc. Then print it out with this sendref# on it.
Then the printed questionnaire is mailed out. The person circles the
questionnare and mails it back in. I'm able to see from the sendref# that it
was sent out and returned on a certain date. Then I'm able to enter the
results in the database.

The most import part of the process, which I need help with, is importing the
word document that is located on the server into the Access database with
letterhead info and logos.

Is there a way to import a word document and capture this info? If so,
please help me with the process/code needed to import the word document
capturing the letterhead, logos, address, body text and questionnaire info.

Thanks much,
Danny

Hi Danny,

I don't understand. How can the respondents "circle the level of
satisfaction" on a Word document? Are these scanned images of paper
documents?

For that matter, what is a sendRef# and what does it have to do with the
documents?

I'm importing a questionnaire in a word document. The documents are on the
server. They consist of logo, company name, address, paragraph section and

[quoted text clipped - 29 lines]

Please respond in the newgroup and not by email.

 

[Danny Moore via AccessMonster.com]

Yes, I did envision saving the logo, address, text etc in Access and then
regenerate it with the reference num on it. But it sounds like I can print
the reference number on the word document from the server. You are correct,
there is one word document that will be sent out to a lot of people. The only
difference is that each document will have a different reference number on it
that is generated from within Access. That's way I thought the document
needed to be regenerated in Access.

But it sounds like I can pull up the word doc from the server, assign a
reference number to it and then print it out. How do I attach the reference
numbers to the survey that's on the server from within Access?

The Process:
The word doc is on the server. I pull it in somehow (or just print the ref
num on it) and assign a running reference number that is printed off. The
printed copy is mailed out. The word document is printed off again but with
a new running reference number and so on........

When it is returned, I need to be able to key in the reference number that is
on the returned word doc , pull up the questions for the word document and be
able to capture the answers in Access and then count how many answers where
1's and how many were 2's etc.,

The data entry person will take the returned printed copy and key into Access
the answers 1-5 so that they can be counted later in a report. So, the
questions from the word document need to be saved in Access too at least once.


My pain points are
1. How do I get the reference numbers that are generated in Access onto the
word document that is stored on the server.
2. If I'm only storing the name and reference numbers in Access, how do I
store the questions and levels 1-5 for each question in Access?

Thanks,
Danny

I have no idea what you mean by "a canned survey".

From your first message it seemed that you wanted to extract data from
Word documents and get it into the database, but it now seems clear that
you just want to store the Word document containing the letter that is
sent out to the respondents, and then to enter the responses manually
later.

It's possible to store Word documents in an Access table using an OLE
Object field. This works reasonably well for a moderate number of
documents, but the way Access stores them causes the size of the
database to "bloat" out of all proportion and it's not suitable for
large numbers of documents. Often it's better to leave the Word files on
the server where they are, and to store just their names and locations
in the database.

Either way, it sounds as if you'll be sending out the same survey on
multiple occasions with different sendref#'s. In that case, if you leave
the documents on the server, all you'll need is a table to store

SurveyName
SendRef

(Although Access accepts characters such as # in field names, they make
things more complicated and it's best only to use alphanumeric
characters and underscores in the names of fields and other objects.)

If you envisage dissecting the Word documents and store their various
parts (logo, address, paragraphs, questions etc.) in the database so
they can be reassembled - it can be done, but it needs quite advanced
programming skills. If the documents vary in their layout and structure
it can get very complicated.

ok. The word document is a canned survey on the server. I need to import
this canned survey that has text and logos, address, body text and

[quoted text clipped - 36 lines]

 

[John Nurick]

Hi Danny,

1) For sending one document to a lot of people with different reference
numbers, I'd probably think in terms of driving Word mailmerge from
Access. Is the SendRef the only thing that changes from one printout of
a document to the next, or do you also have to include the recipient's
name and address?

The following links should help with mailmerge:
Albert Kallal's sample mailmerge application:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
Q209976 ACC2000: Using Automation to Run Word Mail Merge from Access
http://support.microsoft.com/?kbid=209976
Q209882 ACC2000: Using Automation to Create a Word 2000 Merge Document
http://support.microsoft.com/?kbid=209882
http://word.mvps.org/faqs/MailMerge/index.htm
http://word.mvps.org/faqs/interdev/GetDataFromDB.htm

2) But if there are a comparatively small number of the Word documents
(the ones you are using as "templates" to send to multiple people), and
you are expecting to send out lots of surveys in the future that would
mean creating new "templates", I would consider cutting Word out of it
and setting up the database so that the letters to be sent out are
created using Access reports. Here one would be saving the components in
Access and regenerating them as they were printed. This way gives you
most flexibility for creating new surveys, re-using existing questions,
and so on. But most of the work in getting the components of the Word
letters into Access tables would have to be done "manually", with a lot
of copying and pasting and a bit of re-typing - unless you're a VBA
whiz.

3) For storing the questions and responses and reporting on the results,
a very good place to start is with Duane Hookom's AtYourSurvey database.
This may seem inscrutable at first but repays study: it provides a
complete structure for inputting, storing and analysing survey data, and
could be modified to include facilities for printing out the survey
letters.

Yes, I did envision saving the logo, address, text etc in Access and then
regenerate it with the reference num on it. But it sounds like I can print
the reference number on the word document from the server. You are correct,
there is one word document that will be sent out to a lot of people. The only
difference is that each document will have a different reference number on it
that is generated from within Access. That's way I thought the document
needed to be regenerated in Access.

But it sounds like I can pull up the word doc from the server, assign a
reference number to it and then print it out. How do I attach the reference
numbers to the survey that's on the server from within Access?

The Process:
The word doc is on the server. I pull it in somehow (or just print the ref
num on it) and assign a running reference number that is printed off. The
printed copy is mailed out. The word document is printed off again but with
a new running reference number and so on........

When it is returned, I need to be able to key in the reference number that is
on the returned word doc , pull up the questions for the word document and be
able to capture the answers in Access and then count how many answers where
1's and how many were 2's etc.,

The data entry person will take the returned printed copy and key into Access
the answers 1-5 so that they can be counted later in a report. So, the
questions from the word document need to be saved in Access too at least once.


My pain points are
1. How do I get the reference numbers that are generated in Access onto the
word document that is stored on the server.
2. If I'm only storing the name and reference numbers in Access, how do I
store the questions and levels 1-5 for each question in Access?

Thanks,
Danny

I have no idea what you mean by "a canned survey".

From your first message it seemed that you wanted to extract data from
Word documents and get it into the database, but it now seems clear that
you just want to store the Word document containing the letter that is
sent out to the respondents, and then to enter the responses manually
later.

It's possible to store Word documents in an Access table using an OLE
Object field. This works reasonably well for a moderate number of
documents, but the way Access stores them causes the size of the
database to "bloat" out of all proportion and it's not suitable for
large numbers of documents. Often it's better to leave the Word files on
the server where they are, and to store just their names and locations
in the database.

Either way, it sounds as if you'll be sending out the same survey on
multiple occasions with different sendref#'s. In that case, if you leave
the documents on the server, all you'll need is a table to store

SurveyName
SendRef

(Although Access accepts characters such as # in field names, they make
things more complicated and it's best only to use alphanumeric
characters and underscores in the names of fields and other objects.)

If you envisage dissecting the Word documents and store their various
parts (logo, address, paragraphs, questions etc.) in the database so
they can be reassembled - it can be done, but it needs quite advanced
programming skills. If the documents vary in their layout and structure
it can get very complicated.

ok. The word document is a canned survey on the server. I need to import
this canned survey that has text and logos, address, body text and

[quoted text clipped - 36 lines]

Please respond in the newgroup and not by email.