Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Georgiana69 pe Simpatie
 | Femeie
24 ani
Arad
cauta Barbat
24 - 47 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
If in an Excel different from 365 (eg. Excel 2019, Excel 2021 etc.), you open a file with comments made in Office 365 where you added Threaded Comments (in 365 called simply Comments, to which multiple users can respond with their logged in account), you might receive one of two errors (see below). Not to confuse with Notes (in 365) which before Excel 365 were the default comments.
See the difference between Notes and Comments (also called Threaded Comments) in Office 365:
For the sake of this topic, I will name Excel 365 comments as "Threaded Comments" and Excel 365 notes as "Notes".
The 2 things that might occur when you open an Excel file that contains threaded comments:
1. no error, but all Threaded Comments replaced with Notes that each contain a long text informing you they were Threaded Comments
2. dialog box with Yes/No to repair your file, and after clicking Yes a box with a summary of "Threaded Comments [...] xml"
This is how the 2 issues above appeared on 2 different computers:
1. the PC where the Excel file originated, previously had Office 365 and made lots of Threaded Comments, the new PC (for the same user) now has Office 2021
2. my PC where I received the file via e-mail from the computer mentioned at 1 where all Threaded Comments had been replaced with Notes. I still have Office 365 on my PC
After repairing the file in case no 2, I recovered the Threaded Comments as they were made, except instead of the name of the author of the Threaded Comment, it was simply "author"
The end user with the problem (the one with Office 2021 who previously had Office 365) now has hundreds of Notes that before the text of the note (the actual message that was in the Threaded Comment) contain the following message each (:
[Threaded comment]
Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 |
To fix this, they will have to run the following macro each of the files where they encounter the above text in their notes. The code below must be run once on each Excel file with the problem, and it will remove the "[Threaded comment] ..." text of all the notes in the Excel file.
Sub Comments_Remove_Text_Threaded()
Application.ScreenUpdating = False
For Each Sheet In ActiveWorkbook.Sheets
For Each cell In Sheet.UsedRange.Cells
If Not cell.Comment Is Nothing Then
removeText = "[Threaded comment]" & Chr(10) & Chr(10) & _
"Your version of Excel allows you to read this threaded comment; however, " & _
"any edits to it will get removed if the file is opened in a newer version of Excel. " & _
"Learn more: https://go.microsoft.com/fwlink/?linkid=870924" & Chr(10) & Chr(10)
storeTemp = cell.Comment.Text
store = Trim(Replace(storeTemp, removeText, "", 1, 1))
cell.ClearComments
cell.AddComment store
cell.Comment.Visible = False
End If
Next cell
Next Sheet
Application.ScreenUpdating = True
End Sub |
On my PC, in order to send a file to the end user's PC I will have to convert all Threaded Comments to notes, while keeping the text of the Threaded Comment. The code below must be run once on each Excel file that has Threaded Comments, and it will convert all Threaded Comments to Notes which can be seen properly by PCs that don't have Office 365.
Sub Comments_Threaded_to_Notes()
Application.ScreenUpdating = False
For Each Sheet In ActiveWorkbook.Sheets
For Each cell In Sheet.UsedRange.Cells 'Selection ' ActiveSheet.Cells
If Not cell.CommentThreaded Is Nothing Then
store = cell.CommentThreaded.Text
cell.ClearComments
cell.AddComment store
cell.Comment.Visible = False
End If
Next cell
Next Sheet
Application.ScreenUpdating = True
End Sub |
|
|
| pus acum 8 luni |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
Even though when converting Threaded Comments to Notes there is no formatting (because Threaded Comments have none), converting comments / replacing text in comments with formatting while retaining the formatting may be done with the code from the following link:
Code is UNTESTED by me:
Public Sub AddDifferentComment(ByRef toRng As Range, ByRef newRng As Range)
Dim toTxt As TextFrame, newTxt As TextFrame, newStart As Long, divLine As String
divLine = Chr(10) & "---------------------------" & Chr(10)
Set newTxt = newRng.Comment.Shape.TextFrame
Set toTxt = toRng.Comment.Shape.TextFrame
newStart = toTxt.Characters.Count + Len(divLine) + 1
toRng.Comment.Text divLine & newTxt.Characters.Text, newStart
With toTxt.Characters(newStart, newTxt.Characters.Count + 1).Font
.Size = newTxt.Characters.Font.Size
.Bold = newTxt.Characters.Font.Bold
End With
End Sub |
|
|
| pus acum 8 luni |
|