Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Opening & Odd Closing | New Users to Excel | |||
Closing a workbook upon opening another | New Users to Excel | |||
Opening/Closing a lot of groupings via VBA | Excel Programming | |||
Opening and closing workbook with VBA... | Excel Programming | |||
closing excel after closing a workbook | Excel Programming |