Forum Discussion

Seabottom's avatar
Seabottom
Copper Contributor
Oct 29, 2023
Solved

Issue with comments/threaded comments/notes

I recently did something to my spreadsheet locally, and while it still opens fine, on the webversion it didn't.

So I copy/pasted everything over to a new spreadsheet and it opens fine now.

However, the comments, which were all added from the online platform are not in there any more, and so I decided to VBA my way out of it, but I think I made it worse. I removed all the "Threaded comment blablabla stuff" from the comments locally and.. long story short - 

I need to copy all of my comments from the original sheet to the new sheet - which is easy, just ctrl+alt+v and select comments.... except that only works locally. When I open up the spreadsheet online, I can only see the comments (as notes) which I've added after I VBA cleaned the other comments.

If I try to manually add the "Threaded comment blabla stuff" to the comments, the online won't open at all, even after I have removed that edited comment. And such I had to create a new spreadsheet yet again.

How can I fix this?

excel
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Oct 31, 2023

    Seabottom If you can somehow copy the Finances tab from the old to the new file, then this script will paste everything from the "old" Finances (2) tab to the new Finances tab:

     

     

    function main(workbook: ExcelScript.Workbook) {
  // Your code here
  let srcRng = workbook.getWorksheet("Finances (2)").getRange("A1:I168");
  let tgtRng = workbook.getWorksheet("Finances").getRange("A1:I168");
  tgtRng.copyFrom(srcRng,ExcelScript.RangeCopyType.all)
}

     

     

    But the only way I was able to open the "Old" file was by using desktop Excel. Then it offered to do some repairs. After that I copied the sheet to the other file you attached an I copied the comments using the script above.

    I can't check whether the comments are intact. See attached.

     

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 30, 2023
    Can you:
    - Open both files in desktop Excel
    - Copy the sheet from the original file to the "new" file
    - Open the new file in Excel on-line
    - Copy the comments from the "old" sheet to the "new" sheet
    • Seabottom's avatar
      Seabottom
      Copper Contributor
      Oct 30, 2023

      JKPieterse excel online does not support copying of comments, that was the first thing I tried. I can make the new file and open online no problem, but Ctrl+alt+v does nothing and half the ribbon bar is missing as well, as online is only "the basics".

      I'll link the two sheets, there is no confidential information so go ahead. I think the only way to fix it is with a script if some kind

      Old sheets with comments

      New sheet, no comments 

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Oct 31, 2023

        Seabottom If you can somehow copy the Finances tab from the old to the new file, then this script will paste everything from the "old" Finances (2) tab to the new Finances tab:

         

         

        function main(workbook: ExcelScript.Workbook) {
  // Your code here
  let srcRng = workbook.getWorksheet("Finances (2)").getRange("A1:I168");
  let tgtRng = workbook.getWorksheet("Finances").getRange("A1:I168");
  tgtRng.copyFrom(srcRng,ExcelScript.RangeCopyType.all)
}

         

         

        But the only way I was able to open the "Old" file was by using desktop Excel. Then it offered to do some repairs. After that I copied the sheet to the other file you attached an I copied the comments using the script above.

        I can't check whether the comments are intact. See attached.

         

Resources