Excel automatically opens data source when I refresh connection

3
votes

I have two workbooks on a shared network drive:

  • Workbook A (source: Table with data)
  • Workbook B (Pivot Table -Connected to Table in Workbook A)

When a user has workbook A open and another user tries to open workbook B and refreshes the data, it automatically opens workbook A in read-only mode to refresh data. This seems to only happen when workbook A(source) is opened for editing.

How can I prevent this from happening? Would a macro solve this?

1
vbaexceldata-connections
Workbook A would have to be closed for it to not open in read-only mode. As long as nothing in Workbook A needs to be changed, it will still refresh the data in Workbook B whether it's in read-only mode or not. – justkrys
I don't want workbook A to open at all when someone else has it opened. The update needs to happen in the background. – DigitalSea
Looking forward to somebody helping with this problem! I have the same situation. My two workbooks are connected with a MS Query connection. – ositra

1 Answers

0
votes
Application.Screenupdating = False
 'your code here
Application.Screenupdating = True