This action cannot be recorded and cannot copy as code.

[lolorraine]

Article URL

https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel

Issue

I got below message and cannot copy as code when I try to record actions for deleting all objects in an EXCEL workbook.

This action is not yet recordable This action cannot be recorded

[isabela-dominguez]

Hi @lolorraine thank you for reaching out.

Would you please be able to provide the following: The version and build number of the client you are using. Steps to reproduce the issue. Console output and error messages.

[lolorraine]

Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2305 Build 16.0.16501.20074) 64-bit

Step:

1. Go to Automate tab > Scripting tool group> Record Actions, start recording.
2. To delete all checkboxes at a time, go to the Home tab > Editing group > Find & Select > Go To Special, select the Objects radio button, and click OK. This will select all the check boxes on the active sheet.
3. And you simply press the Delete key to remove them.

Console output and error messages: Did nothing but show below two message: This action is not yet recordable --> after step 2 This action cannot be recorded --> after step 3

[ElizabethSamuel-MSFT]

@lolorraine Thanks for providing more details.

@alison-mk Can you take a look? Maybe this should be transferred to the product repo?

Thanks.

[AlexJerabek]

Hi @lolorraine,

It is a known issue that not all Excel actions are supported by the Action Recorder (as stated here, under "Potential Errors"). I can pass this case on to the product team to let them know folks are trying to record the delete-all scenario. To help us prioritize, could you please tell me if this is part of your regular workflow, or were you just testing out the recorder feature?

Thank you.

[microsoft-github-policy-service[bot]]

Thanks for your interest in Office Scripts development! Feedback here is intended for reporting problems with the Office Scripts documentation. Can you please post this feature request to the Excel Tech Community (https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral)? Feature Requests submitted to Tech Community are regularly reviewed by the product teams as they plan future releases. Thanks!

[microsoft-github-policy-service[bot]]

Thanks for your interest in Office Scripts development! Feedback here is intended for reporting problems with the Office Scripts documentation. Can you please post this feature request to the Excel Tech Community (https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral)? Feature Requests submitted to Tech Community are regularly reviewed by the product teams as they plan future releases. Thanks!

[lolorraine]

Hi @AlexJerabek

Actually I plan to convert my VBA macros to office script for power automate flow use. I couldn't find a way to convert VBA-code to Office Scripts-code directly, so I will need to write code by myself. The Record Actions will be very helpful. Although I can still use Excel macros for this, it would be great if office script can do the same thing:)

[AlexJerabek]

Thanks @lolorraine. Let me tag @vaquierm and @jeremy-msft for visibility on this API that needs Recorder support.

In the meantime, can you share a version of your workbook with me? I can work with you to get a sample on how to do this, provided deleting checkboxes is supported by the APIs).

[lolorraine]

Thank you, @AlexJerabek For confidential reason I just delete the content and save as .xlsx sample report.xlsx

My script will:

1. keep the target sheet(wsKeep) only,
2. remove unnecessary items(objects)
3. and do some vlookup for regular meeting use.

Below macros is what I currently use for the step 1 and 2: ' Copy worksheet. Sheets("wsKeep").Select Sheets("wsKeep").Copy

' Delete Button and Check Box ActiveSheet.DrawingObjects.Select Selection.Delete

Since I cannot find the way copying target sheet to a new file for step 1, I use below workaround: /**Keep target worksheet only

• https://powerusers.microsoft.com/t5/General-Power-Automate/Solved-Copy-one-excel-sheet-to-a-new-file/td-p/1828962 */

For step2, Excel macro recorder can easily get the code I want:) And It is where I get stuck.

[AlexJerabek]

Hi @lolorraine,

I think I have a solution for you, though it might be too heavy-handed for your workbook. You can iterate over the Shapes in the workbook and delete them. I think that's the same behavior as deleting everything in the DrawingObjects collection.

function main(workbook: ExcelScript.Workbook) {
    const shapes = workbook.getActiveWorksheet().getShapes();
    shapes.forEach(shape =>{
        shape.delete();
    });
}

Please let me know if that works for you.

[lolorraine]

Thanks for the prompt help @AlexJerabek The solution work perfectly for my workbook and no other impact so far:)

Looking forward to the future releases<3

[AlexJerabek]

Excellent! I'm adding this sample to our API documentation with this PR: https://github.com/OfficeDev/office-scripts-docs-reference/pull/272.

I'll close the issue, but please reach back out if you have any questions.