MSO 365 Sharepoint and File Sharing with Macros Included Potential Issues Question

%3CLINGO-SUB%20id%3D%22lingo-sub-2409962%22%20slang%3D%22en-US%22%3EMSO%20365%20Sharepoint%20and%20File%20Sharing%20with%20Macros%20Included%20Potential%20Issues%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2409962%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20main%20question%20is%20will%20macros%20or%20Power%20Query%20cause%20Excel%20Workbook%20to%20not%20be%20shareable%20in%20Sharepoint%3F%20If%20so%2C%20is%20there%20a%20way%20around%20it%3F%3C%2FP%3E%3CP%3EI%20created%20a%20file%20that%20includes%205%20sheets%20that%20Power%20Query%20cleans%20and%20appends%20as%20one%20master%20file.%20It%20refreshes%20when%20user%20selects%20Macro%20button.%3C%2FP%3E%3CP%3EI%20also%20have%20a%20Private%20Sub%20Worksheet_Calculate%20routine%20that%20runs%20when%20a%20sheet%20Calculates%20based%20on%20the%20target%20value%20it%20hides%20or%20unhides%20charts.%3C%2FP%3E%3CP%3ECould%20any%20of%20these%20effect%20shareability%20in%20Sharepoint%3F%20The%20file%20is%20actually%20saved%20in%20Teams%20but%20Teams%20stores%20it%20in%20Sharepoint.%20I%20have%20had%20some%20off%20and%20on%20issues%20with%20users%20running%20into%20save%20warnings%20and%20errors%20where%20it%20asks%20one%20user%20if%20they%20want%20overwrite%20or%20Save%20a%20copy%20and%20the%20other%20user%20it%20just%20won't%20let%20them%20save.%3C%2FP%3E%3CP%3EMy%20thought%20process%20leads%20me%20to%20believe%20that%20if%20two%20users%20are%20in%20the%20file%20and%20each%20makes%20changes%20to%20one%20of%20the%20five%20worksheets%20and%20refreshes%20that%20this%20may%20create%20issues%20because%20I%20know%20share%20issues%20can%20occur%20if%20one%20user%20changes%20cell%20A1%20in%20Sheet%201%20and%20another%20user%20makes%20a%20different%20change%20in%20the%20same%20sheet%20and%20cell%20this%20will%20cause%20a%20save%20issue.%20This%20will%20not%20occur%20in%20the%20dataset%20that%20I%20have%20for%20the%205%20worksheets.%20However%2C%20the%20data%20that%20is%20linked%20to%20those%20worksheets%20graphs%2C%20charts%20and%20pivot%20tables%20will%20constantly%20change.%20There%20has%20to%20be%20a%20way%20around%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2409962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

My main question is will macros or Power Query cause Excel Workbook to not be shareable in Sharepoint? If so, is there a way around it?

I created a file that includes 5 sheets that Power Query cleans and appends as one master file. It refreshes when user selects Macro button.

I also have a Private Sub Worksheet_Calculate routine that runs when a sheet Calculates based on the target value it hides or unhides charts.

Could any of these effect shareability in Sharepoint? The file is actually saved in Teams but Teams stores it in Sharepoint. I have had some off and on issues with users running into save warnings and errors where it asks one user if they want overwrite or Save a copy and the other user it just won't let them save.

My thought process leads me to believe that if two users are in the file and each makes changes to one of the five worksheets and refreshes that this may create issues because I know share issues can occur if one user changes cell A1 in Sheet 1 and another user makes a different change in the same sheet and cell this will cause a save issue. This will not occur in the dataset that I have for the 5 worksheets. However, the data that is linked to those worksheets graphs, charts and pivot tables will constantly change. There has to be a way around this.

 

0 Replies