Export To Excel

[frankvfox]

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.Offic e.Core and Microsoft Excel 11.0 Object Library. When I try to
use the CreateObject("E xcel.Applicatio n") 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.Offic e.Core

Public Class Workbooks
Inherits System.Web.UI.P age
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 = "MyMonthlyRepor t" & Session("UserID ")
FileNumber = FreeFile()
FileOpen(FileNu mber, Session("TempDi r") & MyMonthlyReport & ".TXT",
OpenMode.Output )
tmp = "XXXXXXXXXXXXXX XXXXXXXXX" & TAB & "YYYYYYYYYYYYYY YYYYYYYY" &
TAB & "ZZZZZZ"
PrintLine(FileN umber, tmp)
tmp = TAB & TAB & "UUUUUUUUU" & TAB & "MMMMMMMMMMMMMM "
PrintLine(FileN umber, tmp)
FileClose(FileN umber)
xls = CreateObject("E xcel.Applicatio n")
xls.workbooks.o pentext(Session ("TempDir") & MyMonthlyReport &
".TXT", , , , -4142, , True)
xls.activeworkb ook.saveas(Sess ion("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
frankvfox@hotma il.com

[Paul Clement]

Re: Export To Excel

On Wed, 4 Aug 2004 17:03:01 -0700, frankvfox <frankvfox@disc ussions.microso ft.com> wrote:

¤ 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.Offic e.Core and Microsoft Excel 11.0 Object Library. When I try to
¤ use the CreateObject("E xcel.Applicatio n") 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.Offic e.Core
¤
¤ Public Class Workbooks
¤ Inherits System.Web.UI.P age
¤ 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 = "MyMonthlyRepor t" & Session("UserID ")
¤ FileNumber = FreeFile()
¤ FileOpen(FileNu mber, Session("TempDi r") & MyMonthlyReport & ".TXT",
¤ OpenMode.Output )
¤ tmp = "XXXXXXXXXXXXXX XXXXXXXXX" & TAB & "YYYYYYYYYYYYYY YYYYYYYY" &
¤ TAB & "ZZZZZZ"
¤ PrintLine(FileN umber, tmp)
¤ tmp = TAB & TAB & "UUUUUUUUU" & TAB & "MMMMMMMMMMMMMM "
¤ PrintLine(FileN umber, tmp)
¤ FileClose(FileN umber)
¤ xls = CreateObject("E xcel.Applicatio n")
¤ xls.workbooks.o pentext(Session ("TempDir") & MyMonthlyReport &
¤ ".TXT", , , , -4142, , True)
¤ xls.activeworkb ook.saveas(Sess ion("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 ImportTextToExc el() As Boolean

Dim ExcelConnection As New
System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;" & _
"Data Source=e:\My Documents\Book2 0.xls;Extended Properties=""Ex cel 8.0;HDR=NO;""")

ExcelConnection .Open()

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

ImportCommand.E xecuteNonQuery( )
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:

[TabDelimitedFil e.txt]
ColNameHeader=F alse
Format=TabDelim ited
CharacterSet=AN SI


Paul ~~~ pclement@amerit ech.net
Microsoft MVP (Visual Basic)