Export To Excel

[Guest]

I need a code sample using VB ASP.Net 2003 which exports a tab delimited text
file resident on the server into an Excel spreadsheet which will be saved
through the browser to the client's local workstation. I don't need examples
of a Windows application where everything is local.

I've tried KB306022 which doesn't work. I've included references to
Microsoft.Office.Core and Microsoft Excel 11.0 Object Library. When I try to
use the CreateObject("Excel.Application") function I get the message "Can't
create ActiveX component".

I can't find anything relevant to my problem in MSDN. I have a client
wondering when I'm going to deliver his software.

The little test code I've written so far follows:

Imports Microsoft.Office.Core

Public Class Workbooks
Inherits System.Web.UI.Page
Dim TAB As String = Chr(9)

Private Sub MonthlyReportWB()
Dim FileNumber As Integer
Dim MyMonthlyReport As String
Dim tmp As String
Dim xls As Object
MyMonthlyReport = "MyMonthlyReport" & Session("UserID")
FileNumber = FreeFile()
FileOpen(FileNumber, Session("TempDir") & MyMonthlyReport & ".TXT",
OpenMode.Output)
tmp = "XXXXXXXXXXXXXXXXXXXXXXX" & TAB & "YYYYYYYYYYYYYYYYYYYYYY" &
TAB & "ZZZZZZ"
PrintLine(FileNumber, tmp)
tmp = TAB & TAB & "UUUUUUUUU" & TAB & "MMMMMMMMMMMMMM"
PrintLine(FileNumber, tmp)
FileClose(FileNumber)
xls = CreateObject("Excel.Application")
xls.workbooks.opentext(Session("TempDir") & MyMonthlyReport &
".TXT", , , , -4142, , True)
xls.activeworkbook.saveas(Session("TempDir") & MyMonthlyReport &
".XLS", -4143)
xls.quit()

etc ...

Notice the references to xls in the code above do not result in Intellisense
corrections to case.
Please help. I am extremely frustrated and will lose a client if I can't get
something running here!!

Help!!

Frank Fox
(e-mail address removed)

 

[Paul Clement]

¤ I need a code sample using VB ASP.Net 2003 which exports a tab delimited text
¤ file resident on the server into an Excel spreadsheet which will be saved
¤ through the browser to the client's local workstation. I don't need examples
¤ of a Windows application where everything is local.
¤
¤ I've tried KB306022 which doesn't work. I've included references to
¤ Microsoft.Office.Core and Microsoft Excel 11.0 Object Library. When I try to
¤ use the CreateObject("Excel.Application") function I get the message "Can't
¤ create ActiveX component".
¤
¤ I can't find anything relevant to my problem in MSDN. I have a client
¤ wondering when I'm going to deliver his software.
¤
¤ The little test code I've written so far follows:
¤
¤ Imports Microsoft.Office.Core
¤
¤ Public Class Workbooks
¤ Inherits System.Web.UI.Page
¤ Dim TAB As String = Chr(9)
¤
¤ Private Sub MonthlyReportWB()
¤ Dim FileNumber As Integer
¤ Dim MyMonthlyReport As String
¤ Dim tmp As String
¤ Dim xls As Object
¤ MyMonthlyReport = "MyMonthlyReport" & Session("UserID")
¤ FileNumber = FreeFile()
¤ FileOpen(FileNumber, Session("TempDir") & MyMonthlyReport & ".TXT",
¤ OpenMode.Output)
¤ tmp = "XXXXXXXXXXXXXXXXXXXXXXX" & TAB & "YYYYYYYYYYYYYYYYYYYYYY" &
¤ TAB & "ZZZZZZ"
¤ PrintLine(FileNumber, tmp)
¤ tmp = TAB & TAB & "UUUUUUUUU" & TAB & "MMMMMMMMMMMMMM"
¤ PrintLine(FileNumber, tmp)
¤ FileClose(FileNumber)
¤ xls = CreateObject("Excel.Application")
¤ xls.workbooks.opentext(Session("TempDir") & MyMonthlyReport &
¤ ".TXT", , , , -4142, , True)
¤ xls.activeworkbook.saveas(Session("TempDir") & MyMonthlyReport &
¤ ".XLS", -4143)
¤ xls.quit()
¤
¤ etc ...
¤
¤ Notice the references to xls in the code above do not result in Intellisense
¤ corrections to case.
¤ Please help. I am extremely frustrated and will lose a client if I can't get
¤ something running here!!
¤

Not sure if this will help but I would eliminate Excel from the import/export equation because I
think you're going to have some real difficulty getting this to work through the browser considering
the client/server side issues when automating an Office application. You may want to try the
import/export directly using ADO.NET. Here is an example:

Function ImportTextToExcel() As Boolean

Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel 8.0;HDR=NO;""")

ExcelConnection.Open()

Dim ImportCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ReportSheet] (F1, F2,
F3, F4, F5, F6, F7, F8, F9, F10) SELECT * FROM [Text;HDR=NO;DATABASE=E:\My
Documents\TextFiles].[ReportFile.txt]", ExcelConnection)
'Dim ImportCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [TextImportSheet]
FROM [Text;DATABASE=e:\My Documents\TextFiles].[TabDelimitedFile.txt]")

ImportCommand.ExecuteNonQuery()
ExcelConnection.Close()

End Function

Comma separated files (.csv) are easier to work with and are recognized natively by Excel, but if
you have to use a tab delimited file then you will also need a schema.ini file to perform the
import. The schema.ini file is placed in the same location as the text file and would contain the
following:

[TabDelimitedFile.txt]
ColNameHeader=False
Format=TabDelimited
CharacterSet=ANSI


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)