Tuesday, June 8, 2010

Import Textfile(.txt) to an existing excel file

The following uses ADO.NET to import into an existing Excel Worksheet named ReportSheet. The source
text file is ReportFile.txt.

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;""")


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)


End Function

No comments: