Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to remove headers when importing from an external source? | Excel Worksheet Functions | |||
External Data Source from Excel Workbook with names | New Users to Excel | |||
Cannot find source of external workbook links | Excel Discussion (Misc queries) | |||
Excel data query locks source file | Excel Discussion (Misc queries) | |||
Importing data from external source | Excel Programming |