+ Reply to Thread
Results 1 to 12 of 12

Closing a Workbook from another workbook: Workbooks.close error: Subscript out of range

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Closing a Workbook from another workbook: Workbooks.close error: Subscript out of range

    Hello,
    In the code below, I first check to see if a certain workbook is open from a separate workbook. If it is, I would like to save and close it.
    The workbook is open, but when it attempts to save and close it (in line Workbooks("S:\Public\PMG\Fixed Income\FI-AA\Attribution\AttributionUS2013v3.xlsm").Close), I recieve Run Time Error '9': Subscript out of range.

    Any ideas on how I can solve this?
    Thank you in advance

    Sub Sample()
    Dim Ret

    Ret = IsWorkBookOpen("S:\Public\PMG\Fixed Income\FI-AA\Attribution\AttributionUS2013v3.xlsm")

    If Ret = True Then
    Workbooks("S:\Public\PMG\Fixed Income\FI-AA\Attribution\AttributionUS2013v3.xlsm").Close True
    End If
    End Sub

    Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = err
    On Error GoTo 0

    Select Case ErrNo
    Case 0: IsWorkBookOpen = False
    Case 70: IsWorkBookOpen = True
    'Case 75: IsWorkBookOpen = True 'this is read only open
    Case Else: Error ErrNo
    End Select
    End Function

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    the index to the Workbooks collection does not include the path
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    Unfortunately, I get the same error whether I use:
    Workbooks("S:\Public\PMG\Fixed Income\FI-AA\Attribution\AttributionUS2013v3.xlsm").Close True or
    or
    Workbooks("AttributionUS2013v3.xlsm").Close True

    Any ideas on why that might be?
    Thanks.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    then the workbook is not open in the current excel instance-perhaps someone else has it open, or the function does not work with the new zipped file formats

  5. #5
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    Tracing through the code in debug mode, the file is indeed open- the if statement triggers.
    The code throws an error on the Workbooks("AttributionUS2013v3.xlsm").Close True line.
    I agree with you that this is a very odd error, I searched Google for quite awhile and couldnt find a similar problem.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    it's not odd-I didn't say it was ;-)

    the workbook cannot be open in the same instance of excel

  7. #7
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    Haha very true- it is just odd to me.
    The workbook is open in a separate instance of excel. So once again, I am not sure what is causing this issue. Seems like it should be straight forward, but there is obviously something I am missing .

  8. #8
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    Not sure if it matters, but the workbook which is checking to see if the other workbook is open, then saving/closing it, is on my desktop, while the workbook to be saved/closed is on a shared drive. I don't believe that should make a difference, but this problem is very baffling.
    Both are open in separate instances of excel.
    I appreciate your help, hopefully this can be solved.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    You'd have to get the other instance of Excel that has it open and close it there.

    No, sorry.
    Entia non sunt multiplicanda sine necessitate

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    the Workbooks collection refers to the workbooks open in the current instance of excel unless you qualify it with a different application instance
    you might use getobject and pass the full path after checking it's open
    Please Login or Register  to view this content.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    Saving the workbook in the other instance will immortalize any fat-fingered mistake, and not saving would discard any deliberate changes. You could add code to test that, but I'd just tell the user to find it and close it.

  12. #12
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Closing a Workbook from another workbook: Workbooks.close error: Subscript out of rang

    Awesome that did it. Thank you so much! And apologies, I realized I misread your previous statement about different instances.
    Cheers

+ 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