Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Allowing multiple users to access external data source

0

I have an excel file that is essentially a data dump. Then, there are multiple files/users that acess the data via pivot table (external data source.)

The issue is if one person is refreshing their pivot table/excel file (or they have their file left open after refreshing) and another person happens to want to do the same thing, excel won't let the second person access the data file.

I've tried making the source data file as Shared but that didn't work.

Is there another way to allow more than one user to access the source data file at the same time?

Thanks.

Answer
Discuss

Answers

0

The trick is to make it impossible for the file to be left open. Use VBA to access the file, copy the relevant data to the user's machine and close the file automatically.

Discuss

Discussion

The source data file isn't actually open on the user's machine.

What's happening is that they click on the pivot table to refresh, the pivot table goes to the external source to get the data and blocks out anybody else who happens to want to refresh at the same time. The second user gets a message "External table is not in the expected format." when they try to refresh their pivot table. (Frankly that in itself is a bit confusing.)

I guess what I want to do is, after a person refresh their pivot table, have their connection end after the refresh. But I don't want to permanently disconnect as the file and data need to be updated on a monthly basis.
Cathy (rep: 53) Jun 11, '18 at 11:37 am
Your idea is exactly what I suggest. "The pivot table goes to the external data source". That is the point where the data are downloaded and the connection ended. I'm not familiar with pivot tables and therefore don't know how, exactly, the process you describe works but the pivot table you mention should be on the user's machine and therefore the program triggered and controlled by that machine. It might be VBA that runs there or it is an internal Excel program which could be embedded in VBA in order to take control of it.
Variatus (rep: 4889) Jun 11, '18 at 8:29 pm
Add to Discussion
0

You could try making the file read only.

In windows explorer right mouse on the xl file.

Select properties.

In the general tab click read only. 

Discuss

Discussion

Thanks for the suggestion, unfortunately "Read-Only" doesn't work. 
Cathy (rep: 53) Jun 11, '18 at 11:27 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login