Still trying to get an answer

[Ed Hawley]

I need to copy an Access table into a word document to be emailed to
clients. Can that be done and if so, how is it accomplished? The table is a
performance report that needs to be inserted into the Word document.

Any help will be appreciated.

Ed

 

[Rob Parker]

Hi Ed,

I don't remember your original post, but here's a few thoughts.

The one-word answer to "Can that be done?" is yes. However, there's lots of
ways of doing what you ask, or (perhaps more correctly) what you want to
accomplish.

If you really want to insert a table into a word document, I suggest you do
some Google searching on the various Access groups (not just
microsoft.public.access.reports); I suggest you try the term "Office
automation", since that's the process of getting Access and Word (or other
Office applications - most commonly Outlook and Excel) to interact. I'm
unable to offer an specific instructions as I don't understand exactly what
you want to do. For example, do you want to insert the whole table into a
document, or only the data for a specific person? If that's really what you
want, then perhaps you might consider whether using Office automation to
insert specific data into bookmarks in an existing Word template document
might be a better approach.

I also suggest that simply inserting an Access table into a Word document is
unlikely to give a result which will be particularly user-friendly. And I
have some difficulty with your statement "the table is a performance report
...." - Access tables and reports are very different objects!

Another alternative is to use the SendObject method to send either the table
directly, or (more user-friendly - particularly if the items is being sent
to clients) an Access report based on the table. The SendObject method
offers several alternative output formats, and is very versatile. It's well
documented in the Help file (except that, in A2002, it neglects to mention
the acFormatSNP as one of the possible output formats - it sends the object
as a snapshot file).

When you've done some research, and figured out exactly what you want/need
to do, post again (in an appropriate group) if you get stuck on how to
implement your desired solution.

HTH,

Rob

 

[MikeJohnB]

There are a couple of ways of doing what I think you require.

1. Open the table showing the records, Select Export from the top file menu
and select Rich Text Format *.rtf option from the "Save as File Type" option
window at the bottom of the export window. This will populate a word document
with a table which will allow you to drag the column sizes to fit the text
widths etc.

2, Select send to mail recipient as attachment, this will populate a word
doc with the same format as your table again but will be as an e mail doc,
you need to cut and paste.

I have used the rtf version on occasions and it works well for transmitting
quick reports on data that has not been written to report yet.

I Hope this helps

Mike

--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all
and asked everyone you know,
read the instruction manual.

 

[Ed Hawley]

Rob,

Thanks for your input, what I am trying to do is to copy a table out of an
Access Report form. I do not seem to be able to highlight the table on the
report form so cannot select it. The object is a table of calculations that
I need to move over into a Word document where the table is explained and
discussed. All attempts to date result in jibberish on the word document
with no format at all and only part of the data are moved. I will try to
find out more about using Office automation to accomplish the task.

Thanks Again!
Ed

 

[Ed Hawley]

Rob,

Thanks for the help but when you use the .rtf format, you lose all
formatting for the table. You only get the data. I need to keep the table
formatting, also. I tried the email approach but it would not let me copy
because the email was in the same format as print view in Access. Is there
no way to export it so that it is possible to cut and paste?

Thanks!
Ed

 

[Rob Parker]

Hi Ed,

Well, I've got a somewhat better idea of what you're wanting, but I think
you're labouring under several mis-apprehensions - and I'm still a little
puzzled as to your exact situation.

First, I don't understand what you mean by an "Access Report form". Is this
a form, or a report? They are not the same thing.

Second, neither a form nor a report contain a table. Both are simply
methods of displaying data from a table (or query); what looks like a table
is a set of textbox controls displaying data from the bound fields of the
underlying recordsource. You cannot "highlight the table on the report
form" because there is no such thing!

If you are prepared to do some manual cut/paste (and reformatting, if
necessary to match fonts, etc) to get the data from a and Access table/query
into a table in a Word document, you can use the SendObject method to export
the table (or query) which is the recordsource for your form/report. For
example, the following:

DoCmd.SendObject acSendTable, "tblReport", acFormatRTF, , , , , , False

will open a mail message (in your email program - Outlook, Outlook Express,
whatever) which contains only an attachment, a Word (.rtf) document named
"tblReport.rtf" containing a real (Word) table, which you can select in it's
entirety and copy/paste into your main Word document; the table borders are
resizeable, data is not truncated to fit cell sizes, etc.

If your data is in a query, change acSendTable to acSendQuery. Set the
second parameter (the string within quotes) to the name of your table or
query - whatever is the recordsource of your form/report.

After copy/paste to your document, you can just close the generated email
message without sending it. This will generate an error message (Error
2501), which you can trap and ignore in your code, if you don't want to see
it.

Maybe that's a suitable solution for you; your reply in the other thread to
MikeJohnB seems to idicate that you are willing to do some manual cut/paste
operation(s). If not, then you'll almost certainly need to go the Office
Automation path - and if you do go that way, you'll need reasonably good VBA
coding skills, and an understanding of the object models of both Access and
Word.

HTH,

Rob

 

[Rob Parker]

And an afterthought if you do decide to use the SendObject method, with
manual manipulation afterwards:

If your data is in a table, with Access-friendly fieldnames (eg. no spaces,
etc), you can set up a query to change these to people-friendly form (eg.
with spaces) and export the query instead. You can also change the font,
font style and size (via Format Font), and the cell border colour/style (via
Format Datasheet) for the query - this will all appear in the exported .rtf
file.

About the only thing I can't tell you how to set in the .rtf file is the
grey shading in the table heading row (the table/query fieldnames/aliases).
Sorry about that - you'll have to change it manually ;-)

Again, HTH,

Rob

Hi Ed,

<snip>

 

[MikeJohnB]

Hi Ed, What version of Access you using then?

I'm using Access 2k3 and I can export as RTF file in table format direct to
file. On opening the rtf file with word, it is in table format, that is all
words are in a cell, you can select the table and add boarders.

Is the first row grey?

There is a check box which says something like save format is this checked,
(I cant uncheck mine.)

There are other options available, export to txt file and research the
wizard which pops up. I have managed to export to Word using this option in a
sort of table format. Select all the text and try converting text to table
using the table menu/Convert.

I am not sure if there is a way via export to excell and then exporting to
word, this option is again available to you but it is a bit of a long way
round.

Please let me know what version of Access you are using, I cant remember if
I was able to do this with Access 97 or 2k but its certainly possible with
Access 2k3

Unfortunately, I cannot post the resultant table demo here but I assure you
I have tried my recommendation prior to posting this reply.

How many times do you think you will be doing this, I use the option only
rarely, prefering to send reports as snapshot views from reports these days.

If you are only doing this only once and your database is not secure, I
could have a look for you and e mail the resultant table in word back to you.

If so, e mail as name here with @hotmail.com added on the back.

You will need to make your title stand out or you'll fall into the junk mail
trap.

Hope you are able to sort this issue

Best Regards Mike B

--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all
and asked everyone you know,
read the instruction manual.