Importing External Data Locks Source Workbook
 

I have imported external data from one workbook to another. Everytime I
refresh the data, no one can open the source file. Is there anyway to
disconnect the ole db query or create something on vb to disconnect it.
I am in a network environment and need other people to be able to access the
file even if I refresh.
Thank you

Importing External Data Locks Source Workbook
 

please be aware of a serious memoryleak when you use
ADODB to query an OPEN workbook.
(see http://support.microsoft.com/kb/319998/en-us

You code should close the connection as soon as
it's retrieved the data, AND you should check that the workbook
is NOT open in the current instance of excel.

I'm not sure on how it will go with shared workbooks.

Excel was never intended for multiuser work, and I would seriously
consider moving your data to an mdb. (data can then be accessed via
queries, users dont need Msaccess.)



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Pablo wrote :

I have imported external data from one workbook to another. Everytime
I refresh the data, no one can open the source file. Is there anyway
to disconnect the ole db query or create something on vb to
disconnect it. I am in a network environment and need other people
to be able to access the file even if I refresh.
Thank you

Importing External Data Locks Source Workbook
 

Thank you for your response!
The excel workbook is not shared.
When I open the second excel document and don't refresh, I can open the
first (source) excel document. Then I close it, refresh the data from the
second document, and when I try to open de first (source) document again, the
document is locked.
Is there anyway that the first document doesn't get locked after refreshing.
I can automate the import with a macro and add code to close or disconnect
from the ole db query, how ever, i don't know the code to do that.
Thank you for your help.

"keepITcool" wrote:


please be aware of a serious memoryleak when you use
ADODB to query an OPEN workbook.
(see http://support.microsoft.com/kb/319998/en-us

You code should close the connection as soon as
it's retrieved the data, AND you should check that the workbook
is NOT open in the current instance of excel.

I'm not sure on how it will go with shared workbooks.

Excel was never intended for multiuser work, and I would seriously
consider moving your data to an mdb. (data can then be accessed via
queries, users dont need Msaccess.)



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Pablo wrote :

I have imported external data from one workbook to another. Everytime
I refresh the data, no one can open the source file. Is there anyway
to disconnect the ole db query or create something on vb to
disconnect it. I am in a network environment and need other people
to be able to access the file even if I refresh.
Thank you

Importing External Data Locks Source Workbook
 

when you select the file in the New Data Query dialog
"Excel Files" etc you can put a checkmark to access the file as
Readonly.

i've just tried it and had no problems.

then i used VBE locals window to view
some properties of the querytable..
dim o
set o = sheet1.querytables(1)
stop

refreshperiod 0
maintainconnection false
robustconnect xlasrequired



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Pablo wrote :

Thank you for your response!
The excel workbook is not shared.
When I open the second excel document and don't refresh, I can open
the first (source) excel document. Then I close it, refresh the data
from the second document, and when I try to open de first (source)
document again, the document is locked.
Is there anyway that the first document doesn't get locked after
refreshing. I can automate the import with a macro and add code to
close or disconnect from the ole db query, how ever, i don't know the
code to do that. Thank you for your help.

"keepITcool" wrote:


please be aware of a serious memoryleak when you use
ADODB to query an OPEN workbook.
(see http://support.microsoft.com/kb/319998/en-us

You code should close the connection as soon as
it's retrieved the data, AND you should check that the workbook
is NOT open in the current instance of excel.

I'm not sure on how it will go with shared workbooks.

Excel was never intended for multiuser work, and I would seriously
consider moving your data to an mdb. (data can then be accessed via
queries, users dont need Msaccess.)



--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Pablo wrote :

I have imported external data from one workbook to another.
Everytime I refresh the data, no one can open the source file. Is
there anyway to disconnect the ole db query or create something
on vb to disconnect it. I am in a network environment and need
other people to be able to access the file even if I refresh.
Thank you