Sharing workbooks

[leojez]

Hello, I'm creating a desktop spreadsheet file that will be shared with other users. It is quite complex with 8 worksheets and uses macros, VBA code, conditional formatting and a few ActiveX checkboxes. Once the users download the workbook they will be working individually on it, it is not being shared in teams.

I'm unclear on a few issues that may arise from this, hoping for some answers from this friendly group!

1. These users will undoubtedly have different set ups - different versions of excel and there's also Windows vs macs and different OSs. I created the workbook on MS Office 365, Windows 10. I understand that ActiveX maybe an issue for Mac users... Right? Are there any other possible compatability issues I should be aware of?

2. I expect I will be rolling out updates to the workbook with added features and functionality. I'd provide a download link to users of my workbook so they can get the update. The thing is, how can they copy across the data in version 1 to version 2? Is it just a case of copy/paste or is there a better solution?

3. I have been avoiding it nstalling add ons because I don't know if they will be included in the workbook when another user opens it. Will they stay intact or would the users need to download and add on themselves?

4. If I make changes to the options of the workbook, will these stay intact or does it depend on the users default settings?

5. Are there any other concerns I may have overlooked when sharing workbooks?

Many thanks in advance!

 

[rlv01]

1. The only way to know about Mac users is to test on a Mac. If you have users with different Windows regional (country) setups, you could have problems. Again, testing is the answer. The best way to do this is to identify a beta user who is willing to work with you before you do a full release to your user base.

2. Copy/paste, unless you write (VBA) code to automate or otherwise assist with the data transfer. If the issue is trivial, then copy/paste is probably ok. If the transfer of data is going to be a giant hassle for the user, consider automating otherwise you are going to meet huge resistance to updating to the latest version.

3. Add-ons that are required to run your workbook increase support problems, and in general complicate life. I avoid unless absolutely necessary.

4. It depends. I will say that altering a users preferred preferences (things that will apply to any workbook they use) is EXTREMELY bad practice and will usually not be received kindly by the user.

5. Implement a versioning system to track your updates and store the version information somewhere in the workbook, so when someone comes to you with an issue you can determine if they are using the latest update.

 

[leojez]

1. The only way to know about Mac users is to test on a Mac. If you have users with different Windows regional (country) setups, you could have problems. Again, testing is the answer. The best way to do this is to identify a beta user who is willing to work with you before you do a full release to your user base.

2. Copy/paste, unless you write (VBA) code to automate or otherwise assist with the data transfer. If the issue is trivial, then copy/paste is probably ok. If the transfer of data is going to be a giant hassle for the user, consider automating otherwise you are going to meet huge resistance to updating to the latest version.

3. Add-ons that are required to run your workbook increase support problems, and in general complicate life. I avoid unless absolutely necessary.

4. It depends. I will say that altering a users preferred preferences (things that will apply to any workbook they use) is EXTREMELY bad practice and will usually not be received kindly by the user.

5. Implement a versioning system to track your updates and store the version information somewhere in the workbook, so when someone comes to you with an issue you can determine if they are using the latest update.

Wonderful reply, thank you! That's a big help. I will avoid using add ons and changing preferences and will try and find some beta testers. Good advice. As for point 2 (also includes 5) how would one go about automating updates? This sounds really interesting and something I would be keen to peruse.

 

[rlv01]

Wonderful reply, thank you! That's a big help. I will avoid using add ons and changing preferences and will try and find some beta testers. Good advice. As for point 2 (also includes 5) how would one go about automating updates? This sounds really interesting and something I would be keen to peruse.

That depends how the data is structured and where it is located, and is normally highly specific to your own application. A couple of general approaches are:

1. Use the new workbook (WB) to open the old WB and copy the data over. If you keep all the user data in one spot (one worksheet, say) that makes this easier.
2. Have an export mechanism so the old WB can export user data to a text based data file (csv or other). Then the new WB would have an import mechanism to import the data file.

If the data is critical, you need to think through how you protect the user's data. For example, making a backup copy before attempting any upgrade operation. And if your new updated WB has the same name as the user's existing WB, how are you going to be sure that they don't overwrite the old file with the new file before they realize that they need to transfer data from the now-overwritten file? Something to think about.