I have several lines of VB that I have in Excel that I am concantanting
and then wanting to drop into the VB editor. When I do this, Excel is
adding a " to the front and back of the code, why?

Example:

Cell A1 = Workbooks.Open Filename:= _
Cell A2 = C:\Documents and Settings\Richard Sabbara\My
Documents\Budget20060730.xls
Cell A3 = A1&CHAR(10)&A2

The result in A3 is:
"Workbooks.Open Filename:= _
C:\Documents and Settings\Richard Sabbara\My
Documents\Budget20060730.xls"

This becomes an even bigger issue for me when I am trying to build this
out using cell references, b/c it will add extra " to the cell
refrences as well. It might just be that Excel is not the program for
this.

Any suggestions?

Richard

First, I would think you'd want something like:

=A1&CHAR(10)&CHAR(34)&A2&CHAR(34)
in A3.

And I think that's just the way excel/windows work. Even if you paste into
Notepad, you get the extra double quotes.

But maybe you can put the value in the cell using code?

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myVal As Variant

Set MyDataObj = New DataObject

myVal = ActiveCell.Value 'select the cell first
MyDataObj.SetText myVal
MyDataObj.PutInClipboard

End Sub

You'll have to have a reference to "Microsoft Forms 2.0 Object Library" to make
this work (tools|References within the VBE).

You'll want to read Chip Pearson's notes on working with the clipboard:
http://www.cpearson.com/excel/clipboar.htm

And then just pasting into the VBE seems to work ok.



wrote:

I have several lines of VB that I have in Excel that I am concantanting
and then wanting to drop into the VB editor. When I do this, Excel is
adding a " to the front and back of the code, why?

Example:

Cell A1 = Workbooks.Open Filename:= _
Cell A2 = C:\Documents and Settings\Richard Sabbara\My
Documents\Budget20060730.xls
Cell A3 = A1&CHAR(10)&A2

The result in A3 is:
"Workbooks.Open Filename:= _
C:\Documents and Settings\Richard Sabbara\My
Documents\Budget20060730.xls"

This becomes an even bigger issue for me when I am trying to build this
out using cell references, b/c it will add extra " to the cell
refrences as well. It might just be that Excel is not the program for
this.

Any suggestions?

Richard

--

Dave Peterson