?
Solved

Populating multiple excel worksheets(tabs) using ASP and ADO

Posted on 2006-04-05
20
Medium Priority
?
1,001 Views
Last Modified: 2012-08-13
I need an old-ASP solution to open an populate multiple excel worksheets with data from a recordset retrieved from a stored procedure. The "HTML-Tables" methodology wont work as it only allows for populating a single worksheet. I am also restricted from using an XML solution. I have heard that this can be done using ADO...

I have seen syntax examples (below) but have yet to get one to function properly.

strSQL = "INSERT INTO [worksheet$] IN '" & strPath & "workbook.xls' 'Excel 2003;' SELECT * FROM tablename"
strSQL = "SELECT * INTO [Excel 2003;Database=" & strPath & "workbook.xls].[worksheet$] FROM tablename"

My thanks in advance to anyone who can offer assistance.
CJ
0
Comment
Question by:cjscott
  • 8
  • 5
  • 4
  • +1
20 Comments
 
LVL 25

Expert Comment

by:kevp75
ID: 16385965
honestly.  I am unsure it can be done.  I know it is possible using Office Web Components, but that method would require the clients all having the OWC installe don their machines....
0
 
LVL 15

Expert Comment

by:deighc
ID: 16391119
You can do this by outputting XML in the XMLSS (XML Spreadsheet) format.

You may an page with a content-type of "application/vnd.ms-excel" then output the correctly formatted XML.

This works for Excel XP and later. Not sure about Excel 2000.

Here's the XMLSS reference from MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xmlss.asp

Because there are huge amounts of formatting options with XMLSS the best way to do this is to manually create the Excel layout you want then click 'Save As' from the file menu and specify a file type of "XML (Spreadsheet)". You can then look at the XML document to see how the formatting is done.
0
 
LVL 15

Expert Comment

by:deighc
ID: 16391136
Here's an example of a nice simple multi-sheet XMLSS output:

http://lists.xml.org/archives/xml-dev/200306/msg00348.html
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 12

Expert Comment

by:peterxlane
ID: 16393166
The only way to do this without XML would be to create multiple worksheets in HTML table format and then create frames code along with JavaScript to make it appear like a multisheet Excel file.  This would be a big old mess!

What do you mean when you say you are restricted from using an XML solution?  I have used the methods suggested by deighc and they work great.
0
 

Author Comment

by:cjscott
ID: 16393265
To all who have responded, thank you for your efforts. I have been told by my superiors to use ADO to update the excel file...

The example below will read from the excel file WorkingCopy.xls

Set ConnXLS = Server.CreateObject("ADODB.Connection")
ConnXLS.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "WorkingCopy.xls" & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
'HDR=No: Indicates that the first row contains data and not columnnames (Yes: Skips first row, No: Uses first Row)
'IMEX=1:  Tells the driver to always read "intermixed" data columns as text

strSQLXLS = "SELECT * FROM [sheetname$]"
set rs = server.CreateObject("ADODB.RecordSet")
rs.Open strSQLXLS, ConnXLS
Do while not rs.EOF
      Response.Write(rs("ColName") & "<br>")
      rs.MoveNext
Loop

If I try to do an insert...

strSQLXLS = INSERT INTO [sheetname$] ('ColName') VALUES ('value')
ConnXLS.Execute(strSQLXLS)

I get:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
0
 
LVL 15

Expert Comment

by:deighc
ID: 16393373
I didn't see this in the original post:

> I am also restricted from using an XML solution.

I can't think how you're restricted from outputting XML from an ASP page. Afterall, it's still just markup text.

cjscott,

You only have a few options to achieve your aim:

1. But a specially built server component that natively understands the Excel object model.

With this you could programmatically build an Excel file on the server. This will work well and achieve what you want, but the only components that I know of that can do this cost serious $$$.

2. Install Excel on the server and use the Excel object model programmatically from your ASP page.

Microsoft strongly you DON'T use Office products for unattended (eg server-side) automation so this is not a good choice.

3. Output XMLSS from your ASP as I described in my first post.

4. Use OWC.

I don't enough about OWC to comment too much on this but it places certain requirements on the end-user. So this isn't a perfect solution if wide scale compatibility is an issue.
0
 
LVL 15

Expert Comment

by:deighc
ID: 16393400
OK, now I'm a bit confused...

In your original post you say

> I need an old-ASP solution to open an populate multiple excel worksheets
> with data from a recordset retrieved from a stored procedure.

Does this mean you ONLY want to update the spreadsheet. Or do you want to both populate the spreadsheet AND output it to the client?
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16393484
This article explains how to do what you are talking about (if I understand you correctly).  It does have an important note near the top of that page:  "IMPORTANT: Though ASP/ADO applications support multi-user access, an Excel spreadsheet does not. Therefore, this method of querying and updating information does not support multi-user concurrent access."  Which in my mind, really defeats the purpose of making it available on the web in the first place.

http://support.microsoft.com/kb/q195951/


