3

The problem only exists when I open the workbook in a new Excel session and there are other sessions open before

No problem when I open the workbook in the same Excel session as the first Excel session

The problem is that something breakes down:

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

cn.Open strCon

When the last line i.e. cn.open strCon above is executed there opens a read only version of the workbook in the first Excel session and everything breakes down.

This is very strange because it is another session in which the read only version pops up in.

I have seen some people complaining about this issue without a solution e.g.:

http://www.ozgrid.com/forum/showthread.php?t=76156

Nick Perkins
  • 1,327
  • 2
  • 12
  • 25
steinbitur
  • 331
  • 4
  • 9
  • 18
  • As an aside, I consider the option of setting the workbook being opened as a 'Shared Workbook' since this allows multiple users to access in read-write mode, however this doesn't work either. What happens in this case is the workbook is opened as explained above but as [Shared] (that is, writable from Excel but not ADO) – Nick Perkins Aug 17 '14 at 00:20

1 Answers1

0

The best solution I've found so far is to check whether the workbook is open prior to connecting to it. This way there is no reason for the error condition to occur. It does require you to close the connection once you are finished accessing data so that other users can access it. You can use the code in https://stackoverflow.com/a/9373914/453475 to check whether a workbook is locked.

This works in my case as I can connect in, write the data and close the connection after it's written. The plus side is that I know that there aren't two users writing at the same time so there should be no possible conflicts.

I haven't tested how this works if using a read only connection. I'd hope that if you are opening a book read only that you shouldn't have to worry about this limitation but I need to research this more first.

Community
  • 1
  • 1
Nick Perkins
  • 1,327
  • 2
  • 12
  • 25