VB close an open workbook from the macro of another workbook

DavidH7470
DavidH7470 used Ask the Experts™
on
I have an excel workbook that runs a macro when it opens that will open another workbook in read only and then close the workbook that the macro openned.  I do this from other macro's quite often and simply have the macro select the workbook that it openned and then do a file close.  But with this particular macro I get the error. "An Error has occurred: 9 - Subscript out of range"  and the line Windows("Workbook2.xls").Activate
is highlighted

The code is:

(Run from Workbook1 when is opens)

Private Sub Workbook_Open()
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Workbooks.Open Filename:="C:\Workbook2.xls", UpdateLinks:=0, ReadOnly:=True
    Windows("Workbook2.xls").Activate
    ActiveWindow.Close
 
   
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
Subscript out of range means it didn't recognize what you put as the index to the method.  in this case it doesn't think "Workbook2.xls" exists.

Rather than use Windows, why not Workbooks?  Its a better approach, as well.

Try:
dim wkb as workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    set wkb = Workbooks.Open ( Filename:="C:\Workbook2.xls", UpdateLinks:=0, ReadOnly:=True)
    'The workbook "Workbook2.xls" is already active, but activate it anyway, I guess in case the open macro in this workbook activates another workbook?
    wkb.activate
    wkb.close savechanges:=false

Open in new window


Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
Ok. I didn't tell you why you got the error, just what the error meant - that it couldn't find the object, and I gave you "improved" code over what you have.

You cannot activate another workbook using the Windows.Activate method from ThisWorkbook codepage.  This must be done in a public module.  However, I recommend another method in either event - ThisWorkbook or public module which is better practice.

However, using the Workbook.Activate method will work just fine.

The "improved" code I provided will work in the ThisWorkbook codepage.

While I added editorial license in my "improved" code, you only really need these changes:
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Workbooks.Open Filename:="C:\Workbook2.xls", UpdateLinks:=0, ReadOnly:=True
    Workbooks("Workbook2.xls").Activate
    ActiveWorkbook.Close savechanges:=False

Open in new window


Cheers,

Dave

Author

Commented:
Thanks it worked great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial