PDA

View Full Version : paste as text from other applications



zamboni
09-07-2016, 05:39 AM
Hi all

I would like to have data which is pasted into a sheet's specific input cell (B8, that is) to always be pasted as values or as text (in cases where the data source is an external application such as a web browser).
In order to achieve this goal I made some tweaks in code that I have used before and which undoes a simple paste and performs a paste special subsequently.
However, my amended code does not work as I want it to. The Worksheet.PasteSpecial method seems to ignore that I want to paste the data as text.
BTW: The Range.PasteSpecial method works just fine - when copying data from within Excel it gets pasted as values only.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range) 'or Target as Excel.Range
'code forces 'simple pasting' to be performed as 'paste values' if data is pasted into cell B8
'Target is a variable which represents the last changed cell


On Error GoTo errorHandler


Application.EnableEvents = False


If Not Intersect(Target, Range("B8")) Is Nothing Then 'If Target.Address = Range("B8") Then <~~ did not work
If Application.CutCopyMode = xlCopy Then
Application.Undo
Target.Select
ActiveSheet.PasteSpecial Format:="Text" ', NoHTMLFormatting:=True <~~ had no effect
Target.PasteSpecial Paste:=xlPasteValues
End If
End If


errorHandler:
Application.EnableEvents = True


End Sub

Any suggestions?

Thanks
Dennis

GTO
09-07-2016, 07:41 AM
Greetings Zamboni,

I would suggest trying this: http://www.siddharthrout.com/2011/08/15/vba-excelallow-paste-special-only/

...modified to meet your needs. Regardless of methodology selected, I would add 'And Target.Count = 1 to your IF test.

Hope that helps,

Mark

zamboni
09-08-2016, 05:50 AM
Hi GTO

Thanks for your quick reply.

I did as you proposed and tried out the code from the link that you provided. Unfortunately, it seems that the code somehow interferes with the rest of my project so I get a Method 'List' of object '_CommandBarComboBox' failed error message.

So I got back to my original code and changed it to the following:

Private Sub Worksheet_Change(ByVal Target As Range) 'or Target as Excel.Range
'code forces 'simple pasting' to be performed as 'paste values' if data is pasted into cell B8
'Target is a variable which represents the last changed cell


On Error GoTo errorHandler


Application.EnableEvents = False


If Not Intersect(Target, Range("B8")) Is Nothing And Target.Count = 1 Then 'If Target.Address = Range("B8") Then <~~ did not work
If Application.CutCopyMode = xlCopy Then
Application.Undo
Target.Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, displayasicon:=False ', NoHTMLFormatting:=True <~~ had no effect
Target.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=False
End If
End If


errorHandler:
MsgBox "Error #" & Err.Number & " - " & Err.Description
Application.EnableEvents = True


End Sub
This results in an 1004 error at run-time (PasteSpecial method of Worksheet class failed).

I cannot tell which line is causing the error but both PasteSpecial methods show the following expressions which are not being capitalized by the editor:
displayasicon:=False
operation:=xlNone
skipblanks:=False

This is all very frustrating given the fact that I started with the following code (which works like a charm):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'code forces 'simple pasting' to be performed as 'paste values'


On Error Resume Next


If Application.CutCopyMode = xlCopy Then


Application.EnableEvents = False


Application.Undo
Target.Select
ActiveSheet.PasteSpecial Format:="Text"
Target.PasteSpecial Paste:=xlPasteValues


Application.EnableEvents = True


End If


End Sub



Having spent much more time on this than what I deem would be appropriate I am going to stick eventually to the simple yet working code above.

Unless of course, somebody will come up with a bright idea...

Thanks to everyone for reading.
Hope I haven't wasted your time.

Cheers
Dennis

snb
09-08-2016, 06:04 AM
Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$B$8" Then
Application.enableevents=false

With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
Target = .GetText
End With

Application.enableevents=true
end if
End Sub