Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating reports from an Excel 2002 worksheet | New Users to Excel | |||
Generating reports with MS Excel 2003 | Excel Discussion (Misc queries) | |||
Generating Simple Reports From A Master Spreadsheet | Excel Worksheet Functions | |||
Generating Reports from Text File | Excel Programming | |||
Generating reports | Excel Programming |