ADO is simply a means of accessing data sources.  That requirement does not prevent you from using the XML method of creating the file.  It is barely even different from creating the html tables method.  I currently use this method to create Excel files which are then saved with a .xls extension and nobody even knows that they are Excel files.

I would tell your superiors that you can build it the right way, or the way they want it, and make them pick...
0
 

Author Comment

by:cjscott
ID: 16394053
To: deighc

I was told to use ADO... but now that it is looking more and more problematic, I can probably convince the decision makers to let me use an XML solution. The elusive ADO solution has become a challenge and I've been stubbornly trying to make it work.

It is strange that I can read from the excel file using ADO, but can't seem to insert data. Makes me wonder if there is something missing from my connection string: ConnXLS.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "WorkingCopy.xls" & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

The XML example you sent looks like it will do exactly what I need.
Thank you
0
 

Author Comment

by:cjscott
ID: 16395447
GOT IT...

A post from “gresty” dated 03/01/2001 led me in the right direction. The title of the original question is “Updating an excel spreadsheet”. I included a simple example below. Note: the excel file must have some data in any cell in first row, or in the row below the header row, so that there will be at least one record returned in the recordset.  This data/value will be overwriten by the new data. If your excel file already has records, use rs.MoveLast and then add your new data.

Set ConnXLS = Server.CreateObject("ADODB.Connection")
ConnXLS.Open "xlsDSN" 'system DSN with options Read Only unchecked

set rs = server.CreateObject("ADODB.RecordSet")
rs.ActiveConnection = ConnXLS
rs.CursorType = 3
rs.LockType = 2
rs.Source = "SELECT * FROM [tparhdr$]"
rs.Open

rs.Fields(0).Value="abc"
rs.Fields(1).Value="def"
rs.Fields(2).Value=""
rs.AddNew
rs.Fields(0).Value="ddd"
rs.Fields(1).Value="sss"
rs.Fields(2).Value="642"

rs.Update
Set rs = Nothing
0
 
LVL 12

Accepted Solution

by:
peterxlane earned 2000 total points
ID: 16395570
That is precisely what was suggested on the link that I posted above:  http://support.microsoft.com/kb/q195951/

I take it the multi user concurrent access issue is not a problem?

Glad to hear you got it working
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16395588
so I'm confused.  I thought your question was about updating or inserting into multiple worksheets.  What you have posted updates 1 worksheet?
0
 

Author Comment

by:cjscott
ID: 16400144
To: kevp75

Sorry for the confusion. I posted a very simple example. In the line...
   rs.Source = "SELECT * FROM [worksheetname$]"
...I am opening the worksheet in a recordset. I can then use the same connection to open another worksheet in another recordset, and so on. Thus updating multiple worksheets in a single excel file.
0
 

Author Comment

by:cjscott
ID: 16400212
To: peterxlane

You are correct. I had also found that page on MSDN, but didn't catch on to what was going on in the code example. My bad. The concurrent user issue is not a problem since this is a periodic process run by one user.

I am going to give you the majority of the points since you posted the solution I used. I would like to give some points to "deighc" for his XML example. I hope this is acceptable to you.

My thanks to all who offered thier assistance. I am new to Experts Exchange and this was my first posted question.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 16400257
ahhh...right...gotcha.  sometime I'm a little thick  (ok, so maybe more than a little:) )

Any luck on seeing if the decision makers would let you do it in XML.  That would be your best option for performace
0
 

Author Comment

by:cjscott
ID: 16400293
To: deighs

I must applogize, I had intended to give you 200 points for your XML solution. I am still learning my way around this web site and thought I'd seen a way to split the points among multiple posts. When I accepted peterxlane's post, he got all the points. Guess I owe you one.
0
 

Author Comment

by:cjscott
ID: 16400344
To: kevp75

No problem. I suffer from the same affliction as you can tell from my last few messages. I am new on this job and I think my boss was giving me a brain-teaser/acid-test to see how I'd handle it.
0
 
LVL 12

Expert Comment

by:peterxlane
ID: 16400420
Glad you got it working.

I would definitely recommend taking a look at the XML option as it will definitely come in handy in the future.  The XML support of these Office applications makes things a lot easier.  And if you are using SQL Server as the data source for populating this spreadsheet, I would take a look at DTS.  In my opinion, DTS would be the fastest most reliable method for doing this, and would even allow you to schedule it to run at specified times if needed.
0
 

Author Comment

by:cjscott
ID: 16400627
To: peterxlane

I will definitely be referencing your XML example in the future. I have very little experience with XML and need to get ramped up on it. I will also do some research on DTS as you suggest.

Thank you again for offering your advice and your expertise.
0
 
LVL 15

Expert Comment

by:deighc
ID: 16400818
> I must applogize, I had intended to give you 200 points for your XML solution.

No problem at all. Glad you solved your problem.

And I would add my support to the idea of looking into DTS.

I've been using DTS a lot lately and it's works very well for the sort of task you've described here. If you have the time (and the inclination) then check it out.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question