Copy from Excel and Paste in external app?

[jmpatrick]

Good morning!

Here's today's issue: I'm copying from a cell in Excel to another app. I'm getting a trailing carriage return when I paste in Notepad, Word, and Adobe Acrobat.

Result:

If I copy the text D10 and paste it in E10 I can get the value up in the Formula Bar. If I copy the text in the Formula Bar I can paste it anywhere without the trailing carriage return. Is it possible to copy the formula using VBA?

 

[Micron]

Did you try expanding row 10 height 2 or 3 times what it is and see what happens if you click below the visible value? Your cell values just might contain trailing character(s) that create a new line. That would suggest the values come from another source although you didn't say they do. If it is as I describe, the right choice is probably to remove the trailing characters instead. That can be done via vba also.

Is it possible to copy the formula using VBA?

What's in the formula bar doesn't look like a formula to me.

 

[jmpatrick]

Did you try expanding row 10 height 2 or 3 times what it is and see what happens if you click below the visible value? Your cell values just might contain trailing character(s) that create a new line. That would suggest the values come from another source although you didn't say they do. If it is as I describe, the right choice is probably to remove the trailing characters instead. That can be done via vba also.

What's in the formula bar doesn't look like a formula to me.

Correct no formula in the formula bar, but it has the text I need to paste and when I copy it from there and paste it externally it works correctly.

 

[Micron]

If you copy a single line from the formula bar you probably won't copy the line feed characters if they are there. That's why I suggested to increase the row size and look for them as described.

 

[jmpatrick]

If you copy a single line from the formula bar you probably won't copy the line feed characters if they are there. That's why I suggested to increase the row size and look for them as described.

I checked. No extra characters in the cell. Is there a way to copy from the formula bar using VBA?

 

[Micron]

Sorry, not sure. Perhaps this

Copy from Formula Bar

Hi all, Sometimes copying from the cell (passwords or links), after pasting does not work. Is there a macro to copy Formula Bar's contents? Thank you in advance.

www.mrexcel.com

although you might get away with using Trim function on the cell contents. If you want to post a wb copy somewhere I'll see what I can do. You can remove everything private that is not required and just leave enough in to replicate the issue.

 

[duggie33]

You could try the following code. It works for me on the cells with a concatenate formula. Change "Sheet1.Range("D1:D50")" to match the range that you want to be copied. The idea is that when you select that cell, the text value of the cell will be copied to the clipboard.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Sheet1.Range("D1:D50")) Is Nothing Then
        With New DataObject
            .SetText Target.Text
            .PutInClipboard
        End With
    End If

End Sub

Hope that helps,

Doug

 

[jmpatrick]

You could try the following code. It works for me on the cells with a concatenate formula. Change "Sheet1.Range("D1:D50")" to match the range that you want to be copied. The idea is that when you select that cell, the text value of the cell will be copied to the clipboard.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Sheet1.Range("D1:D50")) Is Nothing Then
        With New DataObject
            .SetText Target.Text
            .PutInClipboard
        End With
    End If

End Sub

Hope that helps,

Doug

Thanks, Doug. Using your code I'm getting this error:

 

[duggie33]

Sorry, in VBA editor, Tools, References, put a check by "Microsoft Forms 2.0 Object Library".

Doug

 

[jmpatrick]

Sorry, in VBA editor, Tools, References, put a check by "Microsoft Forms 2.0 Object Library".

Doug

Still getting the same error.