473,320 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Export To Excel

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
fr*******@hotmail.com

Jul 21 '05 #1
1 3022
On Wed, 4 Aug 2004 17:03:01 -0700, frankvfox <fr*******@discussions.microsoft.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.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=Micros oft.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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Jul 21 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table....
5
by: Maria L. | last post by:
Hi, I need to export the content of a DataGrid (in Windows application in C#), into an Excel spreadsheet. Anyone knows how to do this? Any code snippets would help! thanks a lot, Maria
2
by: Siu | last post by:
Hi, I use the following code to export and import a file Excel from resp. into a Web page with the following code: //EXPORT Response.Clear(); Response.Buffer = true; Response.ContentType =...
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.