Excel automatically opens data source when I refresh connection

Question

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?

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
– justkrys, Commented Jan 12, 2016 at 22:57
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
– DigitalSea, Commented Jan 12, 2016 at 23:08
Looking forward to somebody helping with this problem! I have the same situation. My two workbooks are connected with a MS Query connection. — ositra
– ositra, Commented May 15, 2018 at 9:09

justkrys · Accepted Answer · 2016-01-12 23:14:55Z

0

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

answered Jan 12, 2016 at 23:14

justkrys

3003 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DigitalSea Over a year ago

Already tried that and the same thing happens. The only way around this I see is "If workbook A is open, Exit Sub Else thisworkbook.refreshall."

justkrys Over a year ago

Yes, maybe myBoolean = IsWorkBookOpen("C:\myWorkbook.xlsm") If myBoolean Then Exit Sub Else refreshall

Collectives™ on Stack Overflow

Excel automatically opens data source when I refresh connection

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related