Start Free Trial

asked on

OWC - getting arrays into multiple spreadsheets

Now I need some hand holding per https://www.experts-exchange.com/questions/20919538/ASP-Export-to-Excel-with-multiple-pages.html

I am not printing out tables to IE, I am simply creating arrays that hold the values then spitting them out to Excel. So my first question is how would I start using the XML stated in the link above. Do I simply copy and paste the xml before my ASP or after? In between the <% and %> or what?

Then how would I move the data in my array to each worksheet name and then what creates the file? Currently, this is my code which creates an Excel spreadsheet and moves the data into it:

Sub CommentReport_Excel

      Dim p, i

      strSQL = "SELECT Email, Date, Comments FROM mytable"
      Set Query = objConnection.Execute(strSQL)

On Error Resume Next

dim objSpreadsheet
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

p = 1

Do While NOT Query.EOF

            objSpreadsheet.Columns(p).Autofitcolumns
            'objSpreadsheet.Cells(p,1).font.size = "12"
            'objSpreadsheet.Cells(p,1).font.bold = True
            objSpreadsheet.Cells(p,1).Interior.Color = RGB(204,204,213)
            objSpreadsheet.Cells(p,1).Value = Query(0).Value
            objSpreadsheet.Cells(p,2).Interior.Color = RGB(214,216,211)
            objSpreadsheet.Cells(p,2).Value = Query(1).Value
            objSpreadsheet.Cells(p,3).Interior.Color = RGB(33,33,244)
            objSpreadsheet.Cells(p,3).Value = Query(2).Value

      p = p + 1

      Query.MoveNext

loop

Call objSpreadsheet.ActiveSheet.Export("C:\SurveyInfo.xls",0)

End Sub

If i read the post correctly, then the approach used is purely XML. It doesnt involve OWC.

You basically have to build the XML string as shown here..
https://www.experts-exchange.com/questions/20919841/Pseudo-XML-HTML-Excel-Export.html

Cheers!!

ASKER

forget that

ASKER

I mean, is it for sure the only way to do so if using OWC? There has got to be a way to do this through OWC rather than have to resort to XML. First, I have spent 3 days coding my app to export to Excel using OWC, second, I have not a clue how to use XML, third, I don't intend to have to totally recode my Excel export automation in XML now that I have spent 3 days doing it through OWC. This is crazy.

BTW.. thanks a mil for posting this Q,.. Learned something new today.. I wouldnt have come across this if not for you.

Try the sample code posted in the accepted answer at the link provided above.

Cheers!!

ASKER

And the link you posted was the link I am showing up in my original post at the top here.

The links are differnet ;o).. Both were asked by ftb.. but in different topic areas.

As for OWC, never really have got along worjing with OWC as MS recomends using it in webservers. The XML solution seems pretty neat to me though.

You really dont need much knowledge of XML, you are just creating a XML string.. just like you create a table string. The main points to note that it is case sensitive.

Cheers!!

ASKER

Sub NewResponses_Excel2

      Dim array1, i, j, Q

      strSQL = "SELECT CustId, '' as empty1, Email, '' as empty2, CONVERT(CHAR(10), Date, 101), CONVERT(CHAR(10), Sent_Date, 101) FROM mytable where Date BETWEEN '" & Trim(Request.Form("Date1")) & "' AND '" & Trim(Request.Form("Date2")) & "'"

      Set Q = objConnection.Execute(strSQL)

      array1 = Q.GetRows

OpenVanDB

      For i=0 to Ubound(array1,2)
            array1(1,i) = GetCompanyName(array1(0,i))
            array1(4,i) = GetCSRName(array1(3,i))
      Next

CloseVanDB

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>IT Department</Author>
<LastAuthor>IT Department</LastAuthor>
<Created><%=Date()%></Created>
<Company>Vitria</Company>
<Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6135</WindowHeight>
<WindowWidth>8445</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
</Styles>

<Worksheet ss:Name="SurveyReports - <%Date()%>">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table">
<Column ss:Width="150" />
<Column ss:Width="200" />

<%       For b=0 to Ubound(array,1)
            For a=0 to Ubound(array,2) %>

<Row>
<Cell><Data ss:Type="String"><%=array1(b,a)%></Data></Cell>
</Row>

<%             Next
     Next
%>

</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected />
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%

%>
</Workbook>

End Sub

What do you think, will this work and what am I doing wrong here?

ASKER

Where do you specify where to create the Excel file...how is it saving it to a location?

Looking at your original post in this thread what is not working? Is there an error message? Does the file get created but some formating or data is work?

