ADO connection to already open excel workbook?

[tinkythomas]

Hi,

I have sucessfully managed to open and extract data using ADO on closed excel workbooks located on a server. However if the workbooks are already open on another user's pc then they actually physically open on my pc (read-only).

I'm also experiencing memory leaks as I receive errors relating to resources. The code is running from Excel 2007 and I'm copying data from several workbooks (.xlam workbooks) into MS Access. I decided to use ADO because using Automation was far to slow!

I don't believe there are problems with the code I'm using because as mentioned it works fine on closed workbooks.

I can however post the code if needed.

Any help would be appreciated.

 

[tinkythomas]

Hi,

I have managed to resolve this issue by following the steps in this Microsoft KB article I followed step 2.

I added a function to test for open workbooks and copied those that were from the server to local pc and opened a connection from there.

Hope this helps someone?

Regards,

 

[matrixdev]

I'd be interested in what function you used to test for an open excel spreadsheet.
I've used
Open strFileName For Binary Access Read Write Lock Read Write As #1


in VBA to check.
TIA
</SPAN>