Sharing Datamodel between users?

hakanfa

New Member
Joined
Mar 25, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I need some advise and ideas. I have a huge Excel dashboard containing a data model with +600t records which I update every week. Then I distribute the Excel file to every user that save a copy of the Dashboard.xlsx on their own computer and uses it as such. What I would like to do, is to only share the "data model" as a file to the users. That means that the changes the the user makes to the dashboard.xlsx are not lost every week when the data model is updated.

I prefer not to build any SQL servers containing the data - that is not what I am looking for - jsyut need to find an easy wy to distribute the updated data model (if possible)

Any thoughts or ideas how this could be achieved? Worth mentioning that the users have no knowledge regarding databases etc. nor I can assume that the could activate power pivot and update the data model - not an option either.

Best regards,
Hakan
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
What changes are the users making to the dashboard.xlsx and do you want them to make changes in that file?

Would it just be possible to just add a command button to call some VBA to refresh the query? I have done something similar - I imported and formatted/cleaned data via Power Query, and then have a button on a different page that just calls (refreshes) the query). I would think the query in dashboard.xlsx could query the main database file.

VBA Code:
Public Sub cmd_RefreshQuery_Click()
    SheetName.ListObjects("NameOfQueryTable").DataBodyRange.ClearContents
    Dim idx As Long
    For idx = 0 To 1000000
    Next idx
    SheetName.ListObjects("NameOfQueryTable").QueryTable.Refresh
End Sub
 

hakanfa

New Member
Joined
Mar 25, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
What changes are the users making to the dashboard.xlsx and do you want them to make changes in that file?

Would it just be possible to just add a command button to call some VBA to refresh the query? I have done something similar - I imported and formatted/cleaned data via Power Query, and then have a button on a different page that just calls (refreshes) the query). I would think the query in dashboard.xlsx could query the main database file.

VBA Code:
Public Sub cmd_RefreshQuery_Click()
    SheetName.ListObjects("NameOfQueryTable").DataBodyRange.ClearContents
    Dim idx As Long
    For idx = 0 To 1000000
    Next idx
    SheetName.ListObjects("NameOfQueryTable").QueryTable.Refresh
End Sub
Hi,
thank you for your answer. The users are allowed to make changes and notes etc. Also the data (the raw data) is changing daily (but weekly updates are enough). The problem is that to be able to work as you mentioned they must first open power query, import the new data (refresh the table) from the Excel and then load the datamodel use it - that will not work, too complicated for the users, I'm sorry


best regards,
Hakan
 

severynm

Board Regular
Joined
Jan 8, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Sorry... maybe I was not super clear. The code I provided is for a command button added into the worksheet that will do the work of refreshing the query (see attached image for an example) - all the users have to to is click the button and the data is automatically refreshed. If you do all of the setup work in an initial dashboard.xlsm template to set up the query to reference your database and do the data transformations you want, you can just distribute that template and all of those steps will be applied when the button is pressed. Is that what you are looking for?
 

Attachments

  • RefreshQuery.png
    RefreshQuery.png
    622 bytes · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,447
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top