Copying from excel - Quotation marks not required

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
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Saqib Husain, SyedEngineerAuthor Commented:
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
ydsonlineCommented:
Have you tried some of the options in  "Paste Special" in Autocad?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Saqib Husain, SyedEngineerAuthor Commented:
In autocad paste-special works only in the drawing area and not in the text/command area
0
Rory ArchibaldCommented:
Did you mean char(10) or char(13)? Is it ok to simply strip those characters out while copying?
0
Saqib Husain, SyedEngineerAuthor Commented:
Rory, either of them gives the same problem. I have to send the enter key so either of them must be present.

Saqib
0
Rory ArchibaldCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerAuthor Commented:
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
Saqib Husain, SyedEngineerAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.