Solved

Creating a XLS in Classic ASP or VB6

Posted on 2009-04-08
10
1,551 Views
Last Modified: 2013-11-25
Hello,

Does anyone know how to create a .xls file in VB6 or Classic ASP without having Excel installed on the machine?

In addition, as these .xls files must be readable by Excel Viewer, which doesrn't read read HTML / MIME type files, so it must be true .xls files.

Thank you.
0
Comment
Question by:APD_Toronto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 4

Expert Comment

by:rentonc
ID: 24098043
You can use filesystemobject to create a text file and name it with the extension .xls.
Fill the file with HTML tables and content, these will be viewable within Excel.
0
 
LVL 23

Assisted Solution

by:apresto
apresto earned 200 total points
ID: 24098443
This is also known as a CSV. If you want an actual XLS file see here:
http://www.15seconds.com/Issue/970515.htm
Unless i have been away so long that i have missed something :) You will need an external COM component to achieve this,
0
 
LVL 4

Expert Comment

by:rentonc
ID: 24098658
Yes Apresto, but this method outputs directly to page as an Excel file opened within the browser, not to an Excel file saved on the server. I use both methods in many systems.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:APD_Toronto
ID: 24099125
I need to deliver to the user a formatted xls sheet readable also by Excel Viewer.  So csv won't work, and Viewer doesnt read HTML.

How could I use OWC in ASP or VB6?  I have tried the code below in VB6 but I can't find the reference?

In HTML I placed it within <script> tage, it gives me ActiveX can't create object: 'owc.spreadshee'
Dim objS As Object
Set objS = CreateObject("owc.spreadsheet")
 
'Build Header
objS.cells(1, 1).Value = "Project"
objS.cells(1, 2).Value = "Tasks Completed"
objS.cells(1, 2).Value = "Last Status Date"
 
'Add a row of data
objS.cells(2, 1).Value = "MyProject"
objS.cells(2, 2).Value = "5/6" 'Want this to appear in the spreadsheet as a fraction.
objS.cells(2, 2).Value = "2006-05-11 10:21 am" 'Want this to appear as a date and time
 
'Save
objS.activesheet.Export "c:\temp.xls", 0
End Sub

Open in new window

0
 
LVL 23

Expert Comment

by:apresto
ID: 24103232
Ah, i think it could be that owc isnt installed on this server. you can download it from here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=982B0359-0A86-4FB2-A7EE-5F3A499515DD&displaylang=EN
 
 

Reference:
http://www.experts-exchange.com/Web_Development/Miscellaneous/Q_21683358.html?qid=21683358

Open in new window

0
 
LVL 4

Accepted Solution

by:
rentonc earned 300 total points
ID: 24105199
Incorrect, the viewer will read the .xls file perfectly as I am using the very same system here.

Filesystemobject saves the .xls file (which is built up with tables) and the viewer reads it perfectly. This way you don't need to install OWC on the server.
0
 

Author Comment

by:APD_Toronto
ID: 24108075
rentonc:

Are you sure, I have the code below.  The full Excel reads it, but Excel Viewer givse me "Microsoft Excel Viewer cannot open files of this  type".  I'm using Excel Viewer 2003 (11.8237.8221) SP3.

Please help?
<%@ Language=VBScript %>
   <%
      'Change HTML header to specify Excel's MIME content type
      Response.Buffer = TRUE
      Response.ContentType = "application/vnd.ms-excel"
      FPath = "c:\" & fn 
      Response.AddHeader "Content-Disposition", "attachment; filename=123.xls" 
 
 
   %>
   <html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
   <head>
    <style>
 
  <!--table
 
  @page
     {mso-header-data:"&XX Day Absence Report\000ADate\: &D\000APage &P";
	  mso-number-format:"\#\,\#\#0\.00"; 
	  mso-page-orientation:landscape; }
 
  .style0
     {font-size:10.0pt;
	  font-style:normal;
	  font-family:Arial;}
 
  td
     {mso-style-parent:style0;
	  font-size:10.0pt;
	  font-style:normal;
	  font-family:Arial;}
 
  -->
 
	</style>
	
	<!--[if gte mso 9]><xml>
		 <x:ExcelWorkbook>
		  <x:ExcelWorksheets>
		   <x:ExcelWorksheet>
		    <x:Name>Book1</x:Name>
		    <x:WorksheetOptions>
		     <x:Print>
		      <x:ValidPrinterInfo/>
		      <x:HorizontalResolution>1200</x:HorizontalResolution>
		      <x:VerticalResolution>1200</x:VerticalResolution>
		     </x:Print>
		     <x:Selected/>
		     <x:DoNotDisplayGridlines/>
		     <x:ProtectContents>False</x:ProtectContents>
		     <x:ProtectObjects>False</x:ProtectObjects>
		     <x:ProtectScenarios>False</x:ProtectScenarios>
		    </x:WorksheetOptions>
		   </x:ExcelWorksheet>
		  </x:ExcelWorksheets>
		  <x:WindowHeight>15840</x:WindowHeight>
		  <x:WindowWidth>23835</x:WindowWidth>
		  <x:WindowTopX>0</x:WindowTopX>
		  <x:WindowTopY>15</x:WindowTopY>
		  <x:ProtectStructure>False</x:ProtectStructure>
		  <x:ProtectWindows>False</x:ProtectWindows>
		 </x:ExcelWorkbook>
		 <x:ExcelName>
		  <x:Name>Print_Titles</x:Name>
		  <x:SheetIndex>1</x:SheetIndex>
		  <x:Formula>=Book1!$A:$A,Book1!$1:$1</x:Formula>
		 </x:ExcelName>
		</xml><![endif]-->
 
	
   </head>
   <BODY>
   <!-- Our table which will be translated into an Excel spreadsheet -->
   <TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1 border=1
			style="border-right-style: solid; border-right-width: 2;
        		   border-left-style: solid; border-left-width: 2;
        		   border-top-style: solid; border-left-width: 2;
        		   border-bottom-style: solid; border-left-width: 2">
 
  <tr><td WIDTH="145"> <b> # </b> </td> </tr>
 
 <% For i = 1 To 500 %>
 
	<tr><td> <%=i%> </td> </tr>
 
<% Next %>
   </TABLE>
   </BODY>
   </HTML>
	

Open in new window

0
 
LVL 4

Expert Comment

by:rentonc
ID: 24108111
You have to save the file to disk first, currently you are streaming the content you create as a excel document type.

In its simplest for, if you saved a file called example.xls with the code below in it, it would open within the viewer.

Try it and let me know, because this works for me on our excel viewers
<Table>
<tr>
<th>Heeading 1</th>
<td>Result 1</td>
</tr>
</table>

Open in new window

0
 

Author Comment

by:APD_Toronto
ID: 24108239
I copied your code in Notepad and saved as both as .htm and as .xls... Excel reads it, Viewer does not.  Same error.
Can you give me the link where you got your viewer?  Mine may be corrupt, but i did get it from microsoft.com?
0
 

Author Comment

by:APD_Toronto
ID: 24110237
rentoc:
Which Version of Excel Viewer are you using?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

739 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