Read from open Excel file?

[Pendragon]

Access03/WinXP

I've written code to add the names of worksheets into a listbox on a form.
The problem is that it is possible the Excel file may already be open by the
user. If that is the case, the user is prompted with a system message saying
the file is already open, do you want to open it as ready-only, etc etc.
This occurs on the line

set objWkbk = objExcel.Workbooks.Open(strFileName)

I tried objExcel.Workbooks(strFileName) but that gave me a "Subscript out of
range" error.

Is there a way to read information from a file without actually opening it?
In getting the worksheets names, I don't need Excel to open visibly or have
the file open visibly (which I've programmed), and I'd rather not have any
prompts if the file is already open.

Suggestions?

Thanks in advance.

 

[Jim Burke in Novi]

Try opening it this way:

set objWkbk = Workbooks.Open Filename:=strFileName,ReadOnly:=True

I think that should open it and avoid the prompt you're getting.

 

[Piet Linden]

Access03/WinXP

I've written code to add the names of worksheets into a listbox on a form..  
The problem is that it is possible the Excel file may already be open by the
user.  If that is the case, the user is prompted with a system message saying
the file is already open, do you want to open it as ready-only, etc etc.  
This occurs on the line

set objWkbk = objExcel.Workbooks.Open(strFileName)

I tried objExcel.Workbooks(strFileName) but that gave me a "Subscript outof
range" error.

Is there a way to read information from a file without actually opening it?  
In getting the worksheets names, I don't need Excel to open visibly or have
the file open visibly (which I've programmed), and I'd rather not have any
prompts if the file is already open.

Suggestions?

Thanks in advance.

You can use something like this to see if the file is already open:

Private Sub Command3_Click()
Dim objXL As Object
Dim objWkb As Object

Set objXL = GetObject(, "Excel.Application")

For Each objWkb In objXL.workbooks

If objWkb.Name = "ECOG" Then
'stop searching, you found it
Exit For
End If

Next objWkb

Set objXL = Nothing
End Sub

and if it is, just set a reference to it (point objWkb at the file)
Why not just open Excel, but don't make it visible? (Set the visible
property to false).