Thread: 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

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

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
>

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
> >