adding comments to ListColumn tables in Excel with Access VBA

[TysonC]

My first post. I can usually find what I need and don't need to post but I've come up empty on this one.
I am exporting several tables from Access to Excel 2013 with VBA and then formatting the sheets as tables, adding NumberFormats and so on.
My titles are abbreviated so I want to add verbose descriptions in a comment.
I've tried all the combinations of .Comment and AddComment I can think of.

xls.Application.Workbooks.Open (xlsxPath)
Set wkb = xls.Application.ActiveWorkbook
Set wks = wkb.Worksheets("tblKPI_Hourly")
myName = "HourlyKPI"
wks.ListObjects.Add(xlSrcRange, wks.Range("A1").CurrentRegion, , xlYes).Name = myName
Set tbl = wks.ListObjects(myName)
tbl.HeaderRowRange("tIn").Address.AddComment = "Total Inbound Contacts"

This returns Run-time error 5: Invalid procedure call or argument at the last line.


Thanks in advance.

[skywriter]

myname is the actual name of the table?
I'll assume tln is the text you have in one of the columns of the header row?
This should work.

Range(myname & "[[#Headers],[tIn]]").AddComment.Text "Total Inbound Contacts"

[Aflatoon]

Try this:

xls.Application.Workbooks.Open (xlsxPath)
Set wkb = xls.Application.ActiveWorkbook
Set wks = wkb.Worksheets("tblKPI_Hourly")
myName = "HourlyKPI"
Set tbl = wks.ListObjects.Add(xlSrcRange, wks.Range("A1").CurrentRegion, , xlYes)
tbl.Name = myName
tbl.ListColumns("tIn").Range(1).AddComment = "Total Inbound Contacts"

[snb]

Sub M_snb()
  With ListObjects.Add(1, Cells(1).CurrentRegion, -1)
      .HeaderRowRange.Cells(1, .ListColumns.Count).AddComment "Total Inbound Contacts"
   End With
End Sub

[TysonC]

skywriter :
Nope. Runtime 1004: application-defined or object-defined error.
Same with

Range(tbl & "[[#Headers],[tIn]]").AddComment.Text "Total Inbound Contacts"

and

Range("[" & tbl & "[[#Headers],[tIn]]]").AddComment "Total Inbound Contacts"

[TysonC]

Aflatoon:
Nope. Run-time error 438: Object doesn't support this property or method

tbl.ListColumns("tIn").Range(1).AddComment ("Total Inbound Contacts")

gets Run-time error 1004: application-defined or object-defined error

[skywriter]

Well I don't know what to tell you.
I created a table named it "HourlyKPI" and one of the headers was labeled with the text tIn and it worked just fine.

[TysonC]

snb:
I tried

tbl.HeaderRowRange.Cells(1, .ListColumns.Count).AddComment "Total Inbound Contacts"

and got a compile error: invalid or unqualified reference for .LastColumns
However,

tbl.HeaderRowRange.Cells(1, tbl.ListColumns.Count).AddComment "Total Inbound Contacts"

did put the comment on the last column.

[TysonC]

Intersect(tbl.ListColumns("tIn").Range, tbl.HeaderRowRange).AddComment "Total Inbound Contacts"

was suggested from Norie at mrexcel.com and that worked. I'd rather not use intersect if there is something cleaner.

[skywriter]

Intersect(tbl.ListColumns("tIn").Range, tbl.HeaderRowRange).AddComment "Total Inbound Contacts"

was suggested from Norie at mrexcel.com and that worked. I'd rather not use intersect if there is something cleaner.

So you're cross posting and not letting us know?

[TysonC]

skywriter:
Yes. Sorry all, I did not know that was an issue. I read up on it and won't do it again.

mrexcel.com/forum/excel-questions/926397-adding-comments-listcolumn-tables-excel-access-visual-basic-applications.html

[SamT]

See if this works. I have made a number of assumptions. It may need to work on different tables. IT should be easy to edit. Even the same table may have different columns at times. It is best to have all header-comment pairs in one place.

I used a Select Case to organize the comments, but a Dictionary, or even two Arrays would work.

Option Explicit

Sub VBAX_SamT_AddingComments()
'For help see: http://www.vbaexpress.com/forum/showthread.php?55368

Dim i As Long
Dim Found As Range
Dim HeaderText As String
Dim CommentText As String

For i = 1 To N 'N = total number of possible Header Comments
Select Case i
  Case 1
     HeaderText = "tIn"
     CommentText = "Total Inbound Contacts"
  Case 2
End Select

' On Error Resume Next 'Uncomment after testing
Set Found = tbl.HeaderRowRange.Find(HeaderText)
If Not Found Is Nothing Then Found.AddComment CommentText
Err = 0

Next
End Sub

[Aflatoon]

There was a typo in mine - it should be:

tbl.ListColumns("tIn").Range(1).AddComment "Total Inbound Contacts"

without the = sign.

[TysonC]

My final code:

   For c = 1 To lastCol        HRR = tbl.HeaderRowRange(c).Value
        
        'Add Comments
        Desc = DLookup("Description", "settings_kpi", "[Field]='" & HRR & "'")
        With tbl.ListColumns(HRR).Range.Cells(1)
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            .AddComment Desc
        End With
more

Thank you so very much!
-Tyson