andybrown115
New Member
- Joined
- Jul 13, 2017
- Messages
- 9
Hi there
First time posting a question – I’m usually a scavenger on this (and other) forum and always seem to find an answer to my question. This time, I’m stumped.
This is my first time using data connections, but a brief outline of the setup. There are more connections, but the issue is the same across them all, so for simplicity let’s just say:
Excel workbook (Dashboard) is run on a separate computer displaying on an office TV, with a data connection to a separate worksheet (KPIs) that is open on my desktop at my desk.
The dashboard workbook has two worksheets: The first is the dashboard itself, which has a simple index/match to worksheet1. Worksheet1 contains a table which comes from a data connection to the KPI workbook. Ideally, I wanted to have the data connection on the dashboard refresh every (say) 30 mins, so I can update and save the KPI workbook at my desk. Both files being open at the same time.
My first issue was that if the dashboard was open already, then it would ‘lock’ the KPI sheet. I got around that by this helpful post HERE – opening the data connection as an MS Query rather than an existing connection to an excel file. That solved that.
My issue now is that when the data attempts to refresh on the dashboard, it refreshes the data no problem, but also opens up a read-only version of the KPI workbook. When I try to refresh again, the data ‘seems’ to want to take the data from the newly-opened read-only workbook rather than querying/referencing the actual KPI workbook itself.
I’m sure I’m doing something wrong here, but just can’t see for the life of me what it is. How do I have the dashboard refresh its data without reopening any data connections as read-only files? Any help is greatly appreciated by me and – on account of my stress levels – my wife.
Thanks
Andrew
Ps. I’m sure I have probably gone about this in a roundabout way, so if there are any other suggestions outside the actual question I am all ears!
First time posting a question – I’m usually a scavenger on this (and other) forum and always seem to find an answer to my question. This time, I’m stumped.
This is my first time using data connections, but a brief outline of the setup. There are more connections, but the issue is the same across them all, so for simplicity let’s just say:
Excel workbook (Dashboard) is run on a separate computer displaying on an office TV, with a data connection to a separate worksheet (KPIs) that is open on my desktop at my desk.
The dashboard workbook has two worksheets: The first is the dashboard itself, which has a simple index/match to worksheet1. Worksheet1 contains a table which comes from a data connection to the KPI workbook. Ideally, I wanted to have the data connection on the dashboard refresh every (say) 30 mins, so I can update and save the KPI workbook at my desk. Both files being open at the same time.

My first issue was that if the dashboard was open already, then it would ‘lock’ the KPI sheet. I got around that by this helpful post HERE – opening the data connection as an MS Query rather than an existing connection to an excel file. That solved that.

My issue now is that when the data attempts to refresh on the dashboard, it refreshes the data no problem, but also opens up a read-only version of the KPI workbook. When I try to refresh again, the data ‘seems’ to want to take the data from the newly-opened read-only workbook rather than querying/referencing the actual KPI workbook itself.

I’m sure I’m doing something wrong here, but just can’t see for the life of me what it is. How do I have the dashboard refresh its data without reopening any data connections as read-only files? Any help is greatly appreciated by me and – on account of my stress levels – my wife.

Thanks
Andrew
Ps. I’m sure I have probably gone about this in a roundabout way, so if there are any other suggestions outside the actual question I am all ears!