By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,231 Members | 1,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,231 IT Pros & Developers. It's quick & easy.

How do I create an Excel workbook with multiple worksheets?

P: n/a
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,
Nov 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Hi Iris,

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

http://www.google.com/gr************...******@4ax.com

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????
Thanks in advance.

Regards,

Nov 20 '05 #2

P: n/a
On Tue, 22 Jun 2004 18:57:01 -0700, "Iris" <Ir**@discussions.microsoft.com> wrote:

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=Micros oft.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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #3

P: n/a
Thanks Paul. I will try this.

Iris

"Paul Clement" wrote:
On Tue, 22 Jun 2004 18:57:01 -0700, "Iris" <Ir**@discussions.microsoft.com> wrote:

¤ 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=Micros oft.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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 20 '05 #4

P: n/a
On Tue, 29 Jun 2004 07:42:03 -0700, "Iris" <Ir**@discussions.microsoft.com> wrote:

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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #5

P: n/a
raj
Hi Paul

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

raj

"Paul Clement" wrote:
On Tue, 29 Jun 2004 07:42:03 -0700, "Iris" <Ir**@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.