?
Solved

Creating a XLS in Classic ASP or VB6

Posted on 2009-04-08
10
Medium Priority
?
1,555 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 400 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
Technology Partners: 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!

 

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 600 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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

770 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