3

I have a document saved on SharePoint that documents testing.

Once testing has been completed I would like to move the tests (entire row) to a separate sheet to archive the test schedule, in the next empty row going down, and because its now archived delete it from the submission sheet.

Both sheets are within the same workbook. The number of rows differ each time the script is run therefore the script code would need to work based on a selection input by the user (either prior to running the script or prompted by the script).

I managed this in VBA. I can't locate a viable alternative to the selection function. Is there any way of translating the below VBA code to Office script so the same thing happens in Excel online?

Sub MoveCompletedTests()

     Selection.Copy Sheets("Archive - ATL staff only").Range("A1048576").End(xlUp).Offset(1, 0)

     Selection.Delete

End Sub

I have a button on the "Sample submission" sheet that runs the above code on the selected range on that sheet moving it to the "Archive - ATL staff only" sheet.

I attempted to use the script recorder but the script didn't allow for the selection to be dynamic enough, it coded for the cell range rather than just selection.

Community
  • 1
  • 1
Ben
  • 33
  • 4

2 Answers2

3

I think that something like getSelectedRange should work (at least, if you are not working with several selected ranges at once):

    function main(workbook: ExcelScript.Workbook)
    {
      let destination: ExcelScript.Range;
      let source = workbook.getSelectedRange().getEntireRow();
      let archive = workbook.getWorksheet("Archive - ATL staff only");
      if(archive.getUsedRange() == undefined) {
        // if the archive sheet is empty, use the first row as a destination
        destination = archive.getRange("1:1");
      }
      else {
        // ... otherwise, use the next row after the last used
        destination = archive.getUsedRange().getRowsBelow().getEntireRow();
      }
      destination.copyFrom(source, ExcelScript.RangeCopyType.values);
      source.delete(ExcelScript.DeleteShiftDirection.up);
    }

p.s. To be clear, in case of VBA a Selection is a property of Application. But in case of ExcelScript it's a method of a Workbook object. This can be a bit confusing because there is an ExcelScript.Application interface, which has no mention of Selection.

Vitalizzare
  • 4,496
  • 7
  • 13
  • 32
  • Hi Vitalizzare, Thanks for the code and the information regarding the differences in VBA and ExcelScript. Would you be able to let me know how to include the transfer of the formatting? I have some dates in the source sheet that are switching to datevalues upon running this script. I understand i could just change the format of the cells (or columns) in the destination sheet, but was wondering if there was a way to incorporate this into the script. Thanks again, Ben – Ben Apr 27 '22 at 15:07
  • 1
    @Ben Try to remove a parameter `ExcelScript.RangeCopyType.values`. I guess, it'll use `ExcelScript.RangecopyType.all` by default. Chances are, that `ExcelScript.RangeCopyType.values & ExcelScript.RangeCopyType.formats` will do the job too, if you have formulas to avoid. See [details here](https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.rangecopytype?view=office-scripts) – Vitalizzare Apr 27 '22 at 16:30
1

You can try the code here:

    function main(workbook: ExcelScript.Workbook) {
        //Assign the source variable to the selected range
        let source: ExcelScript.Range = workbook.getSelectedRange()
    
        //Assign the archive variable to the Archive worksheet
        let archive: ExcelScript.Worksheet = workbook.getWorksheet("Archive - ATL staff only");
    
        //Set the destination range in the archive sheet
        let destination: ExcelScript.Range = archive.getRange("A:A").getExtendedRange(ExcelScript.KeyboardDirection.up).getLastCell().getOffsetRange(1,0)
    
        //Determine if the offset range is set to A2 in the Archive sheet.
        //If so, determine if A1 has a value. If it does not, update the
        //destination range to cell A1.
    
        if (destination.getAddress() === "'Archive - ATL staff only'!A2" ){
          if (destination.getOffsetRange(-1,0).getValue() === "") {
            destination = destination.getOffsetRange(-1,0)
          }
        }
    
        //Copy the selected source range to the destination range
        destination.copyFrom(source, ExcelScript.RangeCopyType.all);
    
        //Delete the data in the source range
        source.delete(ExcelScript.DeleteShiftDirection.up);
      }

This code determines the last cell based on the data in column A in the archive sheet. This can be useful if the used range of the archive sheet has data somewhere, but column A does not. In this scenario, this code would still update correctly.

Also, this code doesn't copy and delete the entire row. So if you intend to use a selection smaller than the entire row, this code may work better.

Brian Gonzalez
  • 1,178
  • 1
  • 3
  • 15