2

My team had to move to Office365 and we cannot use anymore our former MS Excel with his VBA or our former G Sheets with his Scripts.

I already redo the workbook. Basically, it has many exactly same sheets. The only difference between the sheets is what column is shown or hidden. The VBA or the scripts we had just group all the sheets together, so when a there was an edit somewhere, it happen on every sheet as well.

For example, here was my vba for the sheet1 :

Private Sub Worksheet_Activate()
    Sheets(Array("January", "February", "March", "April", "May")).Select
End Sub

I understand that VBA cannot work with excel online. I understand as well that we cannot group sheets on excel online. I understand that I can teach my team to always open this excel file with the desktop app to benefit VBA, but it will be difficult, because they are not that good with excel and actually doesn't know what vba is.

What I ask is : Is there another way to make it happen with excel online ? I heard it has his own kind of script. I heard there is power automate.

Manuel Pelletier
  • 33
  • 3

1 Answers1

0

Using Office Scripts, you can take the approach where you load the worksheets into an array and then simply run the same section of code over each item in a for loop ...

function main(workbook: ExcelScript.Workbook)
{
  let myWorksheets: ExcelScript.Worksheet[] = [];

  myWorksheets.push(workbook.getWorksheet("January"));
  myWorksheets.push(workbook.getWorksheet("February"));
  myWorksheets.push(workbook.getWorksheet("March"));

  myWorksheets.forEach(worksheet => {
    worksheet.getRange("A1:C5").setFormula("=ROW() & \".\" & COLUMN()");
  });
}

In my example, I've hardcoded the worksheets to a specific few but you could always do it for all worksheets if need be using the workbook.getWorksheets() method.

There doesn't appear to be a method which groups the sheets like that which exists in VBA.

If you're not familiar with Office Scripts, be sure to follow the documentation ... https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel ... to get a handle on it.

Further to that, these scripts can be executed from PowerAutomate/LogicApps using the Run script action ...

Run Script

... the documentation does explain that.

Skin
  • 9,085
  • 2
  • 13
  • 29
  • Thank you Skin. I already tried some things from your answer. But I need more time to finish testing and closing this question. – Manuel Pelletier Apr 27 '22 at 11:56
  • Thanks for the update. You’re not obliged to but it shows a level of gratitude you have for the helper which is nice. – Skin Apr 27 '22 at 12:37
  • Ok. I understand. I think that what I want isn't possible. In Office online (and Power automate), there is no way, at first, to trigger a script when an edit is made. I will need to trigger it manually, which is not convenient. I confirm that your script does work to group sheets, and but it cannot be trigger with the event of activating a sheet. With your script I can edit many sheets, but only if I write the edit I want within the script. Now I need to be more precise. When an edit is made in one sheet, I want that all my sheet receive the same edit. Group sheets was a good way before. – Manuel Pelletier Apr 29 '22 at 12:57
  • Yeah, you can, you can use the SharePoint trigger for when a file is updated, but, you can’t target the cell that changed and that may be a shortcoming. Be careful though because it will become recursive if you don’t check for who changed the workbook. You should use some kind of service user. – Skin Apr 29 '22 at 20:53
  • I got another idea. Difficult to explain how come it will be an adequat alternative. I will use a dropdown list and button to hide and show columns depending of which part of the sheet I want to show. I found a script that does this and the button will be the trigger. All my original sheets were all the same except for what was shown or hidden. – Manuel Pelletier May 01 '22 at 00:58
  • Whatever floats your boat mate. – Skin May 01 '22 at 03:29