Excel VBA - Copy first set of characters in ActiveCell before space

Question

Here's what I have so far. Immediate window shows the result I want, but Clipboard remains without that result. I want those characters on the Clipboard.

Sub CopyFirst()
Dim position As Integer
Dim substring As String
position = InStr(ActiveCell, " ")
If (position > 0) Then
    substring = Left(ActiveCell, position - 1)
     Debug.Print substring

End If
End Sub

just put that text in any empty cell and then `Range.Copy` – cyboashu Sep 13 '18 at 18:02 — cyboashu, Sep 13 '18 at 18:02
@Alex Did you try my late-binding solution? – dwirony Sep 13 '18 at 18:16 — dwirony, Sep 13 '18 at 18:16

score 1 · Answer 1 · answered Sep 13 '18 at 17:59

To avoid early binding:

Sub CopyFirst()

Dim position As Integer
Dim substring As String
Dim MSForms_DataObject As Object

Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

position = InStr(ActiveCell, " ")

If (position > 0) Then
    substring = Left(ActiveCell, position - 1)
End If

MSForms_DataObject.setText substring
MSForms_DataObject.PutInClipboard

Set MSForms_DataObject = Nothing

End Sub

nutsch · Accepted Answer · 2018-09-13T18:21:47.477

0

There's nothing in your code that touches the clipboard yet. Add a reference to Microsoft Forms 2.0 library if you don't have it yet.

Sub CopyFirst()
Dim position As Integer
Dim substring As String
position = InStr(ActiveCell, " ")
If (position > 0) Then
    substring = Left(ActiveCell, position - 1)

    Dim MyText As DataObject
    Set MyText = New DataObject

    On Error Resume Next
    MyText.setText substring
    MyText.PutInClipboard

End If
End Sub

edited Sep 13 '18 at 18:21

answered Sep 13 '18 at 17:58

nutsch

5,922
2
20
35

Compile error: "User-defined type not defined" on line MyText As DataObject – Alex Sep 13 '18 at 18:03
2

@Alex If you want to use this solution you'll need **early binding**, aka you have to add the reference library "Microsoft Forms 2.0 Object Library". The alternative is to use late binding where you declare the object instead. – dwirony Sep 13 '18 at 18:08
@dwirony - I went with adding the reference library "Microsoft Forms 2.0 Object Library" - now code works as expected - Thank you and nutsch - will mark this as answer. Can you recommend code to add data from clipboard when I re-name a file? I have the code below, just need full code to pass clipboard contents as first set of characters to a new file name: Name Range("E1") & Range("H" & (ActiveCell.Row)).Value As Range("C2") & Range("H" & (ActiveCell.Row)).Value – Alex Sep 13 '18 at 18:31
why do you want to use the clipboard rather than a public variable? – nutsch Sep 13 '18 at 18:41
@nutsch - not partial to any one solution, but the first piece of a puzzle is now solved by use of clipboard, do you have a code for the second part - to add data from clipboard when I re-name a file? I have the code below, just need full code to pass clipboard contents as first set of characters to a new file name: Name Range("E1") & Range("H" & (ActiveCell.Row)).Value As Range("C2") & Range("H" & (ActiveCell.Row)).Value – Alex Sep 13 '18 at 19:21
check this: https://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard – nutsch Sep 13 '18 at 19:24

Excel VBA - Copy first set of characters in ActiveCell before space

2 Answers2