Reading multiple excel file into 1 dataset.

[Phoebe.]

Hi, Good Day!

Reading 1 excel file into a dataset is fine.
How can I read multiple excel with the same data structure into 1 dataset?
How can I append those data?

Can someone help?
Thanks in advanced.

rgds,
Phoebe.

[Cor]

Hi Phoepe,

I never did it or try it, but try to fill it in a dataset, than it should go
like this

\\\watch typos just written here
dim ds as new dataset
dim cmd as xxxcommand(sqlstring,con)
dim da as new xxxdataadapter(cmd)
da.fill(ds, "sheet1")
cmd.command = ("a new sqlstring")
da.fill(ds, "sheet2")
///

I hope this helps

Cor

[Paul Clement]

On Thu, 29 Jan 2004 09:56:32 +0800, "Phoebe." <ph********@hotmail.com> wrote:

¤ Hi, Good Day!
¤
¤ Reading 1 excel file into a dataset is fine.
¤ How can I read multiple excel with the same data structure into 1 dataset?
¤ How can I append those data?
¤
¤ Can someone help?

Assuming that your connection is to an Excel Workbook, the following example, using the UNION
keyword, should work:

"Select * from [Sheet7$] UNION Select * from [Sheet3$]"
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Phoebe.]

Thanks Paul,

Problem solved if those worksheet is in 1 excel file.

Mine is same worksheet but different excel file. i.e. datasource is
different.
Currently I have 5 excel file, so i connect using 5 connection and data
selected using 5 dataadapter and fill in using 5 dataset into 1 datagrid.

msConnectString = New System.Data.OleDb.OleDbConnection(
"provider=Microsoft.Jet.OLEDB.4.0; data source=C:\a.xls; Extended
Properties=Excel 8.0;")

msConnectString = New System.Data.OleDb.OleDbConnection(
"provider=Microsoft.Jet.OLEDB.4.0; data source=C:\b.xls; Extended
Properties=Excel 8.0;")

........
' Select the data from Sheet1 of the workbook.

da1 = New System.Data.OleDb.OleDbDataAdapter( "select * from [Sheet1$]",
msConnectString)

da2 = New System.Data.OleDb.OleDbDataAdapter( "select * from [Sheet1$]",
msConnectString)

.......

dsExcel = New System.Data.DataSet()

da1.Fill(dsExcel)

da2.Fill(dsExcel)

.......

dgExcel.DataSource = dsExcel
Can this be solved by connecting 5 excel file using 1 connenction?
Thanks in advanced.

rgds,
Phoebe.

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:u8********************************@4ax.com...

On Thu, 29 Jan 2004 09:56:32 +0800, "Phoebe." <ph********@hotmail.com> wrote:
¤ Hi, Good Day!
¤
¤ Reading 1 excel file into a dataset is fine.
¤ How can I read multiple excel with the same data structure into 1 dataset? ¤ How can I append those data?
¤
¤ Can someone help?

Assuming that your connection is to an Excel Workbook, the following example, using the UNION keyword, should work:

"Select * from [Sheet7$] UNION Select * from [Sheet3$]"
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Fri, 30 Jan 2004 10:51:44 +0800, "Phoebe." <ph********@hotmail.com> wrote:

¤ Thanks Paul,
¤
¤ Problem solved if those worksheet is in 1 excel file.
¤
¤ Mine is same worksheet but different excel file. i.e. datasource is
¤ different.
¤ Currently I have 5 excel file, so i connect using 5 connection and data
¤ selected using 5 dataadapter and fill in using 5 dataset into 1 datagrid.

You can connect to a specific Workbook source but also include other Workbook sources in your SQL
statement:

"Select * from [Sheet7$] UNION Select * from [Excel 8.0;DATABASE=e:\My
Documents\Book20.xls;HDR=No;IMEX=1].[Table4$]"
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)