Data connection opening read-only files on refresh

[andybrown115]

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!

 

[andybrown115]

Pleeeease

 

[ExcelExprt]

I encountered this same problem today, but I found a solution if you are interested. Basically I used the "Existing Connections" button under "Get & Transform Data" to import the data tables from other workbooks into a blank workbook (I had approximately 20 workbooks with 12 tables each and I had to select and import all ~240 tables using the "Existing Connections" button). You then have to create the queries from these tables by selecting "From Table/Range" under "Get & Transform Data". You can then append these queries to create a "master" table. The master table can be refreshed and updated with the source workbooks open at the same time. The source workbooks don't even have to be saved for the master table to update. This was imperative for me, as I can now summarize data across multiple tabs and workbooks as they are being updated in real time. I hope this helps!

 

[DrCheese1023]

I encountered this same problem today, but I found a solution if you are interested. Basically I used the "Existing Connections" button under "Get & Transform Data" to import the data tables from other workbooks into a blank workbook (I had approximately 20 workbooks with 12 tables each and I had to select and import all ~240 tables using the "Existing Connections" button). You then have to create the queries from these tables by selecting "From Table/Range" under "Get & Transform Data". You can then append these queries to create a "master" table. The master table can be refreshed and updated with the source workbooks open at the same time. The source workbooks don't even have to be saved for the master table to update. This was imperative for me, as I can now summarize data across multiple tabs and workbooks as they are being updated in real time. I hope this helps!

can you go through the steps with me a little more in depth?
this is a huge problem im having