I have a user who has been using the comments field to store his client
addresses. He now wants the spreadsheet exported from excel into outlook. Is
there any way to export the comments? Or move them into cells on the
spreadsheet.

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell.Cells(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


BWAnnie wrote:

I have a user who has been using the comments field to store his client
addresses. He now wants the spreadsheet exported from excel into outlook. Is
there any way to export the comments? Or move them into cells on the
spreadsheet.

--

Dave Peterson

Nice one Dave. But I think Annie wants a list that she can copy.

Annie

If a user has entered addresses in comments it is hard to imagine how she
may have entered it - and this would be depend on the solution. The first
macro will show a message with the text in each comment in turn.

Sub ShowCmtText()
Dim cmt
Dim sMsg As String

For Each cmt In ActiveSheet.Comments
sMsg = cmt.Text
MsgBox sMsg
Next cmt
End Sub

If the Names and addresses are shown in separate lines then the following
sub will copy the details to sheet2 (Change this to suit). If the address
details are separated by a comma you will have to change the code as detailed
in the sub below

Sub CommentTextToSheet2()
' sheet with comments variables
Dim cmt
Dim sMsg As String
Dim colCount As Integer
Dim iLen As Integer
Dim iIndex As Integer
Dim client As Variant
' Destination sheet variables
Dim wksDest As Worksheet
Dim lNextrow As Long
Dim rngTemp As Range
Set wksDest = Sheets("Sheet2")

For Each cmt In ActiveSheet.Comments
colCount = 1
sMsg = cmt.Text
iLen = Len(sMsg)
For iIndex = 1 To iLen
If Mid(sMsg, iIndex, 1) = vbLf Then
colCount = colCount + 1
End If
Next iIndex

client = Split(sMsg, vbLf)

'or if comma separated use - put an apostophe before the line above
' and remove the apostrophe on the next line

' client = split(smasg,","

With Sheets("Sheet2")
lNextrow = .Range("A1").CurrentRegion.Rows.Count + 1
Set rngTemp = .Range("A" & lNextrow & ":" & Cells(lNextrow,
colCount).Address)
rngTemp = client
End With

Next cmt

End Sub


If the details are separated by spaces, this will not do - 21 The Hi Road
would shown in four columns. If this is the case, Each comment will have to
be edited & a hard return made between each field. Address1, Address2 and so
on.

Once you have the list on a separate sheet you can think about importing the
data int Outlook - I have not done this I'm afraid.

Regards
Peter Atherton

"Dave Peterson" wrote:

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell.Cells(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


BWAnnie wrote:

I have a user who has been using the comments field to store his client
addresses. He now wants the spreadsheet exported from excel into outlook. Is
there any way to export the comments? Or move them into cells on the
spreadsheet.

--

Dave Peterson

I would have expected that the comments were in the same field/column in the
excel data. Then the OP could use the UDF to retrieve the comment into a cell
on that same row.

Then it could be easier to import those comments (along with the other fields)
into Outlook.

Billy Liddel wrote:

Nice one Dave. But I think Annie wants a list that she can copy.

Annie

If a user has entered addresses in comments it is hard to imagine how she
may have entered it - and this would be depend on the solution. The first
macro will show a message with the text in each comment in turn.

Sub ShowCmtText()
Dim cmt
Dim sMsg As String

For Each cmt In ActiveSheet.Comments
sMsg = cmt.Text
MsgBox sMsg
Next cmt
End Sub

If the Names and addresses are shown in separate lines then the following
sub will copy the details to sheet2 (Change this to suit). If the address
details are separated by a comma you will have to change the code as detailed
in the sub below

Sub CommentTextToSheet2()
' sheet with comments variables
Dim cmt
Dim sMsg As String
Dim colCount As Integer
Dim iLen As Integer
Dim iIndex As Integer
Dim client As Variant
' Destination sheet variables
Dim wksDest As Worksheet
Dim lNextrow As Long
Dim rngTemp As Range
Set wksDest = Sheets("Sheet2")

For Each cmt In ActiveSheet.Comments
colCount = 1
sMsg = cmt.Text
iLen = Len(sMsg)
For iIndex = 1 To iLen
If Mid(sMsg, iIndex, 1) = vbLf Then
colCount = colCount + 1
End If
Next iIndex

client = Split(sMsg, vbLf)

'or if comma separated use - put an apostophe before the line above
' and remove the apostrophe on the next line

' client = split(smasg,","

With Sheets("Sheet2")
lNextrow = .Range("A1").CurrentRegion.Rows.Count + 1
Set rngTemp = .Range("A" & lNextrow & ":" & Cells(lNextrow,
colCount).Address)
rngTemp = client
End With

Next cmt

End Sub

If the details are separated by spaces, this will not do - 21 The Hi Road
would shown in four columns. If this is the case, Each comment will have to
be edited & a hard return made between each field. Address1, Address2 and so
on.

Once you have the list on a separate sheet you can think about importing the
data int Outlook - I have not done this I'm afraid.

Regards
Peter Atherton

"Dave Peterson" wrote:

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell.Cells(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


BWAnnie wrote:

I have a user who has been using the comments field to store his client
addresses. He now wants the spreadsheet exported from excel into outlook. Is
there any way to export the comments? Or move them into cells on the
spreadsheet.

--

Dave Peterson


--

Dave Peterson

Good point, but what goes into the actual cell? I thought the use of comments
was to save space so that the cells could be used for something else, maybe
an ID or invoice number.

Anyway the OP has a choice - I guess we are working in the dark most of the
time.

Gegards
Peter

"Dave Peterson" wrote:

I would have expected that the comments were in the same field/column in the
excel data. Then the OP could use the UDF to retrieve the comment into a cell
on that same row.

Then it could be easier to import those comments (along with the other fields)
into Outlook.

Billy Liddel wrote:

Nice one Dave. But I think Annie wants a list that she can copy.

Annie

If a user has entered addresses in comments it is hard to imagine how she
may have entered it - and this would be depend on the solution. The first
macro will show a message with the text in each comment in turn.

Sub ShowCmtText()
Dim cmt
Dim sMsg As String

For Each cmt In ActiveSheet.Comments
sMsg = cmt.Text
MsgBox sMsg
Next cmt
End Sub

If the Names and addresses are shown in separate lines then the following
sub will copy the details to sheet2 (Change this to suit). If the address
details are separated by a comma you will have to change the code as detailed
in the sub below

Sub CommentTextToSheet2()
' sheet with comments variables
Dim cmt
Dim sMsg As String
Dim colCount As Integer
Dim iLen As Integer
Dim iIndex As Integer
Dim client As Variant
' Destination sheet variables
Dim wksDest As Worksheet
Dim lNextrow As Long
Dim rngTemp As Range
Set wksDest = Sheets("Sheet2")

For Each cmt In ActiveSheet.Comments
colCount = 1
sMsg = cmt.Text
iLen = Len(sMsg)
For iIndex = 1 To iLen
If Mid(sMsg, iIndex, 1) = vbLf Then
colCount = colCount + 1
End If
Next iIndex

client = Split(sMsg, vbLf)

'or if comma separated use - put an apostophe before the line above
' and remove the apostrophe on the next line

' client = split(smasg,","

With Sheets("Sheet2")
lNextrow = .Range("A1").CurrentRegion.Rows.Count + 1
Set rngTemp = .Range("A" & lNextrow & ":" & Cells(lNextrow,
colCount).Address)
rngTemp = client
End With

Next cmt

End Sub

If the details are separated by spaces, this will not do - 21 The Hi Road
would shown in four columns. If this is the case, Each comment will have to
be edited & a hard return made between each field. Address1, Address2 and so
on.

Once you have the list on a separate sheet you can think about importing the
data int Outlook - I have not done this I'm afraid.

Regards
Peter Atherton

"Dave Peterson" wrote:

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell.Cells(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


BWAnnie wrote:

I have a user who has been using the comments field to store his client
addresses. He now wants the spreadsheet exported from excel into outlook. Is
there any way to export the comments? Or move them into cells on the
spreadsheet.

--

Dave Peterson


--

Dave Peterson

I try to use comments sparingly.

Even with 256 columns, I can't remember running out of fields--and with 16k
columns in xl2007, it's even more unlikely.

Billy Liddel wrote:

Good point, but what goes into the actual cell? I thought the use of comments
was to save space so that the cells could be used for something else, maybe
an ID or invoice number.

Anyway the OP has a choice - I guess we are working in the dark most of the
time.

Gegards
Peter

"Dave Peterson" wrote:

I would have expected that the comments were in the same field/column in the
excel data. Then the OP could use the UDF to retrieve the comment into a cell
on that same row.

Then it could be easier to import those comments (along with the other fields)
into Outlook.

Billy Liddel wrote:

Nice one Dave. But I think Annie wants a list that she can copy.

Annie

If a user has entered addresses in comments it is hard to imagine how she
may have entered it - and this would be depend on the solution. The first
macro will show a message with the text in each comment in turn.

Sub ShowCmtText()
Dim cmt
Dim sMsg As String

For Each cmt In ActiveSheet.Comments
sMsg = cmt.Text
MsgBox sMsg
Next cmt
End Sub

If the Names and addresses are shown in separate lines then the following
sub will copy the details to sheet2 (Change this to suit). If the address
details are separated by a comma you will have to change the code as detailed
in the sub below

Sub CommentTextToSheet2()
' sheet with comments variables
Dim cmt
Dim sMsg As String
Dim colCount As Integer
Dim iLen As Integer
Dim iIndex As Integer
Dim client As Variant
' Destination sheet variables
Dim wksDest As Worksheet
Dim lNextrow As Long
Dim rngTemp As Range
Set wksDest = Sheets("Sheet2")

For Each cmt In ActiveSheet.Comments
colCount = 1
sMsg = cmt.Text
iLen = Len(sMsg)
For iIndex = 1 To iLen
If Mid(sMsg, iIndex, 1) = vbLf Then
colCount = colCount + 1
End If
Next iIndex

client = Split(sMsg, vbLf)

'or if comma separated use - put an apostophe before the line above
' and remove the apostrophe on the next line

' client = split(smasg,","

With Sheets("Sheet2")
lNextrow = .Range("A1").CurrentRegion.Rows.Count + 1
Set rngTemp = .Range("A" & lNextrow & ":" & Cells(lNextrow,
colCount).Address)
rngTemp = client
End With

Next cmt

End Sub

If the details are separated by spaces, this will not do - 21 The Hi Road
would shown in four columns. If this is the case, Each comment will have to
be edited & a hard return made between each field. Address1, Address2 and so
on.

Once you have the list on a separate sheet you can think about importing the
data int Outlook - I have not done this I'm afraid.

Regards
Peter Atherton

"Dave Peterson" wrote:

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell.Cells(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


BWAnnie wrote:

I have a user who has been using the comments field to store his client
addresses. He now wants the spreadsheet exported from excel into outlook. Is
there any way to export the comments? Or move them into cells on the
spreadsheet.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson