Export data to Excel

[Vanessa]

hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o converting
formatted excel file into web page and then plug in the dynamic data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

Thanks!!

[Larry Bud]

Vanessa wrote:

hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o converting
formatted excel file into web page and then plug in the dynamic data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

1. No, you can only do 1 sheet.
2. I haven't tried it, but you should be able to.

The best way to try these things is to create a workbook, do a Save as
Web Page, choose "sheet" and save it as HTM. Then, if any additional
files were created, delete them, and reopen the HTM in Excel and see
what was preserved. In this case, the simple chart I made was
preserved.

Then just reverse engineer the HTM to see how it's done. Much of the
time you can whittle the XML down to basic components.

Lots of trial and error, but if your client insist on requiring an
Excel file, this is about the only way it can be done.

[Mike Brind]

"Larry Bud" <la**********@y ahoo.comwrote in message
news:11******** **************@ h48g2000cwc.goo glegroups.com.. .

>
Vanessa wrote:

>hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o converting
formatted excel file into web page and then plug in the dynamic data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

1. No, you can only do 1 sheet.

No, you can write to any sheet in the book. You can even use DDL to create
new worksheets and write to them.

--
Mike Brind

[Vanessa]

Mike,
Can you provide simple codes or link on how to write data to multiple
sheets? THANKS!!!!

"Mike Brind" wrote:

"Larry Bud" <la**********@y ahoo.comwrote in message
news:11******** **************@ h48g2000cwc.goo glegroups.com.. .

Vanessa wrote:

hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o converting
formatted excel file into web page and then plug in the dynamic data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

1. No, you can only do 1 sheet.

No, you can write to any sheet in the book. You can even use DDL to create
new worksheets and write to them.

--
Mike Brind

[Mike Brind]

Create a workbook called Book1.xls, and put it in the same directory as the
following script, making sure you have set the appropriate permissions on
the directory:

<%
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source="_
& Server.Mappath( "Book1.xls" ) & ";Extended Properties=""Ex cel
8.0;HDR=Yes;"""
Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open strConn
sql = "Create Table Test1 (Username TEXT, Surname TEXT)"
Conn.execute(sq l)
sql = "Insert INTO [Test1$] (Username, Surname) VALUES ('Mike', 'Brind')"
Conn.execute(sq l)
sql = "Create Table Test2 (Username TEXT, Surname TEXT)"
Conn.execute(sq l)
sql = "Insert INTO [Test2$] (Username, Surname) VALUES ('Mike', 'Brind')"
Conn.execute(sq l)
Conn.Close
Response.Write "Done"
%>

--
Mike Brind
"Vanessa" <Va*****@discus sions.microsoft .comwrote in message
news:A7******** *************** ***********@mic rosoft.com...

Mike,
Can you provide simple codes or link on how to write data to multiple
sheets? THANKS!!!!

"Mike Brind" wrote:

>"Larry Bud" <la**********@y ahoo.comwrote in message
news:11******* *************** @h48g2000cwc.go oglegroups.com. ..

>
Vanessa wrote:
hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o
converting
formatted excel file into web page and then plug in the dynamic data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

1. No, you can only do 1 sheet.

No, you can write to any sheet in the book. You can even use DDL to
create
new worksheets and write to them.

--
Mike Brind

[Mike]

Some more stuff on dealing with Excel from ASP pages:

If you create a workbook in the manner suggested by Larry, it will not be
accessible via the Jet driver. You will get an error stating that the file
is not in the format expected. This can be a problem sometimes if you want
to import the file into another app to do something further with it. The
same is true of reverse-engineered Word docs. For example, Quark refuses to
recognise them as genuine Word docs and opens them up as HTML.

I haven't found a way to add worksheets to the reverse-engineered version of
an xls file, which supports Larry's observation that you can only write to
one sheet using the method he suggested.

With a genuine workbook, you can also access the file using ADOX to
establish the number of tables etc. Apparently, you can use DDL to DROP
tables (worksheets) but when I tried, they steadfastly remained in the
workbook. No error was reported.

The following can be adapted to VBScript easily enough:

http://support.microsoft.com/kb/303814/EN-US/

I haven't tried this, but there appears to be another way to achieve what
you want:

http://www.4guysfromrolla.com/webtech/022801-1.shtml
--
Mike Brind
"Mike Brind" <pa*******@hotm ail.comwrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

Create a workbook called Book1.xls, and put it in the same directory as
the following script, making sure you have set the appropriate permissions
on the directory:

<%
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source="_
& Server.Mappath( "Book1.xls" ) & ";Extended Properties=""Ex cel
8.0;HDR=Yes;"""
Set Conn = Server.CreateOb ject("ADODB.Con nection")
Conn.Open strConn
sql = "Create Table Test1 (Username TEXT, Surname TEXT)"
Conn.execute(sq l)
sql = "Insert INTO [Test1$] (Username, Surname) VALUES ('Mike', 'Brind')"
Conn.execute(sq l)
sql = "Create Table Test2 (Username TEXT, Surname TEXT)"
Conn.execute(sq l)
sql = "Insert INTO [Test2$] (Username, Surname) VALUES ('Mike', 'Brind')"
Conn.execute(sq l)
Conn.Close
Response.Write "Done"
%>

