Generating Data Reports in Excel
 

Hi,

I have a question about spreadsheets in Excel. I need to be able to export
data from SQL server into two workbooks in an excel spreadsheet. I have this
working fine, but I now need to format the data to make it look prettier.
This mainly consists of colouring certain rows depending on their location
within the spreadsheet report, and possibly adding some expanding and
contracting regions to some areas of the report.. What would be the best way
to accomplish this? Would it be as part of the report generation, or would I
be better off having some code that runs on the spreadsheet the first time
it's opened that parses through the data and applies the formatting?

Regards,

Tim.

Generating Data Reports in Excel
 

For the formatting, you should be able to use conditional formatting. To get
it to keep the format for a particular location, regardless of the number of
rows returned by your query, you can use the ROW() and COLUMN() functions; so
your conditional formula could be something like
=AND(ROW(A1)=4,COLUMN(A1)=5)
If you place that in Cell A1 and copy it throughout your query range it will
apply the format to cell E4 regardless of how much the data may shift around.
Be sure to use relative addresses, though!

For "expanding and contracting" regions - what do you want, cells that are
wider/higher, or actually inserting rows/columns? Either way I think this
would be best done with VBA code that runs after the QueryTable gets updated,
but the specifics would depend on exactly what you want to do.


"Tim Payne" wrote:

Hi,

I have a question about spreadsheets in Excel. I need to be able to export
data from SQL server into two workbooks in an excel spreadsheet. I have this
working fine, but I now need to format the data to make it look prettier.
This mainly consists of colouring certain rows depending on their location
within the spreadsheet report, and possibly adding some expanding and
contracting regions to some areas of the report.. What would be the best way
to accomplish this? Would it be as part of the report generation, or would I
be better off having some code that runs on the spreadsheet the first time
it's opened that parses through the data and applies the formatting?

Regards,

Tim.

Generating Data Reports in Excel
 

Thanks for the info, I shall have a look at that and see if I can get
something working.

:)

For the expaanding and contracting stuff, it's basically a hierarchical set
of stats, and the client would like to be able to expand and contract the
different levels of stats. Again, this would have to be dynamic, as the data
would vary each time the report is run. The data is already formatted in a
hierarchical fashion in the spreadsheet (SQL Server does all of the work for
this and just drops the data into the spreadhseet). At the moment my line of
thinking is that this would have to be VBA code, as you suggest, that get's
run by the spreadsheet when it's opened....... Do you have any useful links
or pointers on how to do this, as it's not something I've had to do before!

Regards,

Tim.