Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet Creation - ADO
I posted a question similar to this on another Excel newsgroup, but I think
its wise to post here as it seems to have the most traffic...sorry for reposting! My problem is basically this, I need to create a worksheet that has multiple tables. These tables will be identical in data type content, but will be grouped by various fields. Each of these tables will need to have a summary row or have some seperator between them that must follow the formatting for the spreadsheet. I need to be able create a spreadsheet, copy the formatting details from a template file, insert the data, and then save the spreadsheet. One problem that I have is that I have is that for a couple of the spreadsheets I do not know the number of tables that will be included because it is based on the number of periods that have been reported. What would be the best method for a) creating the template file b) inserting the data c) creating summary rows If what I am trying to do is not possible without using automation, then so be it, but most of these spreadsheets are being created and then attached to emails and sent to clients. Ideally this should be able to be done without any user interaction, so that is why I'm trying to avoid the use of automation. I'm not sure if this will be an issue, but the spreadsheets must be able to be opened by Excel versions 97 - 2003. Thanks for any and all help, it is greatly appreciated. Michael Garcia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet Creation - ADO
Anyone have any ideas? The main area I am stumped on is how I should go
about interfacing the worksheet and writing the multiple tables on the same sheet. Thanks, Michael Garcia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet Creation - ADO
I am using an offset to create a dynamic named range for backwards
compatibility. Now my problem is that one of the spreadsheets the dynamic range will be inserted after all the data has been placed in the document. It will look something like this... Report Header 1Q HEADER / DATA 2Q HEADER / DATA 3Q HEADER Dyamic range for 3q data I'm going to keep working, if anyone has any ideas I would be glad to hear them. -Mike "Michael Garcia" wrote in message ... Jamie, Thanks for the reply. I pretty much decided yesterday that trying to do this server side was just going to be too complex and error prone. I'm going to use automation to create these reports and ADO to read them. One of the spreadsheets I create is going to be for client activity reporting. It will be filled in with previous quarters activity and will leave a place to enter the current quarters activity. I'm thinking of making this a list, but I'm trying to keep this as simple as possible for the clients. Also I have the requirement that this must function on Excel versions 97 - 2003. The computers that the automation will be taking place on will all be running 2003. The question(s) I am getting at is, is a list supported in Excel 97, is it necessary for me to use a list to access the information when the spreadsheet is returned, and is a list really the best solution? I have never used a list before and I'm currently looking over some Excel documentation so hopefully I can figure out these answers, but if anyone has any pointers they would be appreciated. Thanks again, Michael Garcia "Jamie Collins" wrote in message om... "Michael Garcia" wrote ... Anyone have any ideas? The main area I am stumped on is how I should go about interfacing the worksheet and writing the multiple tables on the same sheet. From an ADO perspective, multiple tables on one sheet is not a great idea and copying formatting is not usually accessible. Although it's possible to get round some of limitations you can only get so far. For multiple tables on a sheet, you could use an UPDATE on a specific single row range to create header column then use these in an INSERT INTO..SELECT, again using a restricted range address; however, you may run into problems with data typing unless all columns are of data type TEXT. For formatting, if your table is a simple 'named range' (workbook level defined Name) the formatting will be copied down as the table expands to fit new rows; however a single null value will remove the formatting for the remainder of the column. AFAIK you wouldn't be able to create a new file based on a template with ADO (that said you could do this in code without *Excel* automation). Short answer: I think the chances of achieving your goals without automating Excel are small. Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet Creation - ADO
"Michael Garcia" wrote ...
The question(s) I am getting at is, is a list supported in Excel 97, is it necessary for me to use a list to access the information when the spreadsheet is returned, and is a list really the best solution? I have never used a list before Sorry, I don't understand. What do you mean by a 'list'? Jamie. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet Creation - ADO
A list is an object available in Excel 2003. It is basically a dynamic
range that has autofilter turned on by default. It does not work in Excel 97 or 2000, not sure about 2002. This is not the solution I am going to use because it is not compatible in the older versions. I created dynamic ranges in the two sheets I will be using as input. The problem I am running into now is how to dynamically create a dynamic range! Spreadsheet looks something like this ... Report Header Quarter Header Quarter Data (From DB) Next Quarter Header Dynamic Range for quarters activity to be reported and then read by my program "Jamie Collins" wrote in message om... "Michael Garcia" wrote ... The question(s) I am getting at is, is a list supported in Excel 97, is it necessary for me to use a list to access the information when the spreadsheet is returned, and is a list really the best solution? I have never used a list before Sorry, I don't understand. What do you mean by a 'list'? Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet Creation - ADO
"Michael Garcia" wrote ...
A list is an object available in Excel 2003. It does not work in Excel 97 or 2000, not sure about 2002. This is not the solution I am going to use because it is not compatible in the older versions. I get what you mean now, XML lists and all that: http://msdn.microsoft.com/library/de...03XMLIntro.asp Yes, it is Excel2003 only. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet Function creation? | Excel Worksheet Functions | |||
Table creation help please | Excel Worksheet Functions | |||
Creation of macro with an example | Excel Discussion (Misc queries) | |||
Spreadsheet Creation | Excel Discussion (Misc queries) | |||
.xml database creation | New Users to Excel |