Import External Data refresh locking source workseet

[xtan]

All,

I've got a macro set up on a worksheet in workbook A, which once an hour is exporting data to a worksheet in workbook B. This is housed on a shared drive. I'm trying to set up Workbook C to have a worksheet setup that imports the data from workbook B, so that management can view hourly updates without having to check their email. I've set a query on workbook C to refresh at regular intervals to pull the updates each hour. The problem is that once workbook C refreshes, it locks workbook b preventing any further writes from workbook A. I've tried setting longer intervals between the refreshes, setting the workbook B as shared, and attempted to change the Mode in the OLE QUERY from Share Deny Right to Read. Any help would be greatly appreciated.

Thanks.

 

[xtan]

Basically it boils down to the autorefresh on workbook c maintaining a connection to workbook b, preventing workbook b from being updated.

 

[Anthony Rose]

I've had exactly the same problem, although I am not using a macro to do the refresh.
I set up some data import queries on certain worksheets, and whenever they refresh, no matter how it is requested or set up, they hold the source file (but it is not in the Workbooks collection.
I have tried making the Mode=Read or Share Deny None, but no luck.
My work around for the moment is to RefreshAll on close of the workbook:
Sub Auto_Close()
ActiveWorkbook.RefreshAll
End Sub
This means that the workbook refreshes, then closes and releases the source file immediately.
Because I use the xls manually, I prefer to have it ask me whether I want to Save or not at this time,
but if you want to control the whole thing from a macro, you should of course save after the RefreshAll.
Perhaps you could schedule Windows to open the workbook.xls, which will automatically refresh and close itself again this way, every hour:
Autpen()
ActiveWorkbook.RefreshAll
Activeworkbook.Save
Activeworkbook.Close
End Sub
Of course that macro would close the workbook every time you opened it
(unless you held down Shift while opening the file from within Exce to avoid running macros).
You could alternatively open and close the workbook from another control workbook's macro...

 

[Anthony Rose]

Silly me, of course you want someone to be able to read it!
So schedule another control workbook with the above Autpen, and add a line
Workbooks.Open("C:...xls")
at the start to activate the target Workbook
and
ThisWorkbook.Close
at the end to finish the control workbook off again.
All very clumsy. But if it works, it'll do until someone comes up with a fix. i can't think of another way.
Please let me know, if you do!
PS I'm on Office 2003.