Check if Excel file is read-only (1 Viewer)

wildsyp

Registered User.
Local time
Today, 06:35
Joined
Oct 15, 2008
Messages
19
Hi,

I wonder if anybody has a bit of code which will confirm if an excel spreadsheet is read-only.

I need this to check a file before I import data from it. If somebody is in said spreadsheet when the import is attempted it causes an error on the upload.

If I could somehow check if a file was read-only before the import was attempted, this would save a lot of hassle!

Cheers
Paul
 

wildsyp

Registered User.
Local time
Today, 06:35
Joined
Oct 15, 2008
Messages
19
Thanks for your reply SQL_Hell. I have tried this, but it doesn't seem to work in VBA in access 2003. It doesn't seem to like System.IO as a variable. Is there something obvious I am missing here?:confused:
 

DCrake

Remembered
Local time
Today, 06:35
Joined
Jun 8, 2005
Messages
8,632
The fact that an Excel file is open would determine that the file is read only. So taking that on board here is a function that tests to see if an excel file is open or not.

Code:
Function IsXLBookOpen(strName As String) As Boolean
     
     'Function designed to test if a specific Excel
     'workbook is open or not.
     
    Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
     
     'Find/create an Excel instance
    On Error Resume Next
    Set XLAppFx = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        NotOpen = True
        Set XLAppFx = CreateObject("Excel.Application")
        Err.Clear
    End If
     
     'Loop through all open workbooks in such instance
    For i = XLAppFx.Workbooks.Count To 1 Step -1
        If XLAppFx.Workbooks(i).Name = strName Then Exit For
    Next i
     
     'Set all to False
    IsXLBookOpen = False
     
     'Perform check to see if name was found
    If i <> 0 Then IsXLBookOpen = True
     
     'Close if was closed
    If NotOpen Then XLAppFx.Quit
     
     'Release the instance
    Set XLAppFx = Nothing
     
End Function


To test this function try the following

Code:
If IsXLBookOpen("C:\Test\Test.xls") = True Then
    Msgbox "Excel workbook is being used by another user"
End If
 

Users who are viewing this thread

Top Bottom