Solved

Creating a XLS in Classic ASP or VB6

Posted on 2009-04-08
10
1,544 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
  • 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

808 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