Anything Access

You may need to save excel comments from one excel model and load them into another file. This can be a time consuming task and this routine will eliminate if ( big IF) you have a key column to store the comments on. In this case it was the crystal ID.

You need an access database to save the excel comments into. The VBA can be executed either from your personal workbook or the excel file directly.

---

Function to Save the Excel Comments

Sub SaveComments()
Dim rst As Recordset, i As Integer, tmpComment, tmpCrystal
Dim db As Database
Set db = OpenDatabase("Your Access Database")
 
If MsgBox("Save these comments to the database ?", vbYesNo) = vbNo Then
    Exit Function
End If
 
Range("D1").Select
 
For i = 2 To 5000
    ActiveCell.Offset(1, 0).Select
    tmpCrystal = ActiveCell.Value
    tmpComment = ActiveCell.Offset(0, 62).Value
    If Len(tmpComment & "") > 0 Then
        If Len(tmpCrystal & "") > 0 Then
            Set rst = db.OpenRecordset("select * from tblComments where CrystalID='" & tmpCrystal & "'")
            If rst.RecordCount > 0 Then
                rst.MoveFirst
                rst.Edit
            Else
                rst.AddNew
            End If
            rst!CrystalID = tmpCrystal
            rst!Comments = tmpComment
            rst.Update
        End If
    End If
Next i
 
Set rst = Nothing
MsgBox "Comments Updated"
 
End Sub

Function to load the Excel comments

Sub LoadComments()
Dim rst As Recordset, i As Integer, tmpComment, tmpCrystal
Dim db As Database
Set db = OpenDatabase("Your Access Database")
 
If MsgBox("LOAD Comments into this File from the Database ", vbYesNo) = vbNo Then
    Exit Function
End If
 
tmpComment = ""
Range("BN2").Select
For i = 2 To 5000
    tmpComment = tmpComment & ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
Next
 
If Len(tmpComment & "") > 1 Then
    If MsgBox("Comments already exist these may be lost - CONTINUE ?", vbYesNo) = vbNo Then
        Exit Function
    End If
End If
 
Range("D1").Select
 
For i = 2 To 5000
    ActiveCell.Offset(1, 0).Select
    tmpCrystal = ActiveCell.Value
    '
    If Len(tmpCrystal & "") > 2 Then
        Set rst = db.OpenRecordset("select * from tblComments where CrystalID='" & tmpCrystal & "'")
        If rst.RecordCount > 0 Then
            rst.MoveFirst
            ActiveCell.Offset(0, 62).Value = rst!Comments
        End If
    End If
Next i
 
Set rst = Nothing
MsgBox "Comments Updated"
 
End sub

The structure of the table “tblComments” should include your Key identifier , in my case CrystalID and a field to save the comments. I set that field to 255 characters, but you can have a longer memo type field if required.

Other Excel Posts

How to Add Excel Comments in VBA

You can choose to show or hide excel comments by clicking the review tab on the ribbon menu and selecting the options shown below