VBA/API pasting from clipboard in Word

[dprayner]

Hi people. I am having trouble pasting from the clipboard. I am using SendKeys "^v" to paste, but the problem is I am trying to do this from a form and I need to put it in a form close event triggered by a Unload statement. There doesn't seem to be a Form_Close() event, but I have tried Form_Deactivate, and Form_Terminate, without success. When the user clicks on a command button I want several things to happen. First the item is copied to the clipboard (done), second the form is closed (Unload UserForm1), and third the item is pasted into the body of the Word Document. Any suggestions? DAVE

 

[Bogiemon]

Have a look at the DataObject ojbect

 

[TonyJollans]

Hi Dave,

What is the problem with the normal Paste method?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dprayner]

Hi people. unfortunately I am trying to store a picture and DataObject currently supports only text formats.

As far as the normal paste method, I need to use VBA code to accomplish this task. Although I have the code, it will not paste (or not paste correctly) when I invoke the SendKeys "^v". I need to include this in a sub triggered when the code executes the Unload UserForm1 statement.

DAVE

 

[TonyJollans]

Hi Dave,

I'm not sure I understand. What is wrong with the simple Selection.Paste (or .PasteSpecial, or .PasteAndFormat ....)? What happens when you try and use any of these? They are much more reliable than SendKeys.

When you've got the paste working you can worry about the triggering and timing of it (which may be affected by the method of pasting). However I don't see why it should fail in the userForm Terminate event (or the CommandButton Click event for that matter).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dprayner]

Well I think the Selection.Paste might work, but I don't have anything to reference. It's not like in Excel where I can reference a cell or Range of cells. I'm using Word and UserForm1.Paste doesn't work.

The UserForm_Terminate only seems to work when the user click on the X to close the form, but when the statement Unload UserForm1 is executed, it ignores the UserForm_Terminate sub for some reason.

DAVE

 

[TonyJollans]

Hi Dave,

The Terminate event should fire in response to Unload - but if that causes a problem just do the paste before you unload the form - it's all in the same interaction as far as the user is concerned.

You can specify a range to paste in word just as you can in excel. Not sure what you mean when you say Userform1.Paste - are you trying to paste onto the userform? If so, I can see you have a problem.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dprayner]

Hi guys. Create a VB form with one command button in Word and add the following code:

Private Sub cmdSubmit_Click()
Call ClearClipboard
WordBasic.SendKeys "(%{1068})" 'Alt Print Screen
Unload UserForm1
End Sub

Private Sub UserForm_Terminate()
'MsgBox "Pasting", vbOKOnly
SendKeys ("^v") 'Paste
End Sub


Module 1 code:

Sub ClearClipboard()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText ""
MyData.PutInClipboard
End Sub

See what you get for results and let me know. DAVE

 

[TonyJollans]

Dave,

Do you mean a VB Form? I do have the technology but not a vast amount of knowledge. I assumed we were talking about a Word UserForm? Will try later.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dprayner]

Hi Tony. Yes the VB Form from Word's VB Editor. DAVE

 

[TonyJollans]

Hi Dave,

Interesting! The posted code did nothing useful but I played with and got some bizarre results. I will need to play a bit more!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dprayner]

Hi Tony. I added code to the Document_Open event UserForm1.Show and if you manually hit control v you can see what is in the clipboard. Also I commented out the clear clipboard and it works better. DAVE

 

[TonyJollans]

Hi Dave,

I've just realised why I get the odd results - and why your code fails. It's a timing issue - the Print Screen needs time to complete. The Paste fails because the screen image hasn't reached the clipboard before you try and take it from there.

I added a quick DoEvents loop to give it time and (my edited version) worked fine. I've moved the paste into the click event - it doesn't add anything using the terminate event, and this runs for me ..

Private Sub CommandButton1_Click()
Call ClearClipboard
WordBasic.SendKeys "(%{1068})" 'Alt Print Screen
For f = 1 To 100
DoEvents
Next
Unload UserForm1
Selection.Paste 
'SendKeys ("^v") 'Paste
End Sub
 
Private Sub UserForm_Terminate()
'MsgBox "Pasting", vbOKOnly
'SendKeys ("^v") 'Paste
End Sub


'Module 1 code:

Sub ClearClipboard()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText ""
MyData.PutInClipboard
End Sub

I'm sure the DoEvents loop is over the top and there are probably other improvements that can be made but it does work for me.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

Hi Dave,

I've had another look at this and the critical thing is the DoEvents; it makes sure the SendKeys queue is processed. I don't see the point of clearing the clipboard as you're going to overwrite it anyway and I have what appears to work (but not thoroughly tested) using just the click event - in fact I've used the Form click event so the button is unnecessary.

So, any userform you like with this code ..

Private Sub UserForm_Click()
    WordBasic.SendKeys "(%{1068})" 'Alt Print Screen
    DoEvents
    Unload Me
    Selection.Paste
End Sub

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dprayner]

Thank you Tony. You're the man. That did the trick. I was so close, but yet so far. DAVE