Excel comments???

[DirtDawg]

I have a spreadsheet that is updated via an SQL server. The users update information on a daily basis. Some of the data is only updated on a weekly basis. All of the code at this point is working the way that it should.
On the weekly update all of the information moves up one row or sometimes more. Question is
I want to be able to add a comment to a cell. and have that comment move with the weekly updates? Any ideas for me to start on?

 

[xlbo]

Do-able but tricky
You would need to set up a Querytable class to access the brefore_refresh and after_refresh events. You would then have to loop through all the records in the before_refresh, to find any comments and find the "key" so that you can replace them again. Then in teh after_refresh eventy, you would have to replace all the comments

OR - if you're lucky (and I havn't tried this)
It may be that the comments will stay referenced to the right cell as the querytable update mode can be changed - have a look at the table properties for options on what to do when the number of records changes

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[PHV]

Anyway, to add a comment:
YourRange.AddComment "Your comment here"

Hope This Help
PH.

 

[DirtDawg]

Here is a the two pieces of code that I have at this time
Admin Side is updated weekly and will add the comments to the file. The staff part the update on a daily basis. I believe that my code is good enough that I can add a loop into the formula and not have any problems. As you can see below I am already using many loops. If you can Give me a little more help I would appreciate it the Cell with the comments would be (h8,h10,h12,h14,h16,h18,h20) in the staff I have it updating the color based on a checkbox response.

