Populating multiple excel worksheets(tabs) using ASP and ADO

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
cjscottAsked:
Who is Participating?
 
peterxlaneCommented:
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
 
kevp75Commented:
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
 
deighcCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
deighcCommented:
Here's an example of a nice simple multi-sheet XMLSS output:

http://lists.xml.org/archives/xml-dev/200306/msg00348.html
0
 
peterxlaneCommented:
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
 
cjscottAuthor Commented:
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
 
deighcCommented:
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
 
deighcCommented:
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
 
peterxlaneCommented:
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
 
cjscottAuthor Commented:
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
 
cjscottAuthor Commented:
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
 
kevp75Commented:
so I'm confused.  I thought your question was about updating or inserting into multiple worksheets.  What you have posted updates 1 worksheet?
0
 
cjscottAuthor Commented:
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
 
cjscottAuthor Commented:
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
 
kevp75Commented:
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
 
cjscottAuthor Commented:
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
 
cjscottAuthor Commented:
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
 
peterxlaneCommented:
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
 
cjscottAuthor Commented:
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
 
deighcCommented:
> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.