How do I create an Excel workbook with multiple worksheets?

[Guest]

I have 8 text files (tab delimited) that I would like to import into an Excel workbook as 8 individual worksheets but I cannot find any example code on this subject. Can anyone help me please????

Thanks in advance.

Regards,

 

[Cor Ligthert]

Hi Iris,

Paul Clement gave yesterday a very nice sample in this newsgroup.

http://www.google.com/[email protected]

I hope this helps,

Cor

I have 8 text files (tab delimited) that I would like to import into an

Excel workbook as 8 individual worksheets but I cannot find any example code
on this subject. Can anyone help me please????

 

[Paul Clement]

¤ I have 8 text files (tab delimited) that I would like to import into an Excel workbook as 8 individual worksheets but I cannot find any example code on this subject. Can anyone help me please????
¤

If you use data access methods, tab delimited files require a schema.ini file similar to the
following:

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

An entry would be required for each file to be imported. The file would be placed in the same
location as the text files.

If you are importing into an Excel Workbook and the Worksheets are to be created from the Import
then the following should work:

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

ExcelConnection.Open()

Dim ImportCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [TextImportSheet]
FROM [Text;DATABASE=e:\My Documents\TextFiles].[TabDelimitedFile.txt]", ExcelConnection)

ImportCommand.ExecuteNonQuery()
ExcelConnection.Close()

End Function


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

 

[Guest]

Thanks Paul. I will try this.

Iris

 

[Paul Clement]

¤ Hi Paul:
¤
¤ This code works using VB.net in Visual Studio 2003 only.
¤
¤ I have Visual Basic 6. Can I use this code in Visual Basic 6? If not, what additional components/references do I need? Thanks in advance.
¤

Yes, you would use ADO instead of ADO.NET under VB 6.0:

Function ImportTextToExcel() As Boolean

Dim cnn As New ADODB.Connection
Dim strSQL As String

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=Excel 8.0;"

strSQL = "SELECT * INTO [TextImportSheet] FROM [Text;DATABASE=e:\My
Documents\TextFiles].[TabDelimitedFile.txt]"

cnn.Execute strSQL
cnn.Close

End Function


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

 

[Guest]

Hi Paul

COuld u please tell me how to do otherway arround (.xls to .txt)?

raj