Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook name (Excel 2003)

    How can I name a new workbook so that I can reference it within a macro? For example, if I had the following code:

    Workbooks.Add 'creates a new workbook
    'I forget how to do this, but switch to another window, called "AnotherWorkbook.xls"
    'do some stuff in that workbook
    'switch back to my newly created workbook

    The problem is, excel automatically names the new workbook Book1, Book2, Book3, etc. and the macro has no way (that I know of) to know the name of that new workbook. The only solution I've found so far is to save it, and then I have its name available from the SaveAs dialog box. It's functional but not elegant!

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

    Re: Workbook name (Excel 2003)

    To name the workbook, you have to save it. If you just want to reference it, you can do something like this:

    <pre>Dim oWbk As Workbook
    Set oWbk = WorkBooks.Add
    ' Do stuff on other workbooks
    oWbk.Activate
    </pre>

    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workbook name (Excel 2003)

    Use a variable of type Workbook:

    Dim wbk As Workbook
    Set wbk = Workbooks.Add
    With Workbooks("AnotherWorkbook.xls")
    ...
    ...
    End With
    wbk.Worksheets(1).Range("A1") = 37

Posting Permissions

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