Results 1 to 6 of 6

Thread: Locked Excel file

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    238

    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")

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    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!

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    238

    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.

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    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.

  5. #5
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jan 2009
    Posts
    238

    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
  •  



Click Here to Expand Forum to Full Width