+ Reply to Thread
Results 1 to 2 of 2

Excel is adding "" to my concanteted fields

  1. #1

    Excel is adding "" to my concanteted fields

    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


  2. #2
    Dave Peterson
    Guest

    Re: Excel is adding "" to my concanteted fields

    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.



    [email protected] 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1