how to check if a file is open??

[Painkiller]

I want to make a connection with a database file or excel worksheet file in VB. Problem is, I want to check if the file is already open (that is, being used by another program like acces, excel etc.). Anybody know how to do this?

Much appreciated.

 

[sunaj]

Hi,

-----------------------------------------------------
On Error GoTo ErrHndl:

'open your file here

Exit sub

ErrHndl:
MsgBox (Err.Number & " " & Err.Description)
-----------------------------------------------------

If the file is open the open statement will raise an error.

Sunaj

 

[Painkiller]

Hi Sunaj,

Thanks for your reply, but the problem with this is that you can have a file open in excel and you still make a connection to it with a (VB) program. The connection will be slow, but you can still read data. It get's critical though when the user closes excel (and the excel file) because this causes the vb program to crash! So that's why I'm searching for a way to see if a file has already been opened.

 

[PeteG]

The Excel workbook has a readonly property. If somebody already has the file open the property will be 'true' when you try.

 

[Painkiller]

PeteG, thanks for your reply. Could you give a more detailed explanation of how I can test the readonly property? I mean, how can I reach the read only property of the workbook. I use the following code:

strConnection = "Microsoft.jet.OLEDB.4.0"
strFileDirectory = "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\"
strFile = "autoprijzen_mei01.xls;"
strExtraOptions = "Extended Properties=Excel 8.0;" '8.0 = excel office 97, 7.0 = office 95

Set cnExcelConnection = New ADODB.Connection

With cnExcelConnection
.Provider = strConnection
.ConnectionString = strFileDirectory & strFile & strExtraOptions
.Open
End With

Set rsControlRecordset = New ADODB.Recordset

strSelection = "select * from [lease$]"

rsControleRecordset.Open strSelection, cnExcelConnection, adOpenDynamic, adLockOptimistic

 

[PeteG]

Sorry, I thought you were setting a reference to the actual workbook object. Re your ado connection, have you tried using the 'mode' property? In your 'with/end with' section (but before .open), you could maybe add

.Mode = adModeReadWrite

I'd expect that to fail if the file could not be opened with read and write permissions. I've used something similar to ensure that a database is always opened exclusively.

Or after you've opened the file, you could return/test the value of the mode property. If the returned value was, for example, adModeRead, it would indicate that you had read-only permission which would indicate that somebody else has the file open. I've never used it with connection to an Excel file but it should work.....