Opening another workbook and then closing it.
 

Hi there,

I am trying to get my code to open a workbook, then update the links and
then close the workbook again. It is working okay until I get to the line
"Windows("http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls").Activate" in the code.

It is giving me the message Run-time error '9': Subscript out of range

I have added the code below and put the troublesome line in brackets. Extra
info - the % in the filename in the initial bit where I open the file are
because it is on sharepoint and replaces spaces with %

Could anyone help with this?

Private Sub Workbook_Open()
MsgBox "Click on the statement to provide assurance on the IS controls in
place", vbInformation
Workbooks.Open Filename:= _

"http://knet/sites/fapas/risk/InformationSecurity/IS%20Documents/Plan/Statement%20of%20Applicability.xls"
Windows("IS register new one!.xls").Activate
Workbooks("IS Register new one!.xls").Activate
ActiveWorkbook.UpdateLink Name:= _
"http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls" _
, Type:=xlExcelLinks
((((Windows("http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls").Activate))))
ActiveWorkbook.Close
End Sub

Opening another workbook and then closing it.
 

Pasty,
Give yourself a variable to work with

Dim WS as Workbook
Set WS = Workbooks.Open
Filename:="http://knet/sites/fapas/risk/InformationSecurity/IS%20Documents/P
lan/Statement%20of%20Applicability.xls"
With WS
.UpdateLinks...etc
.Close
End with

NickHK

"Pasty" wrote in message
...
Hi there,

I am trying to get my code to open a workbook, then update the links and
then close the workbook again. It is working okay until I get to the line
"Windows("http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls").Activate" in the code.

It is giving me the message Run-time error '9': Subscript out of range

I have added the code below and put the troublesome line in brackets.
Extra
info - the % in the filename in the initial bit where I open the file are
because it is on sharepoint and replaces spaces with %

Could anyone help with this?

Private Sub Workbook_Open()
MsgBox "Click on the statement to provide assurance on the IS controls
in
place", vbInformation
Workbooks.Open Filename:= _


"http://knet/sites/fapas/risk/InformationSecurity/IS%20Documents/Plan/Statem
ent%20of%20Applicability.xls"
Windows("IS register new one!.xls").Activate
Workbooks("IS Register new one!.xls").Activate
ActiveWorkbook.UpdateLink Name:= _
"http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls" _
, Type:=xlExcelLinks
((((Windows("http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls").Activate))))
ActiveWorkbook.Close
End Sub

Opening another workbook and then closing it.
 

Excellent stuff - thanks for your help.

"NickHK" wrote:

Pasty,
Give yourself a variable to work with

Dim WS as Workbook
Set WS = Workbooks.Open
Filename:="http://knet/sites/fapas/risk/InformationSecurity/IS%20Documents/P
lan/Statement%20of%20Applicability.xls"
With WS
.UpdateLinks...etc
.Close
End with

NickHK

"Pasty" wrote in message
...
Hi there,

I am trying to get my code to open a workbook, then update the links and
then close the workbook again. It is working okay until I get to the line
"Windows("http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls").Activate" in the code.

It is giving me the message Run-time error '9': Subscript out of range

I have added the code below and put the troublesome line in brackets.
Extra
info - the % in the filename in the initial bit where I open the file are
because it is on sharepoint and replaces spaces with %

Could anyone help with this?

Private Sub Workbook_Open()
MsgBox "Click on the statement to provide assurance on the IS controls
in
place", vbInformation
Workbooks.Open Filename:= _


"http://knet/sites/fapas/risk/InformationSecurity/IS%20Documents/Plan/Statem
ent%20of%20Applicability.xls"
Windows("IS register new one!.xls").Activate
Workbooks("IS Register new one!.xls").Activate
ActiveWorkbook.UpdateLink Name:= _
"http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls" _
, Type:=xlExcelLinks
((((Windows("http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls").Activate))))
ActiveWorkbook.Close
End Sub