[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Copying from excel - Quotation marks not required

Posted on 2010-04-06
9
Medium Priority
?
336 Views
Last Modified: 2012-05-09
When I copy the result of a formula from excel to another application, normally it copies as-is. But if the resulting text contains a char(10) then the text copied is enclosed in quotes when pasted. This is the formula I am using

="-insert "&VLOOKUP(IF(LEFT(C12,3)="HR ","HR",C12),Sheet2!$A$1:$B$1000,2,0)&CHAR(13)&G12&","&$J$10&" "&$I$10&" "&IF(E12="R",180,0)

presently I am copying the text to notepad and replace the quotes then copying from notepad to the application which is quite a nuisance.

How can I get rid of this and be able to directly copy to my application.

BTW my application is Autocad.

Saqib
0
Comment
Question by:Saqib Husain, Syed
9 Comments
 
LVL 50
ID: 29981323
Hello ssaqibh,

the double quotes are put in by the receiving application, not by Excel.

If you paste into Word, for example, there will be no quotes.

As a workaround, you may want to

- paste into Word
- copy text in Word
- paste into AutoCad.

A bit clumsy, but faster than pasting into Notepad and replacing the quotes.

cheers, teylyn
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 29982165
Thanks Teylyn. I agree that it is a bit neater than the notepad routine but I would be happier if I could get rid of Word as well. If no one comes up with a better solution I shall accept this solution but I think I will do a vba routine instead for my work.

Saqib
0
 
LVL 5

Expert Comment

by:ydsonline
ID: 29985306
Have you tried some of the options in  "Paste Special" in Autocad?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 29987723
In autocad paste-special works only in the drawing area and not in the text/command area
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 29988636
Did you mean char(10) or char(13)? Is it ok to simply strip those characters out while copying?
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 29990156
Rory, either of them gives the same problem. I have to send the enter key so either of them must be present.

Saqib
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 29990938
Does this work? Needs a reference to MSForms (though it could be late bound):

Sub DoCopy()
    Dim objDO As MSForms.DataObject
    Set objDO = New MSForms.DataObject
    objDO.SetText ActiveCell.Text
    objDO.PutInClipboard
    Set objDO = Nothing
End Sub

Open in new window

0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 29994689
Yes it does

I modified it as follows to copy an entire range

Private Sub CommandButton3_Click()
    Dim objDO As MSForms.DataObject
    Set objDO = New MSForms.DataObject
    For Each cel In Selection
    sstr = sstr & cel.Text & vbCr
    Next cel
    objDO.SetText sstr
    objDO.PutInClipboard
    Set objDO = Nothing

End Sub
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 29994830
But again once I wrote the code I simply redid it to send it directly to autocad
So no selection, no copy no paste.

Private Sub CommandButton2_Click()
Set acd = GetObject(, "Autocad.application")
Set dwg = acd.activedocument
For Each cel In Range("J11:J1000")
If cel = "" Then Exit For
dwg.sendcommand cel & vbCr
Next cel
For Each cel In Range("K11:K1000")
If cel = "" Then Exit For
dwg.sendcommand cel & vbCr
Next cel
End Sub
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question