Ken wrote:
> Hi,
>
> I would not automate excel from a website. This is probably the best
> method
>
> http://support.microsoft.com/kb/319180
>
> Ken
> ------------------------------------------
>
> "Mad Scientist Jr" wrote:
>
> > >From an asp.net web page I want the user to open the results of a SQL
> > query in Excel, as automatically as possible (ie not having to loop
> > through columns, rows, in code).
> >
> > For this, dataset.writexml works great
> > (got the code from
> > http://forums.devx.com/archive/index.php/t-57273.html )
> >
> > The only question I have is, when Excel opens up, it isn't the view I
> > would prefer. It opens as a read-only workbook, I would prefer as an
> > xml list.
> >
> > If I look at the workbooks.open method
> >
> > exc.Workbooks.Open(filename:=Server.MapPath("") &
> > "\my_sql_results.xml")
> >
> > there are additional parameters
> > Open (Filename As String, [UpdateLinks As Object], [ReadOnly As
> > Object], [Password As Object], etc...
> >
> > that I haven't found any documentation on, but might allow me to
> > specify the "xml list" format. I am guessing the "format" parameter
> > would be it, but I don't know what to pass in. Can anyone help?
> >
> > Thanks...
> >
> >
> > PS If I try opening the XML file locally from Excel, it prompts:
> >
> > Open XML
> > Please select how you would like to open this file:
> > (x) As an XML list
> > ( ) As a read-only workbook
> > ( ) Use the XML Source task pane
> > [OK] [Cancel] [Help]
> >
> > I choose the first option and click OK and Excel then prompts:
> >
> > Microsoft Office Excel
> > The specified XML source does not refer to a schema. Excel will create
> > a
> > schema based on the XML source data.
> > [ ] In the future, do not show this message.
> > [OK] [Help]
> >
> > I click OK and it opens in Excel in the format I want.
> >
> > PPS Here is the full code from
> > http://forums.devx.com/archive/index.php/t-57273.html
> >
> > 01-15-2003, 04:04 PM
> > Here's an example. The variable "ds1" in the code below is a DataSet
> > filled
> > with a single DataTable (I used the SQL Server sample pubs database
> > Authors
> > table to test this) . I haven't tested it with multiple DataTables.
> >
> > You need to add a COM reference to the Microsoft Excel Object Library.
> > This
> > sample used the Microsoft Excel 10.0 Object Library -- a version which
> > can
> > read XML files. As Constance noted, you may need to write some other
> > file
> > type to import the data into earlier versions of Excel.
> >
> > ' create a DataSet
> > Dim ds As New ds1()
> >
> > ' choose a file name for the output
> > Dim filename As String = "c:\authors_data.xml"
> >
> > ' open the connection and fill the DataSet
> > ' Sample used "SELECT * FROM Authors" as the query
> > Me.SqlConnection1.Open()
> > Me.SqlDataAdapter1.Fill(ds)
> >
> > ' delete any existing file
> > If File.Exists(filename) Then
> > File.Delete(filename)
> > End If
> >
> > ' save the DataSet in its default XML format
> > ds.WriteXml(filename)
> >
> > ' clean up
> > Me.SqlConnection1.Close()
> > Me.SqlDataAdapter1.Dispose()
> > Me.SqlConnection1.Dispose()
> >
> > ' create an Excel Application object and make it visible
> > Dim exc As New Excel.Application()
> > exc.Visible = True
> >
> > ' open the saved file
> > exc.Workbooks.Open(filename:=filename)
> >
> > ' show it
> > exc.ActiveWindow.Visible = True
> >
> > This uses Excel's defaults for the column headings, etc. and because of
> > the
> > way the DataSet persists itself in XML, the name of the DataSet shows
> > up in
> > the spreadsheet. You could easily get rid of the unwanted values or
> > change
> > column names by either processing the saved XML before loading it into
> > Excel, or by using Excel's object model to delete and modify the data
> > after
> > loading the XML file. Finally, you could accomplish this same result
> > without
> > going through an intermediate file by iterating through the DataSet and
> > stuffing the data directly into Excel worksheet cells.
> >
> >