Broken Excel Script - Please help me troubleshoot

Question

I created a simple recorded-action macro in excel designed to pull key data from specific cells after the contents of a .txt file data are pasted into a specific location (with text to columns active for space, tab, and forward-slash, treating consecutive delimiters as one) and the macro is run. After pasting the data into the spreadsheet, all it does is copy and paste cells one at a time to reorganize the data. It was working yesterday, but not today.

Here's the code, and the error:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Paste to range B3 on selectedSheet from range C16 on selectedSheet
	selectedSheet.getRange("B3").copyFrom(selectedSheet.getRange("C16"), ExcelScript.RangeCopyType.all, false, false);

Line 4: Range copyfrom: You cannot perform the requested operation.

There is no error code associated and the help options are unrelated to the issue

Troubleshooting I've tried that have failed:

-Closing and reopening Excel

-Pasting the data as values

-Deleting potentially problematic steps in the macro function

-Re-recording the steps of the macro

-Reverting to an older version of the file and using data files that worked

[I feel like I've tried everything besides creating a range of calculated cells to clone the data and then copying and pasting the values from that] EDIT: this did not work either.

I suspect the recent Microsoft update (5/28) is the cause, either changing the way text-to-columns works and causing consecutive delimiters to be formatted as merged cells OR they broke the way macros handle copy-and-paste functions. My company IT department pushes all updates automatically and requires them, so I do not have the option to revert to an older version if that is the only fix.

Any help would be greatly appreciated. Please bear in mind that I am not working with VBA, just recorded actions. My skills with coding are still limited.

Answer 1

delete that, use this

	let rng1 = selectedSheet.getRange("B3").getValue();
		selectedSheet.getRange("C16").setValue(rng1);

Answer 2

Hi,

It's possible that recent updates to Excel could have introduced changes that affect the recorded macro's functionality.

Ensure that your Excel application and the recorded macro are compatible. Verify that the recorded macro was created in the same version of Excel that you're currently using.

Test with Manual Steps:

• Manually perform the steps recorded in the macro to see if they work as expected.
• Check if you encounter any errors or unexpected behavior while manually copying and pasting the data.
• If manual steps work fine, it could indicate an issue with the recorded macro code.

Best Regards.

Answer 3

I still cannot get the macro to work, as of 6/1/2023, however I did find a simplistic workaround. If you can't copy and paste data with a macro, the only solution that works for me seems to be cell-reference formulas that clone the data. While it does cut down very slightly on software load, the cons to using cell references are apparent:

1. The macro handled pasting data as values so that the manual portion of relocating the data did not require it, and thus did not need to be reformatted in their new location. Using cell references, the data needs to be pasted as values when relocated into the data table, which has caused dates and times to paste as numbers because the destination formatting is overwritten. There is no option to change this, so it then needs to be reformatted manually and is adding time to the process. I anticipate similar problems when pasting things like #E# hex code or lot numbers, where it will automatically format them as exponential numbers.
2. The macro was designed to overwrite and clear data after processing, which cell references cannot do. Any inconsistencies that cause data within the normal range to persist may lead to double entry.
3. Cell reference formulas are prone to being changed accidentally. The macro did not have this issue because it was locked behind the script. Running the macro twice in a row, for example, would clear all of the data on the tab, which was a useful feature. The data persists with cell references. Deleting the data not only takes more time, but can create reference errors in your formulas if done incorrectly (such as deleting cells or rows). I had to put a safeguard in place to prevent this from happening by creating a backup copy of the data converter tab so that it can be copied to replace the main one if the formulas were compromised. I also had to lock this backup tab with a password to prevent unintentional use.

Answer 4

I am having the same issue. I tried recording the macro again today and I am still getting an error on line 4: Range copyfrom: You cannot perform the requested operation.

All other macros with the same script are running fine

Answer 5

Hi everyone, thanks for reporting this issue! The Office Scripts team is investigating and we have confirmed that scripts are failing more often than usual on Excel for Windows, specifically builds 16.0.16327.20308 and 16.0.16501.20196. We are currently trying to reproduce the issue and determine the underlying cause.

In the meantime, you can try the following workarounds:

• Running the script in the browser
• Reverting to an earlier version of Excel

Additionally, if you continue to encounter this issue, let us know - understanding what specific APIs/scenarios are failing can help us identify the root cause. We apologize for the inconvenience; I'll share further updates once we know more!

Update (6/12): this should be fixed in CC and will be fixed soon for MEC and SAC as well. Thank you for your patience!