Re: how to open an Excel Sprea***/workbook as read only


"Smoreno" <Smoreno@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:08DDACA1-E262-4D5F-B45F-A6A09F1E8007@xxxxxxxxxxxxxxxx
Hello,

I am working on a script that will open an excel spread *** and copy
some
of the records from some cells into an Access database table. The script
fails if the spread*** is in use by somebody else (i.e. file is in use
by
another user or you don't have the proper permissions...)

here is the VB code that I use to open and then read the data from the
excel
spread ***. Is there a way to open the excel file as read only with the
ADODB.Connection method below?

' Open Excel Connection
Set cnxExcel1 = CreateObject("ADODB.Connection")
cnxExcel1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strExcelFile1 & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

' Load ADO Recordset with Excel Data
Set rstExcel1 = CreateObject("ADODB.Recordset")
rstExcel1.Open "Select * from [Sheet1$A9:D10000]", cnxExcel1, 3, 3


Don't use constants. Use defined Enums. It will help. ie,
Const adStateClosed = 0
Const adOpenStatic = 3
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adUseClient = 3
Const adLockReadOnly = 1
...

rstExcel1.Open "Select * From ...", cnxExcel, adOpenStatic, adLockReadOnly

But you still won't be out of the woods. Excel will tend to open Worksheets
with Exclusive access. So if you are trying to chew on a file, an
Interactive User is using, you will still lose most of the time.

AFAIK ADO (or DAO) only works reliability with *closed* xls. There is no
reliable way to determine if a xls is open or not, except by trying and
experiencing an error. Automation may give you better results.

However, I'm not an Excel expert. You may want to post your question in an
Excel Developer's group.

-ralph


.