PDA

View Full Version : [SOLVED] adding comments to ListColumn tables in Excel with Access VBA



TysonC
03-07-2016, 10:39 AM
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
03-07-2016, 05:59 PM
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
03-08-2016, 01:39 AM
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
03-08-2016, 02:00 AM
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
03-08-2016, 12:40 PM
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
03-08-2016, 12:45 PM
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
03-08-2016, 12:56 PM
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
03-08-2016, 01:00 PM
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
03-08-2016, 01:03 PM
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
03-08-2016, 01:28 PM
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
03-08-2016, 04:06 PM
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
03-08-2016, 05:11 PM
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
03-09-2016, 01:07 AM
There was a typo in mine - it should be:


tbl.ListColumns("tIn").Range(1).AddComment "Total Inbound Contacts"
without the = sign.

TysonC
03-15-2016, 06:32 AM
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