Share Excel with Table

Peter888

New Member
Joined
Aug 15, 2017
Messages
13
I am working on school. I did have students data table and summary on excel. Then I have Network Attached storage and uploading excel file on it. I want my assistant to open the same excel file to be able to view the recent changes on my excel without open and close the excel just to refresh it. So i came up with solution to share it but excel did not allowed me to share it because there was a table on it. Is there any other solution on how to solve it? I just want my assistant to view and print the data but not editing. Opening and Closing solution seems not efficient for me. Do you guys have any suggestion or other solution for it? Thank you in advance!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Hi.
Shared workbooks have a lot of limitations.
There are features you cannot use in a shared workbook.
If you really want to have a shared workbook and you don't need the table object that bad you can convert the table to range.
You will need to rework some code and formulas.
There are also other options which involve some coding and you have to split your workbook in front end and back end.
However if you only need it in a read-only mode you can update the in-memory read-only copy by using a simple vba method:
ThisWorkbook.UpdateFromFile
This will updateb the read-only version to the version last saved on the hard drive. See if it works for you.
 

Peter888

New Member
Joined
Aug 15, 2017
Messages
13
Hi.
Shared workbooks have a lot of limitations.
There are features you cannot use in a shared workbook.
If you really want to have a shared workbook and you don't need the table object that bad you can convert the table to range.
You will need to rework some code and formulas.
There are also other options which involve some coding and you have to split your workbook in front end and back end.
However if you only need it in a read-only mode you can update the in-memory read-only copy by using a simple vba method:
ThisWorkbook.UpdateFromFile
This will updateb the read-only version to the version last saved on the hard drive. See if it works for you.
Hi, Thank you for sharing some suggestion. Can you guide me on how to to dothe VBA Method? I am fine as long my assistant don't have to close and re-open just to see the changes. It's consuming time for us.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Again, there are various possible solutions. And behind the scenes, IMHO, this method does the same as Close / Open the file again. But in terms of efficiency it will come down to pushing a button. If opening the file itself is time consuming and slow because of it's size or network connection - maybe this will not help too much.

1. (Depending on your file vesrion/format) If your workbook is in XLSX format - save it as XLSM or XLSB format. The other option that will work is XLS (but compatibility issues may turn up if your Excel version is newer). Basically you need a file format that can store VBA code.
2. (I assume that your Excel is newer than 2003/2007) Enable the Developer tab (if it is hidden), then click on Insert/ ActiveX Controls / Command button (the first in the group). Select a place for your button and place it there.
3. Right click on the newly created button (The design mode button, next to the Insert, must be enabled) and select View code. VBE will pop-up and you will see something like:
VBA Code:
Private Sub CommandButton1_Click()

End Sub
... with your cursor between the lines.
4. add this between the two lines:
VBA Code:
    With ThisWorkbook
        If .ReadOnly Then .UpdateFromFile
    End With
5. Close VBE and save the file.

Assuming that macros are enabled/allowed when the file is opened in read-only mode - clicking the button will bring the file up to date with the last version saved on the disk.
 
Solution

Peter888

New Member
Joined
Aug 15, 2017
Messages
13
Again, there are various possible solutions. And behind the scenes, IMHO, this method does the same as Close / Open the file again. But in terms of efficiency it will come down to pushing a button. If opening the file itself is time consuming and slow because of it's size or network connection - maybe this will not help too much.

1. (Depending on your file vesrion/format) If your workbook is in XLSX format - save it as XLSM or XLSB format. The other option that will work is XLS (but compatibility issues may turn up if your Excel version is newer). Basically you need a file format that can store VBA code.
2. (I assume that your Excel is newer than 2003/2007) Enable the Developer tab (if it is hidden), then click on Insert/ ActiveX Controls / Command button (the first in the group). Select a place for your button and place it there.
3. Right click on the newly created button (The design mode button, next to the Insert, must be enabled) and select View code. VBE will pop-up and you will see something like:
VBA Code:
Private Sub CommandButton1_Click()

End Sub
... with your cursor between the lines.
4. add this between the two lines:
VBA Code:
    With ThisWorkbook
        If .ReadOnly Then .UpdateFromFile
    End With
5. Close VBE and save the file.

Assuming that macros are enabled/allowed when the file is opened in read-only mode - clicking the button will bring the file up to date with the last version saved on the disk.

It works like a charm. It refresh so fast and not delayed. I dont need to close and re-open excel file anymore. Thank you for the assistance!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,286
Members
418,185
Latest member
snoogz2

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