With ActiveWorkbook.Sheets(1)
For Each cell In .Range("A8&quot 'A21,A26:A39,A43:A56,A60:A73,A77:A90,A95:A108,A113:A126,A132,A145,A150:A163,A186:A199,A203:A216,A220:
If cell.Value > strColAMax Then
strColAMax = cell.Value
End If
Next cell
strColBFirst = .Range("B8&quot.Value
strColBLast = .Range("B20&quot.Value
End With
Set cell = Nothing
'If lowest to highest use And (eg 10, 11, 12 ,etc),
'otherwise use Or (eg 51, 52, 01, 02, etc)
strColBLast = Format(strColBLast, "00&quot
strColBFirst = Format(strColBFirst, "00&quot
If Val(strColBFirst) >= 47 Then '1st week to rollover
strAndOr = " Or "
strOrderBy = _
"IIF(wkn >= " & Val(strColBFirst) & "," & _
" wkn - 53, wkn)"
Else
strAndOr = " And "
strOrderBy = " wkn "
End If
If Descending Then
strDesc = " DESC"
End If

Set cnn = CreateObject("ADODB.Connection&quot
Set rst = CreateObject("ADODB.Recordset&quot
cnn.Open "driver={SQL SERVER};SERVER=SERVER;DSN=DSN;UID=UID;PWD=PWD;DATABASE=DB"
src = "SELECT srv, vslvoy, wkn, etd, jpn, twn FROM vsl " & _
"WHERE srv = '" & strColAMax & "' " & _
"AND (wkn >= '" & strColBFirst & "' " & strAndOr & _
&quot; wkn <= '&quot; & strColBLast & &quot;') ORDER BY '&quot; & strOrderBy & &quot;'&quot;

rst.Open Source:=src, ActiveConnection:=cnn
rst.movenext

rst.MoveFirst
Set myrng = Range(&quot;c8,c10,c12,c14,c16,c18,c20&quot ',c220,c222,c224,c226c228,c230,c232&quot
For Each cell In myrng
cell.Value = rst.Fields(&quot;vslvoy&quot.Value
rst.movenext
Next cell

rst.MoveFirst
Set myrng = Range(&quot;f9,f11,f13,f15,f17,f19,f21&quot ',c220,c222,c224,c226c228,c230,c232&quot
For Each cell In myrng
cell.Value = rst.Fields(&quot;etd&quot.Value
rst.movenext
Next cell

rst.MoveFirst
Set myrng = Range(&quot;g8,g10,g12,g14,g16,g18,g20&quot ',c220,c222,c224,c226c228,c230,c232&quot
For Each cell In myrng
cell.Value = rst.Fields(&quot;jpn&quot.Value
rst.movenext
Next cell

rst.MoveFirst
Set myrng = Range(&quot;j8,j10,j12,j14,j16,j18,j20&quot ',c220,c222,c224,c226c228,c230,c232&quot
For Each cell In myrng
cell.Value = rst.Fields(&quot;twn&quot.Value
rst.movenext
Next cell

rst.Close
cnn.Close


For the STAFF side

With ActiveWorkbook.Sheets(1)
For Each cell In .Range(&quot;A8&quot
If cell.Value > strColAMax Then
strColAMax = cell.Value
End If
Next cell
strColBFirst = .Range(&quot;B8&quot.Value
strColBLast = .Range(&quot;B20&quot.Value
End With
Set cell = Nothing
'If lowest to highest use And (eg 10, 11, 12 ,etc),
'otherwise use Or (eg 51, 52, 01, 02, etc)
strColBLast = Format(strColBLast, &quot;00&quot
strColBFirst = Format(strColBFirst, &quot;00&quot
If Val(strColBFirst) >= 47 Then '1st week to rollover
strAndOr = &quot; Or &quot;
strOrderBy = _
&quot;IIF(wkn >= &quot; & Val(strColBFirst) & &quot;,&quot; & _
&quot; wkn - 53, wkn)&quot;
Else
strAndOr = &quot; And &quot;
strOrderBy = &quot; wkn &quot;
End If
If Descending Then
strDesc = &quot; DESC&quot;
End If

Set cnn = CreateObject(&quot;ADODB.Connection&quot
Set rst = CreateObject(&quot;ADODB.Recordset&quot
cnn.Open &quot;driver={SQL SERVER};SERVER=SErver;DSN=DSN;UID=UID;PWD=PWD;DATABASE=DB&quot;
src = &quot;SELECT wkn, srv, vsl, allo, flr FROM jpn &quot; & _
&quot;WHERE srv = '&quot; & strColAMax & &quot;' &quot; & _
&quot;AND (wkn >= '&quot; & strColBFirst & &quot;' &quot; & strAndOr & _
&quot; wkn <= '&quot; & strColBLast & &quot;') ORDER BY '&quot; & strOrderBy & &quot;'&quot;

rst.Open Source:=src, ActiveConnection:=cnn
rst.movenext

rst.MoveFirst
Set myrng = Range(&quot;f8,f10,f12,f14,f16,f18,f20&quot
For Each cell In myrng
cell.Value = rst.Fields(&quot;allo&quot.Value
rst.movenext
Next cell

rst.MoveFirst
Set myrng = Range(&quot;h8,h10,h12,h14,h16,h18,h20&quot
For Each cell In myrng
If rst.Fields(&quot;flr&quot.Value = &quot;&quot; Then
cell.Interior.ColorIndex = 0
ElseIf rst.Fields(&quot;flr&quot.Value = &quot;YES&quot; Then
cell.Interior.ColorIndex = 37
End If
rst.movenext
Next cell

rst.Close

 

[xlbo]

Aaaah - you're using ADO not MSQuery - in which case my before and after refresh comments aren't applicable. Don't really know much ADO and how it interacts with worksheets so I'm afraid I'm gonna bow out now

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DirtDawg]

Actually this comment will be only on the excel sheet nothing to do with any type of connection

 

[xlbo]

I know but the data is getting put there by ADO - I can't actually see where the data is entered onto the worksheet to be able to know where to do the loop to pick up comments - you can use this to find where the comments are BEFORE you input new data:

For Each cmt In ActiveSheet.Comments
MsgBox cmt.Parent.Address
Next


You would need, at the very least to pick up the &quot;Text&quot; property of the comment and the address property of the parent of the comment (a range) but I don't know how you would figure out what the offset would need to be to move the comments...

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DirtDawg]

All that i am actually looking for is a way to take the comments from 6 rows and move them up two rows. The comments will not be put onto the server the will stay strictly on the excel fill only. I will just append the code at the end of one of my ADo Connection? I have found several sodes that can copt comments to cells but not comment to comment?

 

[xlbo]

Unfortunately you can't copy comments, as I said before, you would need to pick up the text from the comment and add a new one, then populate it with the text from the old one

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DirtDawg]

I figured it out that if I run the macro Recording in Excel with paste special only comments that it will do what i wanted and copy the comments.

Thanks for your help though

 

[xlbo]

D'oh - completely forgot about that option - yes - I imagine that'd work nicely ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244