Spreadsheet Creation - ADO

[Guest]

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

 

[Jamie Collins]

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.

--

 

[Michael Garcia]

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

 

[Michael Garcia]

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

 

[Jamie Collins]

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.

--

 

[Michael Garcia]

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]

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/d...c_xl2003_ta/html/odc_xl_Excel2003XMLIntro.asp

Yes, it is Excel2003 only.

Jamie.

--