Help with Paste.

[huzman]

Hello all! Happy New Year!

I have two macros as follows:

Sub Paste_Excel()

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub
_______________________________________________________________________
Sub Paste_Text()

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False

End Sub
_______________________________________________________________________

I want to have one command button which functions as follows: IF the data to be pasted is from Excel, use Paste_Excel ELSEIF the data to be pasted is not from Excel, use Paste_Text.

How do I code this??

Thanks!!

 

[Andrew Poulsom]

This seems to work:

Private Sub CommandButton1_Click()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End Sub

 

[huzman]

Andrew,

Hello!!

this is the code i have now:

Private Sub CommandButton6_Click()

On Error Resume Next

ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

End Sub

When i try to paste data from within excel, it doesn't work, but when the data is from outside of excel (ex. from Word), it works.

What do you think?

 

[Andrew Poulsom]

Well in my code the first PasteSpecial method referenced the Selection object not the ActiveSheet object. I expect that's why your version isn't working.

 

[huzman]

I now have the following, the first method still doesn't work. Thanks!!

Private Sub CommandButton6_Click()

On Error Resume Next

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

End Sub

 

[Andrew Poulsom]

It works for me (XL 2000). Maybe you are selecting a range that is not equal in dimensions to the copied range, thus creating an error. Try selecting a single cell and clicking the button.

 

[EDUCATED MONKEY]

set up office 2007 xp pro ie 8

Hello I have just been looking through the site and found your article, my problem is very similar! I am looking for a solution not really known the cause,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
First here is the code I am using <o></o>
<o></o>
ActiveSheet.PasteSpecial Format:="Text", LINK:=False, DisplayAsIcon:= _
False
The situation is I send a url plus a account key to access the site the server then send a reply the format being xml , the code above being used to past the xml in to a worksheet column A:A
Most of the time so about 90% it works fine the other 10% it fails and I cannot see why more over the data collect at the moment of failure is repeated without the failure happening, <o></o>
Action taken so far I have checked the generation of the URL and found no error, this is confirmed by the fact that the relevant data is returned by the server <o></o>
I have increased and decreased the Application.Wait time again this has made no difference
I have run the entire batch of URLs to see if there is some problem with volume of the data and found that I could run that same batch and not get the error
Please see code below
regards pete

 Dim Q As String

Dim aurl As String
Dim EXP
                         Sheets("CONTROL").Select
           Q = Worksheets("CONTROL").Range("C2").Value ' pass AUTHOR in from other process
aurl = "http://api.alibris.com/v1/public/search?wquery=" & Q & "&apikey=xxxxxxxxxxxxxxxxxxx"
                      Set EXP = CreateObject("InternetExplorer.application")
                                 EXP.Visible = True
                                      EXP.Navigate (aurl)          
                  Application.Wait (Now + TimeSerial(0, 0, 4))
                                  SendKeys "^a"
                                  SendKeys "^c"
                 Application.Wait (Now + TimeSerial(0, 0, 3))
                         Worksheets("INCOMMING_DATA").Select     
                       Worksheets("INCOMMING_DATA").Range("A1").Select
   ActiveSheet.PasteSpecial Format:="Text", LINK:=False, DisplayAsIcon:= _
       False
 
<o:p></o:p>
End Sub