asked on

VB close an open workbook from the macro of another workbook

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

dlmille

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

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DavidH7470

ASKER

Thanks it worked great