excel report

[jas]

our supplier in their wisdom will only accept order forms in excel
format via email which is a right pain to work with. (we cannot change
suppliers!)

i have built an order processing database for the purpose of producing
the end form to email off to the supplier. the test report i created
workes really well if it were to be sent in either snapshot or pdf
format but if i try to export it out in either rtf or excel I lose most
or all of the formatting. the supplier will only accept a certain
format.

can anyone point me to a way of generating a report to get into an excel
spreadsheet from access that would have all the formatting I want to put
into it?

 

[PC Datasheet]

Jas,

You need to create an Excel template with the formatting you need. Then when you
need to send the suplier an order you need to programaticcally make a copy of
the template and assign a file name then you need to programmatically fill the
cells in the order form with data from an Access form. There will be some cells
you need to fill with individual lines of code and other cells (most likely the
line items) you can bulk fill.

If you need help with this, I am in business to provide customers a resource for
help wih Access and Excel, email me at my email address below.

 

[JulieD]

Hi Jas

following on from what PC Datasheet says ... i have done a similar thing in
the past where i have used an Access Make-Table query to create a table that
links to excel as a database query and then feeds into specific cells the
front sheet.

The database query option is found in excel under data / get external data
(or import external data depending on version) / new database query.

So in Access i have a form which provides the criteria for the make-table
query
In excel i have a database query populating the sheet called "Source Data"
then i have a front sheet ("order form") which has formulas that bring the
source data into the appropriate cells.

When i want to do a new order, i open up the access form, set the criteria,
press the button, the table is created, excel is automatically opened to the
appropriate workbook, the database query is updated from the access table
and the front sheet is updated from the linked sheet. Then all you need to
do is select the front sheet, copy / paste special - values to get rid of
the formulas, copy it to its own book and email the book to the supplier.

Hope this helps
Cheers
JulieD