Is this page accessed anonymously? Because if it is then the accoiunt used for security is your iusr account. You didn't give the iusr account permissions to this path did you?

C:\SurveyInfo.xls

Also, I am not totally familiar with OWC I usualy use excel app directly. Could there be permission issues with accessing this object itself?

ASKER

My original post turned into having to use XML. There is nothing wrong with my original post and I never said there was. I posted code to show you how I was doing the writing to excel from ASP. I wanted to know how to write to multiple spreadsheets in which all I have been told is that you must use XML at that point. Now I'm trying my own stab at it via the link where Fritz used XML in his and I just now posted my latest code at my attempt to encorporate XML.

The only problem is, I must take my XML portion out of the <% %> but I can't do this because my ASP page has multiple subs in it and each sub shows different forms. I can't just throw out XML in the open. Plus, I have no need to show the data in an XML table, I just need to take my array and use XML to shove it into multiple Excel spreadsheets.

ASKER

And the example where Fritz is using XML, it appears that he is no longer using OWC. Originally he tried OWC like I did but then came across the same stumbling block...how to move data from ASP to Excel in multiple sheets. I see that Fritz had to start using this form of syntaxt

Response.ContentType = "application/vnd.ms-excel"

rather than OWC:

dim objSpreadsheet
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

which brings me to another question besides if my new XML code works. The question I have in addition is that if you useResponse.ContentType = "application/vnd.ms-excel", that simply throws it into Excel without any formatting. I assume that you must use XML to then format the cell colors, format, etc. in Excel at that point where previously I was using OWC to do this? (e.g. 'objSpreadsheet.Cells(p,1).font.bold = True
objSpreadsheet.Cells(p,1).Interior.Color = RGB(204,204,213))

ASKER

I am referring to Fritz in this link https://www.experts-exchange.com/questions/20919841/Pseudo-XML-HTML-Excel-Export.html

ASKER

There has got to be a way to do this through OWC without using 100% XML but maybe not.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ok, the problem or better yet question is that before I decide to go to XML, I must be sure that I can format the excel cells as needed. I don't want to go to a new way of doing all of this when I don't know if it can be done AND if I am unsure exactly how you are to format the Excel cells when using the XML way. Quite frankly, it took me a long time to find any info on OWC formatting that I show in my initial post.

ASKER

I tried pasting this into a new asp page and just trying the code as you suggested before I even attempt to go back and continue my earlier post which needs to be in a separate file as you stated.

I tried to run that page but it is not finding the page..in other words something is not right in my code. The connection string is in my functions.asp so that should be ok.





Server.ScriptTimeout = 15

%>

<%
Response.ContentType = "application/vnd.ms-excel"
%>

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>IT Dept</Author>
<LastAuthor>IT Dept</LastAuthor>
<Created>1/1/2004</Created>
<Company>CompanyName</Company>
<Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6135</WindowHeight>
<WindowWidth>8445</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
</Styles>
<%
Dim strSQL, Query

strSQL = "SELECT DISTINCT CSR_ID FROM mytable WHERE Date1 IS NOT NULL"
Set Query = objConnection.Execute(strSQL)

While Not Query.EOF

%>
<Worksheet ss:Name="TEST1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
<Cell><Data ss:Type="String">CE Id</Data></Cell>
<Cell><Data ss:Type="String"><%=Query("CSR_ID")%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected />
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%
Query.MoveNext
Loop
Query.Close
%>
</Workbook>

I was talking about the following piece of code..
Just change the parameters for

oCon.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=NorthWind;Data Source=(local);User Id=user;Password=password;"

I assume that you are running on MS SQL and have the Northwind Database in SQL Server.

As for foramtting the cells... I am not quite sure how to go about it. Maybe there is a way.. because everything from last author to the owner seems to be configurable using this method.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

<%@Language="VBScript"%>
<%
'Using the Northwind as an example:
'works for Excel 2000 and Excel 2002

Response.ContentType = "application/vnd.ms-excel"
Function GetIsoDate()
Dim datNow
datNow = Now
GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
End Function

Function PadItem(ByVal value)
value = CStr(value)
If Len(value) = 1 Then
value = "0" & value
End If
PadItem = value
End Function
%><?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Fritz_the_Blank</Author>
<LastAuthor>Fritz_the_Blank</LastAuthor>
<Created><%=GetIsoDate()%></Created>
<Company>ABC Inc</Company>
<Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6135</WindowHeight>
<WindowWidth>8445</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
</Styles>
<%
Dim oCon, oCmd, oRS, oXml, oXslt, oElement
Set oCon = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
Set oRS = Server.CreateObject("ADODB.Recordset")

