Closing Excel database by using SQL

[Osvaldo Luis Aveni]

Hello, I made an Excel file with 15 tables by using SQL. I did not use interop, because interop take a lot time, and I can't wait in my process.

Dim ConnReporteExcel As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & NombreExcelAGenerar & "; Extended Properties = 'Excel 12.0 Xml; HDR = Yes'")

In NombreExcelAGenerar I place the name of the file.

I can create the different tables, I can place all the information without any problem.

But when the process finishes, I cant open the Excel file from the file explorer. I have an error message. "Excel can't open the file because the format or the extension is wrong. Look if the file is damage.

I thougt that I could have a mistake closing the connection, because if I CAN OPEN the same file when I finish the vb application without any problem.

Then I thought I didn't close my database properly, but I am closing this like this

ConnReporteExcel.close()

ConnReporteExcel.Dispose()

But the problem went on

I realized that if I open other connection and if I load this information in a DataGrid, after that I can open the Excel File. I haven't got the slightest idea why, but it works.

Look the code:

Dim CarpetaRegistro As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments + "\ScadaTecnotar\Registro"
Dim NombreTabla As String = CarpetaRegistro + "\RegistroDiario.accdb"
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & NombreTabla
Dim MyConn As OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim tables As DataTableCollection
Dim source1 As New BindingSource

MyConn = New OleDb.OleDbConnection
MyConn.ConnectionString = connString
ds = New DataSet
tables = ds.Tables
da = New OleDb.OleDbDataAdapter("Select * from ResumenGeneral", MyConn)
da.Fill(ds, NombreTabla)
Dim view As New DataView(tables(0))
source1.DataSource = view
FormPrincipal.DataGridView1.DataSource = view

MyConn.Close()
MyConn.Dispose()

If I remove only one line like "FormPrincipal.DataGridView1.DataSource = view", I can´t open the Excel File again.

Note that the file in this process is not the same as the Excel.


Can you help me please!!!!!!!??????

Thanks a lot!!!!!

Continue reading...