Read/Extract Excel Data in .NET

[Guest]

I am opening, reading and extracting an Excel spreadsheet in .Net (VB). It seems to work fine, however, when I am finished and I open up Excel and try to open up the file a message pops up "File Now Available", "select Read-Write or Cancel". Its almost like my code is changing the documents attributes. Am I doing something wrong in my code

Any help would be great. Thanks
Diann
PS: I'm not quite sure why, but it seems that I have to use the COM reference for Excel... Is there a .Net reference

Private Sub StartReadingDataFile(ByVal FileName As String
Dim strConn As String = "
Dim strSql As String = "
Dim dsExcel As New DataSet(
Dim xlWb As Excel.Workbook(
Dim xlApp As Excel.Applicatio
xlApp = New Excel.Application(
Dim xlWs As New Excel.Worksheet(
Dim [readOnly] As Object = Fals
Dim i As Intege
Dim ExcelData As New System.Data.OleDb.OleDbDataAdapter(strSql, strConn

'Create Output Data Schem
CreateOutTables(

'Set Up String Command With File Name (data source
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=" & FileName & ";" &
"Extended Properties=""Excel 8.0;IMEX=1;""

'Loop Through each Tab and find tabs which begin with numeric characte
'Build SQ
'You must use the $ after the object you reference in the spreadshee

For Each xlWs In xlApp.Workbooks.Open(FileName, [readOnly]).Worksheet
dsExcel = New DataSet(
strSql = "SELECT * FROM [" & xlWs.Name.ToString & "$]
ExcelData.SelectCommand.CommandText = strSq
ExcelData.SelectCommand.Connection.ConnectionString = strCon
ExcelData.Fill(dsExcel, "Excel"
WriteDataSet(dsExcel
End I
Next xlW

'Free the Object
xlApp.Quit(
xlWb = Nothin
xlWs = Nothin
xlApp = Nothin

MsgBox("Data Processed, Verification Report to Proceed"

End Sub

 

[Guest]

Hi

I could reproduce the same error with the code you have sent. Have done little changes

'Loop Through each Tab and find tabs which begin with numeric characte
'Build SQ
'You must use the $ after the object you reference in the spreadshee

ExcelData.SelectCommand.Connection.ConnectionString = strCon
ExcelData.SelectCommand.Connection.Open()

For Each xlWs In xlApp.Workbooks.Open(FileName, [readOnly]).Worksheet
strSql = "SELECT * FROM [" & xlWs.Name.ToString & "$]
ExcelData.SelectCommand.CommandText = strSq
ExcelData.Fill(dsExcel, "Excel"
'call method to write datase
'WriteDataSet(dsExcel
Next xlW

ExcelData.SelectCommand.Connection.Close(
ExcelData.SelectCommand.Connection.Dispose(
'Free the Object
dsExcel = Nothin
ExcelData = Nothin
xlApp.Quit(
xlWs = Nothin
xlWb = Nothin
xlApp = Nothin

this code works fine, without giving any "read-write " dialogue.

1) Have explicitly opened and closed the connection outside the loop
2) Have set the ExcelData object to nothing, else am able to see the "Excel" application process still running even after closing this sample code application
3) There are 2 declarations of dsExcel, which I have removed

Hope this helps.

Regards
Annapoorni