Results 1 to 13 of 13

Thread: [RESOLVED] run time error 91: activeworkbook.name

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    7

    Resolved [RESOLVED] run time error 91: activeworkbook.name

    Hello -

    I'm opening a workbook and getting a runtime error 91 which debugs to the following line:

    Code:
    LeCurFname = ActiveWorkbook.name
    I have been using this workbook for years and saving it as different files but this is the first time this error has happened. The debugged line is part of this sub:

    Code:
    Sub initializevars()
      LeCurFname = ActiveWorkbook.name
      LeWrkFname = ActiveWorkbook.name
      LeCurCode = Sheets("Global Assumptions").Cells(6, 5)
      LeCurDescription = Sheets("Global Assumptions").Cells(6, 6)
      Application.Caption = AppTitle
      Call FixPath  
    End Sub
    I'm a relative newbie in terms of VBA but any help that can be offered is appreciated!!

    Matt

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,659

    Re: run time error 91: activeworkbook.name

    Thread moved to Office Development/VBA forum (note that the "VB Editor" in Office programs is actually VBA rather than VB)

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    7

    Re: run time error 91: activeworkbook.name

    Also, we've recently switched our office from 2003 to 2007 so it may be a compatibility issue? This is the first time I've had this issue in this line of code.

    Matt

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: run time error 91: activeworkbook.name

    I dunno about 2007, might be a problem. The 91 is usually that you are trying to reference an object but that object is not set. ie, your activeworkbook is nothing so you cannot access the name.

    May be in 2007 you need to do something first to set ActiveWorkbook?

    Have you tried the help with it?
    (type activeworkbook, put your cursor on it, f1 - might mention if it is set or not...)

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  5. #5
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538

    Re: run time error 91: activeworkbook.name

    Both the ActiveWorkbook method and the associated Name property does exist within Excel 2007
    http://msdn.microsoft.com/en-us/library/bb220820.aspx
    Returns Nothing if there are no windows open or if either the Info window or the Clipboard window is the active window.
    This could possibly be the cause here.

    This code would give you a little more control as to throwing up that error automatically and is a good practice. However if the above isn't affecting the ActiveWorkbook being equal to Nothing, I would review your other code prior to the initializevars() call, and also, with a breakpoint on this method line, reviewing whether there are any workbooks loaded and visible at this time.
    Code:
    If not (ActiveWorkbook is nothing) then
      LeCurFname = ActiveWorkbook.name
    End If
    Does it work if you attempt to access the workbook using a specific index number? Workbooks(1).name? If not what is the count of the workbooks shown at this stage (workbooks.count) please?

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  6. #6

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    7

    Re: run time error 91: activeworkbook.name

    Workbooks(1).name” worked okay. But…

    It makes no sense that we have to go through and change the code throughout this spreadsheet just to make it work, for this reason:

    If I understand this right, when I make those changes, the user cannot have any other spreadsheets open, or the results will be unpredictable. I’m pretty sure this index is saying, “Give me the name of first (or maybe the last?) Workbook I opened.” If they have multiple workbooks open, then this code could use the wrong file name for whatever it does – saving, etc.

    Furthermore, once the "workbooks(1).name" got me past that error the next line failed. I sort of expected that, because it also references objects that wouldn’t be there, if the code didn’t “see” the workbook at the time of code execution.

    What’s happening is that information – like the file name of the workbook, worksheet names, etc. – are not visible to this particular spreadsheet when the code is executing. However, all those things are visible in other spreadsheets.

    I really can’t bypass this code, as it initializes variables key to the process.

    I have two spreadsheets that are, to my knowledge, duplicates of each other, in terms of the code.

    In one spreadsheet, this is not a problem. In debug mode, I can see the ActiveWorkbook name exists and is being assigned. The name, by the way, is “Davenport 5-08.xls”.

    In the problem spreadsheet, named “ICC land estate - 8 buildings 05-08.xls”, when I run in debug mode, the ActiveWorkbook object is not defined. Changing the workbook name does not help.

    This is an Excel object, not one of my own, so I wonder why it isn’t assigned in this particular case. Does anyone know of a particular issue with 2007 on this? I guess I could move the code to another event instead of Open, but rather than run away from the issue I’d like to see if anyone has seen sporadic results like this.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    7

    Re: run time error 91: activeworkbook.name

    sorry about the long post :-)

  8. #8
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538

    Re: run time error 91: activeworkbook.name

    Quote Originally Posted by mlvikes13
    If I understand this right, when I make those changes, the user cannot have any other spreadsheets open, or the results will be unpredictable. I’m pretty sure this index is saying, “Give me the name of first (or maybe the last?) Workbook I opened.” If they have multiple workbooks open, then this code could use the wrong file name for whatever it does – saving, etc.
    Right train of thought, but opposite way round

    ActiveWorkbook - actively focused workbook. If the user opens another workbook, or switches to another actively open workbook and starts to edit that, then your code will have problems. The user can have open, and access other workbooks.

    Workbooks(index) - you are explicitly referencing a certain, specific workbook no matter which has the focus. You don't need to use 1, you can loop through the collection and search for an already open workbook name you need, or store the workbook number when/if your code opens a workbook to ensure you always interact with your required one.

    The reason I was saying to try that code originally was to determine whether a workbook was open - that the workbooks collection wasn't null at all. That was my train of thought.

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  9. #9

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    7

    Re: run time error 91: activeworkbook.name

    Alex - Thanks for the clarification. To koosid, I've attached the problem file if you'd like to share your thoughts. Let me know if you need a working version for comparison.

    Thanks!

    Matt
    Attached Files Attached Files

  10. #10

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    7

    Re: run time error 91: activeworkbook.name

    hmm... sorry about that. Let's try again. It won't let me attach an excel file directly
    Attached Files Attached Files
    Last edited by mlvikes13; Jul 30th, 2008 at 01:21 PM.

  11. #11
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538

    Re: run time error 91: activeworkbook.name

    Not entirely sure why that isn't working there, but will this workbook always have the same name or contain common text such as "ICC land estate" or "ICC land estate - " and "buildings" which would be unique to this workbook only?

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  12. #12
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: run time error 91: activeworkbook.name

    I think I know what is the problem

    Follow these steps...

    1) Open the file. It will give the error. That's ok. Click on Debug. It will take you to the VBA Editor.
    2) Switch to the Excel application. you will not see your sheet. That's ok again.
    3) Click on Menu Windows=>Unhide and show the workbook
    4) Save the file and exit
    5) Now open it. there will be no error.

    The problem was that the workbook was hidden when it was opening hence

    Activeworkbook.Name was failing.

    Hope this helps....
    Last edited by Siddharth Rout; Feb 12th, 2020 at 01:00 AM. Reason: Too many smilies... :P
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  13. #13

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    7

    Re: run time error 91: activeworkbook.name

    Well that's just embarrassing. hopefully next time I post I can at least post something challenging!

    Thanks to both you guys for helping me out.

    Matt

Posting Permissions

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



Click Here to Expand Forum to Full Width