Comma delimited output for comments

[Michael Reach]

We have some Excel files with data in the cells, + comments often added when
you hover over them. I need to output the Excel to a text file to work with
it in some other code. Is there a way to output the data but also the
comments, say to a csv file or something?
I saw that I could output to xml Spreadsheet 2003, but it has loads of other
things in it I don't need. I'll do it if I have to, but I'd much rather just
get the data and comments.
Thanks

 

[Gord Dibben]

You could extract the Comments text to a range on same worksheet or all
Comments on all sheets to a new sheet which you can save as a new workbook
and *.csv

Or extract to Word and save that as *.csv

Lots of options.

Which way do you want to go?


Gord Dibben MS Excel MVP

On Thu, 18 Feb 2010 08:26:06 -0800, Michael Reach <Michael

 

[Michael Reach]

I guess either would be fine. Columns directly to the right of the originals
might be simpler, so that the rows would line up, then you could save the
whole thing at once as .csv.
Michael

 

[Gord Dibben]

This macro from Dave Peterson will extract the text to an adjacent cell if
that cell is blank.

Before running the macro, insert a new blank column to the right of each
current column.

Sub ShowCommentsNextCell()
'based on code posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

For Each mycell In commrange
If mycell.Offset(0, 1).Value = "" Then
mycell.Offset(0, 1).Value = mycell.Comment.Text
End If
Next mycell

Application.ScreenUpdating = True

End Sub


Gord

 

[Michael Reach]

That's excellent, thank you. Now is there a way to run that macro on a whole
sequence of Excel files, or sheets? And is there a way to write them out to
..csv files automatically as well?
Thanks again,
Michael

 

[Gord Dibben]

Saving as *.csv means you would have to break out each sheet to its own
file.

To do what you want on all sheets in a workbook would require modifications
to iterate through the workbook sheet by sheet, inserting blank columns and
extracting the comments text, copying each sheet to a new workbook and save
that new workbook as *.csv

For starters this revision will extract the comments text to new blank
columns on each sheet in a workbook and save each sheet as its own
sheetname*.csv file.

Will overwrite existing workbooks of same name without warning.

To prevent that remove Applications.DisplayAlerts = False

Also edit the path to save in.

Sub ShowCommentsNextCell()
'based on code posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False
Application.DisplayAlerts = False 'remove if need warning

Dim commrange As Range
Dim mycell As Range
Dim Sh As Worksheet
Dim i As Long
Dim j As Long

Set Sh = ActiveSheet
i = Sh.Range(Cells(1, 1), Cells(Cells(1).Row, _
Columns.Count).End(xlToLeft)).Count

For Each Sh In ActiveWorkbook.Worksheets
On Error Resume Next
For j = i To 1 Step -1
Sh.Columns(j).Offset(0, 1).EntireColumn.Insert
Next j

Set commrange = Sh.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

For Each mycell In commrange
If mycell.Offset(0, 1).Value = "" Then
mycell.Offset(0, 1).Value = mycell.Comment.Text
End If
Next mycell
Sh.Copy
With ActiveWorkbook
.SaveAs Filename:="C:\yoursavefolder\" & _
Sh.Name & ".csv", FileFormat:=xlCSV
.Close
End With
Next Sh

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


Gord