Share Excel with Table

[Peter888]

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!

 

[bobsan42]

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]

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]

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:

Private Sub CommandButton1_Click()

End Sub

... with your cursor between the lines.
4. add this between the two lines:

    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.

 

[Peter888]

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:

Private Sub CommandButton1_Click()

End Sub

... with your cursor between the lines.
4. add this between the two lines:

    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!