-
Feb 12th, 2020, 08:36 AM
#1
Thread Starter
Addicted Member
Locked Excel file
For years I have use the code below with no problems. Office 365 update now tells me the file is Read Only. I am wondering how to close it correctly after searching with the code below to populate a datatable. When Process.Start(myFolderName & "\Filename.xlsx") executes I am now told the file is in use by another user.
HTML Code:
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=" & myFolderName & "Filename.xlsx" & ";" &
"Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
con.ConnectionString = connstring
Try
'Clear the Datatable called dt
dt.Clear()
da = New OleDb.OleDbDataAdapter("SELECT TITLE, MEDIUM, FRAMED, SOLD, RECEIVED FROM [Sheet1$] WHERE SOLD LIKE '%YES%' ORDER by TITLE", con)
da.AcceptChangesDuringFill = False
'Fill the data table
da.Fill(dt)
'Bind the DataGrid to the Table
DataGridView1.DataSource = dt
DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Bisque
Catch ex As Exception
MsgBox(ex.Message)
con.Close()
End Try
con.Close()
'Open Excel file
Process.Start(myFolderName & "\Filename.xlsx")
-
Feb 12th, 2020, 09:36 AM
#2
Re: Locked Excel file
I see Excel 12.0 in the string. Is that the version number with O365?
Please remember next time...elections matter!
-
Feb 12th, 2020, 10:00 AM
#3
Thread Starter
Addicted Member
Re: Locked Excel file
You may be right, but if I change that to 16 it cannot find ISAM. My solution is imply to add the code End after Process.Start etc. That way the VB program quits as the excel file loads. I bit clumsy I guess.
-
Feb 12th, 2020, 10:15 AM
#4
Re: Locked Excel file
give this a try
Code:
Option Strict On
Imports System.Data.OleDb
Public Class Form1
Private objConnection As OleDbConnection
Private objCommand As OleDbCommand
Private objDataAdapter As OleDbDataAdapter
Private objDataTable As System.Data.DataTable
Private PathExcelFile As String = "E:\Book1.xlsx"
Private strConnectionString As String = _
"provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & PathExcelFile & ";" _
& "Extended Properties=""Excel 12.0 XML;HDR=Yes"""
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
objConnection = New OleDbConnection(strConnectionString)
'select sheet
objCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", objConnection)
'select Range
'objCommand = New OleDbCommand("SELECT * FROM [Sheet1$C6:K10]", objConnection)
'select with Letter, AD1 and AD2 are Header names in the Excel Sheet
'objCommand = New OleDbCommand("SELECT AD1, AD2 FROM [Sheet1$] Where AD2 Like 'Q%'", objConnection)
objDataAdapter = New OleDbDataAdapter
objDataAdapter.SelectCommand = objCommand
objDataTable = New System.Data.DataTable
objDataAdapter.Fill(objDataTable)
DataGridView1.DataSource = objDataTable.DefaultView
'Clean(up)
objDataAdapter.Dispose()
objDataAdapter = Nothing
objCommand.Dispose()
objCommand = Nothing
objConnection.Dispose()
objConnection = Nothing
End Sub
End Class
hth
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Feb 12th, 2020, 11:01 AM
#5
Re: Locked Excel file
Yes i don't think trying to open the excel directly is a good idea.
I would better go with Chris solution, although I don't think there is a need to do all the disposing ( Haven't tried tho as I don't do the call that way.)
Just closing the connection or use a "using" will do i guess.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Feb 12th, 2020, 11:50 AM
#6
Thread Starter
Addicted Member
Re: Locked Excel file
Will do. Looks a good way forward once I put my own reference lines in.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|