Reading from Clipboard before pasting

schielrn

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
I have searched for this and never really came across this before, but when distributing one of my workbooks to a collegue I thought it might be nice to be able to do this.

Is it possible for VBA to read data from a clipboard so that it knows what sheet to paste the data to? Currently we run a financial tool and 2 reports are ran and I put in a bottom so users could easily just paste special values into the correct place. Sometimes the users forget if they copied the balance sheet data or income statement data and currently its not a big deal because there are no columns to the right of where it is pasting so it isn't screwing anything up and they just delete the data and paste it to the correct sheet.

I have thought of 2 approaches I am going to take, but started to wonder if one could read the data from the clipboard before the paste.

My 2 options would be:

Option 1. Paste data to newly created sheet and check if it is balance sheet or income statement and then move from newly created sheet to correct sheet and delete newly created sheet. The user wouldn't see any of this because I would disable screen updating.

Option 2. Learn how to use Undo within my VBA or keep the Undo cache with items in it as it typically clears when VBA is executed. That way if they paste it and it is wrong I can just undo the VBA paste.

Option 1 I am confident I can handle. Option 2 I can research and feel I could do, but if it comes a time when a sheet has data I do not want to paste over then this option is no good.

I would like to hear any comments or feedback on this and if it is possible to read data off the clipboard so it knows where to paste.

Thanks.
 
Sort by date Sort by votes
Not sure if this helps, maybe not, but in 2003, in Edit, there is an option for Office Clipboard, which lets you view the contents of the clipboard.
 
Upvote 0
That may be of some assistance, I will have to look into that, but recording a macro opening it and clearing the list does not show up in the recording, I was hoping to get some type of code or attributes about it.
 
Upvote 0
This puts the clipboard into a string variable.

Code: 
Dim clipboardData As New DataObject
Dim clipboardContents As String

clipboardData.GetFromClipboard
clipboardContents = clipboardData.GetText

MsgBox clipboardContents & vbCr & " is in the clipboard"
 
Upvote 0
Solution
I would try Mikes idea first. This assumes that you can determine the destination by looking at text alone. The structure of the clipboard is actually quite complicated in office apps. If text alone is not sufficient, I would go with your option one. If you want to delve into the office clipboard and one or more of its many custom formats to determine the destination for your data, I can help you out on this. It will be faster than option one...
 
Upvote 0
I tried Mike's approach, but DataObject is not an option when I type it in? I am using Excel 2003.
 
Upvote 0
Sorry I had to check the reference for Microsoft Forms 2.0.

That works perfectly except the fact that i didn't quite realize what I was getting into with this particular item. It gets exactly what i need, now I just need to modify to make sure I always get the same first line each time when I download the report. Or I may use the len() of the clipboard text as the income statement is always 10000 more lines as we need more detail there.

This is great. Thanks Mike and I may come back with some follow up after I mess around with this a bit myself.
 
Last edited:
Upvote 0
Ok here is what i went with rather than Len(). In case anyone else wanted to know.

Code: 
Dim clipboardData As New DataObject
Dim clipboardContents As String
clipboardData.GetFromClipboard
clipboardContents = clipboardData.GetText
If InStr(1, clipboardContents, "Cash on Hand") = 0 Then
    Sheets("Income Statement").Range("C8").PasteSpecial
    MsgBox "Your Data was pasted to the Income Statement Sheet"
Else
    Sheets("Balance Sheet").Range("D8").PasteSpecial
    MsgBox "Your Data was pasted to the Balance Sheet Sheet"
End If
Since Cash on Hand is a Balance Sheet account it should be a good differing point for me.
 
Upvote 0
Mike:
Can't one use "late-binding" to reference the DataObject, without having to reference Forms 2.0 under Tools, References? If so, how would your code
appear in such case?
TIA,
Jim
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
working with images
Replies
6
Views
302
unluckyuser
U
Rijnsent
  • Solved
Macro speed - copy/paste rows
2
Replies
10
Views
317
Peter_SSs
Peter_SSs
B
  • Solved
Copy and paste range that includes charts
Replies
1
Views
269
BuJay
B
V
  • Question Question
Copy data from 1 sheet and paste in another sheet.
Replies
2
Views
138
Vasanth88ece
V
P
  • Question Question
Create new worksheet from list and copy information from that row to new sheet
Replies
3
Views
88
HighAndWilder
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top