oCon.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=NorthWind;Data Source=(local);User Id=user;Password=password;"
oCon.Open
Set oCmd.ActiveConnection = oCon
oCmd.CommandText = "SELECT TOP 15 * FROM Orders"
Dim count
count = 0
Set oRS = oCmd.Execute
While Not oRS.EOF
count = count + 1
%>
<Worksheet ss:Name="<%=oRS("OrderId")%>">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
<Cell><Data ss:Type="String">Order Id</Data></Cell>
<Cell><Data ss:Type="String"><%=oRS("OrderId")%></Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Order Date</Data></Cell>
<Cell><Data ss:Type="String"><%=oRS("OrderDate")%></Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Required Date</Data></Cell>
<Cell><Data ss:Type="String"><%=oRS("RequiredDate")%></Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Freight</Data></Cell>
<Cell><Data ss:Type="Number"><%=(oRS("Freight"))%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%
oRS.MoveNext
Wend
oCon.Close
%>
</Workbook>

Cheers!!

ASKER

ooops...yea, he is using northwind....thanks

ASKER

Ok, tried it. It prompted me to save but then wouldnt' allow me. Is it tyring to save to Excel here? It states that it is trying to save an asp page..I don't get it and of course since I can't accomplish this, I can't tell

Error after I click Save As

"Internet Explorer Cannot Downloand mypage.asp from mysite.com
Internet Explorer was not able to open this Internet site. The site requested is either unavailable or cannot be found"

why is it trying to open a site rather than save an Excel file?

Replace

Response.ContentType = "application/vnd.ms-excel"

With

Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
Response.ContentType = "application/vnd.ms-excel"

Also, Make sure that you have provided the correct parametes for the db connection string.

I tried the code at my end.. it works like a dream...

Cheers!!

ASKER

weird, checked my connections, still getting that error. I have my code below and have renamed some stuff for privacy

<%@Language="VBScript"%>
<%
'Using the Northwind as an example:
'works for Excel 2000 and Excel 2002

Response.AddHeader "Content-Disposition", "attachment;filename=invoice.xls"
Response.ContentType = "application/vnd.ms-excel"

Function GetIsoDate()
Dim datNow
datNow = Now
GetISODate = CStr(Year(datNow)) & "-" & PadItem(Month(datNow)) & "-" & PadItem(Day(datNow)) & "T" & _
PadItem(Hour(datNow)) & ":" & PadItem(Minute(datNow)) & ":" & PadItem(Second(datNow)) & "Z"
End Function

Function PadItem(ByVal value)
value = CStr(value)
If Len(value) = 1 Then
value = "0" & value
End If
PadItem = value
End Function
%><?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Fritz_the_Blank</Author>
<LastAuthor>Fritz_the_Blank</LastAuthor>
<Created><%=GetIsoDate()%></Created>
<Company>ABC Inc</Company>
<Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6135</WindowHeight>
<WindowWidth>8445</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
</Styles>
<%
Dim oCon, Query, strSQL

oCon.ConnectionString "Provider=sqloledb;Data Source=mydatasource;Initial Catalog=catalogname;User Id=sa;Password=somepassword;"

oCon.Open

Set Query = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT TOP 15 * FROM mytable"

Dim count
count = 0

Set Query = objConnection.Execute(strSQL)

While Not Query.EOF
count = count + 1
%>
<Worksheet ss:Name="<%=Query("Email_Date")%>">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ID="Table<%=count%>">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
<Cell><Data ss:Type="String">CSR Id</Data></Cell>
<Cell><Data ss:Type="String"><%=Query("CSR_Id")%></Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">Date</Data></Cell>
<Cell><Data ss:Type="String"><%=Query("Email")%></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<% if count = 1 then %>
<Selected />
<% end if %>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<%
Query.MoveNext
Wend
oCon.Close
%>
</Workbook>

ASKER

Why is the filename showing my asp page name and the file type blank when it prompts me to save?

ASKER

and is this line looking on my hard drive? <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>

How did you call this page?

Response.redirect?

Cheers!!

ASKER

I didn't call it, I am just directly loading it via URL...typing in my URL, then this page

>>and is this line looking on my hard drive? <LocationOfComponents HRef="file:///\\OfficeXP\CD1\"/>

Not quite sure.. maybe the location as to where to find the files from the Office CD!

>>I didn't call it, I am just directly loading it via URL...typing in my URL, then this page

hmm.. mine also doesnt seem to work now.. I'll fix it and let you know...

Cheers!!

ASKER

this post is too long and getting of subject from my original post. I am going to start a new thread in ASP forum.