Problem in getting PasteSpecial together with Unicode text to paste where I want.

[richarddawson]

I can get :-
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
to work.

But setting the active cell is not straightforward.
Workbooks("myBook").activate always works but
Cells(1, 1).select (and Cells(1, 1).activate) sometimes work but are both unreliable.

Can anyone help, please?

 

[MARK858]

Maybe...

Workbooks("myBook").[COLOR="#FF0000"]Sheets(1)[/COLOR].Cells(1, 1).PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

Change the red bit as appropriate.

Please try and avoid activating/selecting as it only slows the code down and 99% of the time is unnecessary.

 

[richarddawson]

Thanks for that.

I am aware of the "avoid activating" advice, but,, try as hard as I can, I have been unable to avoid it
in this particular instance.

Can you suggest any way, please?

 

[richarddawson]

These two procedures both try to do the same thing.

The first one works; the second doesn't.

Sub tryFirst()
Application.DisplayAlerts = False
Workbooks("getMonths.xlsm").Sheets("ped").Cells(1, 1).Activate
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Application.DisplayAlerts = True
End Sub

Sub trySecond()
Application.DisplayAlerts = False
Workbooks("getMonths.xlsm").Sheets("ped").Cells(1, 1).PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Application.DisplayAlerts = True
End Sub

 

[MARK858]

Are you sure that you aren't clearing the clipboard as you don't have the copy in the same procedure?

Btw, what alert are you trying to hide and why?

 

[richarddawson]

Yes - I do have material on the clipboard.
I am not sure why I am blocking alerts - probably residual - I am trying without it.

Strangely the routine works a few times - then it stops working - but if I close excel and reload it it works, for a few times again.

 

[MARK858]

By on the clipboard I mean do you still have the marching ants on the cells you are copying from?

 

[richarddawson]

No, I am copying from a third party website. But I can, and do, check that there is material available to paste on the clipboard,
by clicking on paste; paste; and looking to see HTML and Unicode text and text are all available.
I have never seen that being so and the material failing to paste; so I am confident that the stuff I want is on the clipboard.

The problem is that it is highly formatted - and I want it in plain text which I cannot get from the more common versions of pasteSpecial - if I could then
I would be able to use Destination:=

 

[MARK858]

What do you get with....

Sub test()

    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
       .GetFromClipboard
       Workbooks("getMonths.xlsm").Sheets("ped").Cells(1, 1) = .GetText
    End With

End Sub

 

[richarddawson]

An exact copy of your sub, but in a single cell.