Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Names (Eng/XL97/SR2)

    Is it possible to send a cell containg text from one workbook to another workbook where the second workbook is not active in memory? The code below works only if the second workbook is active.

    Example:
    Workbook 1 is active and cell A1 = "Success"
    Workbook 2 is not active and contains a range name of "Input"

    Code in workbook one:
    Sub TsfrCellDate ()
    Var1 = Range("A1").value
    Workbooks(book2.xls).Worksheets("Sheet1").Range("I nput") = Var1
    End Sub

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Names (Eng/XL97/SR2)

    What exactly do you mean by "Active" ?. Do you mean Open ?

    If both workbooks are Open but Workbook 1 is the active book (and contains the running code) your code shouls work, but you need to pacle quote marks (") around book2.xls. If book2 is not saved, then you need to remove .xls. So the following line will work if book2 is not saved :<pre> Workbooks("book2").Worksheets("Sheet1").Range("Inp ut") = Var1</pre>

    Hope that helps you sort it out.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Names (Eng/XL97/SR2)

    Workbook 1 has been opened and is the only active workbook.

    Workbook 2 is not opened ie can not be active.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Names (Eng/XL97/SR2)

    If book2.xls is not open, it is not in the Workbooks collection. Therefore, there is no way to reference the workbook to store the value into it. So the answer is, no. You must open the workbook to store something into it.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Range Names (Eng/XL97/SR2)

    j, as Legare says, the workbook must be open, but if this is a problem, you can make it invisible. This little demo shows how. It dosn't seem to add much, but if you leave off the close until the other workbook is closed, then you could do great things! HTH --Sam
    <pre>Option Explicit
    Sub demo()
    Dim wsh As Worksheet
    Dim wbk As Workbook
    Set wsh = ActiveSheet
    Set wbk = Workbooks.Open(Filename:="test.xls")
    ActiveWindow.Visible = False
    wsh.Range("A1").Copy wbk.Sheets("Sheet1").Range("A1")
    Application.DisplayAlerts = False
    wbk.Close
    Application.DisplayAlerts = True
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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