--
Mike Brind
"Vanessa" <Va*****@discus sions.microsoft .comwrote in message
news:A7******** *************** ***********@mic rosoft.com...

>Mike,
Can you provide simple codes or link on how to write data to multiple
sheets? THANKS!!!!

"Mike Brind" wrote:

>>"Larry Bud" <la**********@y ahoo.comwrote in message
news:11****** *************** *@h48g2000cwc.g ooglegroups.com ...

Vanessa wrote:
hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o
converting
formatted excel file into web page and then plug in the dynamic
data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

1. No, you can only do 1 sheet.

No, you can write to any sheet in the book. You can even use DDL to
create
new worksheets and write to them.

--
Mike Brind

[Larry Bud]

Mike Brind wrote:

"Larry Bud" <la**********@y ahoo.comwrote in message
news:11******** **************@ h48g2000cwc.goo glegroups.com.. .

Vanessa wrote:

hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o converting
formatted excel file into web page and then plug in the dynamic data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

1. No, you can only do 1 sheet.

No, you can write to any sheet in the book. You can even use DDL to create
new worksheets and write to them.

If I understand Vanessa right, not the way she wants to do it.

If you change the content type to Excel:

Response.Conten tType = "applicatio n/vnd.ms-excel"

You cannot make more than 1 sheet.

And using OWC is frought with problems.

[Mike Brind]

"Larry Bud" <la**********@y ahoo.comwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .

>
Mike Brind wrote:

>"Larry Bud" <la**********@y ahoo.comwrote in message
news:11******* *************** @h48g2000cwc.go oglegroups.com. ..

>
Vanessa wrote:
hi Everyone,

I have two questions on exporting data to Excel using ASP (w/o
converting
formatted excel file into web page and then plug in the dynamic data):

1. Can we export data into different sheets at Excel?
2. Can we do graph at export?

1. No, you can only do 1 sheet.

No, you can write to any sheet in the book. You can even use DDL to
create
new worksheets and write to them.

If I understand Vanessa right, not the way she wants to do it.

My understanding of the way she wanted to do it is that she didn't want to
reverse-engineer an existing file to html and "plug" dynamic data into it.
At least,that what I assume "w/o [without?] converting formatted excel file
into web page and then plug in the dynamic data" meant. The only
alternative to that that I'm aware of is to open an existing blank document
and write to it.

>
If you change the content type to Excel:

Response.Conten tType = "applicatio n/vnd.ms-excel"

You cannot make more than 1 sheet.

I noted this in another reply.

>
And using OWC is frought with problems.

There's no ideal way to automate any Office product from ASP. All of them
fall short in one way or another.

--
Mike Brind