Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Activate the temporary "Book1"

  1. #1
    Board Regular
    Join Date
    Mar 2014
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Activate the temporary "Book1"

    I am trying to activate a "generated" workbook that is unsaved and has no extension, just the name "Book1". If I save it as "Book1.xlsx" and then repoen it, I can refer to it. Like this.

    Code:
    Set sh = Workbooks("Book1.xlsx").Sheets("Stats")
    Set rng = sh.UsedRange
    The question is, can I refer to it while it is unsaved and named simply "Book1"? I have tried
    Code:
    Workbooks.("Book1").Sheets("Stats").Activate
    - to no avail.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,626
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Activate the temporary "Book1"

    Have you tried Workbooks("Book1")?
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Mar 2014
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate the temporary "Book1"

    I think I was doing that before and the "." was a typo. Anyway I get a runtime error, subscript out of range.
    This is a workbook generated by a software application, and I'm afraid it doesn't have a name that Excel likes unless I save it. Strangely I remember being able to activate it some time ago (when I didn't need to), but I don't know how I did that.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,626
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Activate the temporary "Book1"

    So Book1 isn't the name of the workbook?

    Is it the only workbook that's open?

    If it is then you can refer to it using Workbooks(1).

    If it isn't and you know the names of the other open workbooks then you can use code to set a reference to it.

    Another possibilty would be to identify it by partial name, that would only really work if there was some pattern/logic the other application uses for the workbook name.
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate the temporary "Book1"

    Thanks for your reply. No, it's not the only open workbook, and it would not make sense if was, because it's a generated workbook and I need to reference it from another workbook and copy/paste its data.
    I'll attach a screenshot for better understanding.
    https://www.dropbox.com/s/nupo5obgdt...504_201733.jpg
    As you can see the workbook does have a name (Book1) and also ActiveWorkbook.Name is Book1
    Can you please expand on what would be the best option to make a reference to it. I can't simply save it and then reference to it because this has to be done hundreds of times (once per player).
    The software does have a pattern to naming the files. If there's no other generated file open then the name will be "Book1". If there is one generated file already open then the name will be "Book2", if there are two, then "Book3", etc. But I normally only open one.

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Activate the temporary "Book1"

    What does
    Code:
    Debug.Print ActiveWorkbook.FullName
    give you?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    Board Regular
    Join Date
    Mar 2014
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate the temporary "Book1"

    I'm sorry, I don't know where to find what it Debug.Print returns as result.
    After doing this however
    Code:
    Range("G1") = ActiveWorkbook.FullName
    the result is "Book1"

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Activate the temporary "Book1"

    Can you refer to it as
    Code:
    Set sh = Workbooks("Book1.xls*").Sheets("Stats")
    or
    Code:
    Set sh = Workbooks("Book1.xlt*").Sheets("Stats")
    Although be very careful if you can with xlt*


    btw
    I'm sorry, I don't know where to find what it Debug.Print returns as result
    It puts it in the immediate window (if it isn't showing Ctrl-G)
    Last edited by MARK858; May 4th, 2014 at 02:54 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    Board Regular
    Join Date
    Mar 2014
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activate the temporary "Book1"

    No luck. And immediate windows still says "Book1"

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Activate the temporary "Book1"

    No luck. And immediate windows still says "Book1"
    Didn't say it wouldn't...just saying where to find it as you didn't know.


    Afraid no other ideas as I don't think you can refer to it as a .tmp file but then again never tried it so wouldn't know.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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