Exporting to Excel - Also got a related question re macros

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi,

I have created a form and am using a command button with code which will
export all data to an excel spreadsheet and format the spreadsheet how I
want i.e. autofilter etc.


Is it possible to add some code to also format certain cells within a range
(D1:D1200) depending on their text value, please note that the range will
change as records are added/deleted

i.e.
if cell D8 = Group1, set the pattern to Red
if cell D978 = Group18, set the pattern to blue
etc.

Also, is it possible to code an excel macro inrto a new spreadsheet from
Access?
I suspect the answer to this is no, but it's always worth asking

Many Thanks to any who can help and those who have a look!

Andy
 
Andy,

It has been my experience that I can do from access about 95% or what I
can do from excell.

What I have done many times is to create a master spreadsheet that has
most of the formating etc already done and before the export copy the
master to the output name I want the new spreadsheet to be and then
export into it and then open it and select and copy the sections of the
export into the appropriate areas in the pre-formated tabs.

For the conditional formating you may want to try coditional formatting
if there are not too many (more than 3, I beleive). However you can
also run an excell macro from within access, so you could make a macro
that would do what you want and have it included in the master
workbook, then after copying and importing and moving, run the macro
from access.

As to coding the creation of a macro, I am not so sure. the problem
usually comes in of having responses to questions that excell will
want.

For instance I wanted to delete the imported tab from the new
spreadsheet but because excell was requifing an answer to a question
that would NOT come up if the I had made view=false, I had to change
the view to true, with a waring to the user to respond "Yes" to the
requests. Then again maybe I had a verify option set somewhere that I
couldn't find.

A lot of times I will first do all of the code I want to do from access
in excell first as a macro and then copy it over and make the necessary
modifications to make access able to do what I want - This can come
into play when tab names can change etc. and I know what they will be
in access but an excell macro would not know.

I hope this has given you some ideas.

Ron
 
Back
Top