Access & Excel Notes

Monday September 10 2007

Copying an Excel range to the Windows clipboard

Filed under: Excel,VBA — ccnotes @ 5:02 pm

This will copy a range to the clipboard to be pasted elsewhere.  I needed it to copy my plant notes into cms from my plant note template. 

If you paste into notepad or other text editors it all goes onto one line.  However, it works correctly in CMS, with line returns where they should be. 

Sub CopyMe2Clipboard()
    Application.Goto Reference:="CopyMe"
    Call SelectionToClipboard
End Sub
Sub SelectionToClipboard()
'I got this from an XLA that was apparently intended to copy a selection to a HTML table or something
'It was called Confluence and is by philbogle at gmail
    Dim specialChars As String
    specialChars = Chr(7) & Chr(10) & Chr(13) & "\"
    Dim result As Variant
    Dim cellStr As String
    Dim i, j As Integer
    result = ""
    For i = 1 To Selection.Rows.Count
        result = result                            ' & "| "     He formatted this with borders, I don't want
        For j = 1 To Selection.Columns.Count
            Dim cell As Range
            Set cell = Selection.Cells(i, j)
            cellStr = cell.Text
            If Not IsEmpty(cellStr) And cellStr <> "" Then
                Do While InStr(specialChars, Right(cellStr, 1)) > 0
                    cellStr = Left(cellStr, Len(cellStr) - 1)
                Loop
            End If
            cellStr = Replace(cellStr, Chr(13), "\\" + Chr(13))
            cellStr = Replace(cellStr, Chr(10), "\\" + Chr(13))
            If cell.Font.Bold Then cellStr = "*" & cellStr & "*"
            If cell.Font.Italic Then cellStr = "_" & cellStr & "_"
            result = result & cellStr              ' & " | "     He formatted this with borders, I don't want
        Next
        result = result & Chr(13)
    Next
    Dim MyDataObj As New DataObject
    MyDataObj.SetText result
    MyDataObj.PutInClipboard
    MsgBox "Plant notes copied to clipboard: " & Chr(13) & Chr(13) & result
End Sub
 
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: