Load Excel comments to Ms Access table as a seperate field

[KrishnaSiva]

Hi Team,



We have a excel file with 6 columns, out of this 4 columns has the comments .

Now i am trying to import the Excel data to access, when i import i am able to import all the 6 columns but i have a requirement to capture the comments of 4 excel columns and load in to access table as different fields. So finally in excel there should be 10 fields( 6 Excel Columns+ 4 Comments).

It's a bit urgent issue, please help to respond ASAP.

Regards,
Siva

[ranman256]

Can't you just import all columns using :
Docmd.transferspreadsheet?

[Gicu]

You will have to use Excel automation to do that or you can edit your Excel file to convert the comments to cell content using one of the methods below:
https://www.extendoffice.com/documen...-to-cells.html
https://chandoo.org/wp/2009/09/03/get-cell-comments/
https://www.howtoexcel.org/vba/how-t...book-with-vba/

Cheers,
Vlad

[NoellaG]

Excel function with error handling:

Public Function getComment(rCell As Range) As String
On Error GoTo Err_getComment
    Dim strComment As String
        
    strComment = rCell.Comment.Text
    getComment = strComment
    
Exit_getComment:
    Exit Function
    
Err_getComment:
    If Err.Number = 91 Then
        getComment = ""
    Else
        getComment = Err.Number & ": " & Err.Description
        Resume Exit_getComment
    End If
        
End Function