Excel Comments 1

[MinnKota]

I am bringing some Excel data into Access 2002. I have ended up saving the Excel worksheet as tab delimited. But I am having trouble exporting the comments from Excel. Many of the cells have a tagged comment.

 

[SkipVought]

MinnKota,

You're going to have to put the comments into cells. Macro record the process and then expand to the range of rows. Post back if necessary.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

Skip, I figured as much. I'll get on it. Thanks

 

[MinnKota]

Skip,

I am finding the Macro process a bit beyond my skills. I have figure out how to show all comments and then move from comment to comment. (I have the reviewing toolbar showing) For each comment, I choose Edit Comment from the reviewing bar, and then holding Shift I press Page Up and the Home. This selects all the text. I then copy the text with a Ctrl + C. The tab key then allows me to use the Goto command to the appropriate cell. I then paste the text. Here are my issues:

(1) When I paste the data, it pastes on multiple lines, but I would like it to paste in a single cell.

(2) There are four columns that I would like to check for comments and then paste each comment (if there is one) in the appropriate four cells at the end of the same row. I am not sure how to tell Visual Basic how to do this.

That's it I suppose,

Minn Kota

 

[SkipVought]

1) Edit the cell you want to paste into and THEN paste. THis will paste into a single cell.

Post the code that you currently have recorded.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

Skip I have almost got the code...
One question, how might I check to see if the current cell has a comment?

Many thanks

 

[MinnKota]

Here is my code. The error handling is the workaround for the Null check.


Sub Comments()
'
' Comments Macro
' Keyboard Shortcut: Ctrl+m
'
Dim FirstRow As String
Dim LastRow As String
Dim Text As String
FirstRow = "2"
LastRow = "972"

Application.DisplayCommentIndicator = xlCommentAndIndicator
On Error GoTo ErrorResolution:

For i = FirstRow To LastRow
Range("c" & i).Comment.Shape.Select True
Text = Range("c" & i).Comment.Text
Range("k" & i).Value = Text
Next i
For i = FirstRow To LastRow
Range("d" & i).Comment.Shape.Select True
Text = Range("d" & i).Comment.Text
Range("l" & i).Value = Text
Next i
For i = FirstRow To LastRow
Range("e" & i).Comment.Shape.Select True
Text = Range("e" & i).Comment.Text
Range("m" & i).Value = Text
Next i
For i = FirstRow To LastRow
Range("f" & i).Comment.Shape.Select True
Text = Range("f" & i).Comment.Text
Range("n" & i).Value = Text
Next i
For i = FirstRow To LastRow
Range("j" & i).Comment.Shape.Select True
Text = Range("j" & i).Comment.Text
Range("o" & i).Value = Text
Next i

ErrorResolution:
Text = ""
Resume Next

End Sub

 

[SkipVought]

Here's similar code streamlined...

Sub Comments1()
    '
    ' Comments Macro
    ' Keyboard Shortcut: Ctrl+m
    '
    Dim r  As Long
    Dim c As Integer
    Dim Text As String
    
    Application.DisplayCommentIndicator = xlCommentAndIndicator
    On Error GoTo ErrorResolution:
    With ActiveSheet.UsedRange
        For r = .Row To .Row + .Rows.Count - 1
            For c = .Column To .Columns.Count - 1
                Select Case c
                    Case 3: c1 = "K"
                    Case 4: c1 = "L"
                    Case 5: c1 = "M"
                    Case 6: c1 = "N"
                    Case 10: c1 = "O"
                End Select
                Cells(r, c1).Value = Cells(r, c).Comment.Text
            Next
        Next
    End With

ErrorResolution:
    Text = ""
    Resume Next

End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

Skip,

I tried it and it works great! It would be nice if the "For c" loop could be made for efficient, so that it steps through only (3,4,5,6,and 10). I suppose one could always adject the columns prior to running the macro and only run the "For c" loop on the first five columns.

Many thanks

ps how do you post code like that?

 

[SkipVought]

Check out the _{Process TGML} hyperlink below the Your Reply window.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

I have one more issue with this process. Upon my export from Excel, the carriage returns were turned into a box symbol. It is not the end of the world, but it would be nice to replace these with a space I think the best way to solve this would be to actually remove the carriage returns from the comments in Access (using a macro) but I am not sure how to do that.

 

[SkipVought]

NewText Replace(CurrentText, vbCr, "")
NewText Replace(NewText , vbLf, "")

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884