The below gives error 9: subscript out of range (when error handling is not
used). I thought it might be to do with workbooks(path), but not sure. It is
located in the ThisWorkbook object.



Private Sub workbook_open()

'sub to force intermediate xlwb to update its links without opening it

'Dim msg As String

On Error GoTo ms

with Workbooks("G:\filepath\filepath\file.xls")

..UpdateLink Name:=.LinkSources

end with

Exit Sub

ms:
msg = "links may have not updated properly" & vbNewLine
msg = msg & "open relevant file"
MsgBox msg


End Sub

From the help file, the Workbooks property:-

Returns a Workbooks collection that represents all the open workbooks.

Try changing your line to:-

With Workbooks.Open("G:\filepath\filepath\file.xls")

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"PBcorn" wrote:

The below gives error 9: subscript out of range (when error handling is not
used). I thought it might be to do with workbooks(path), but not sure. It is
located in the ThisWorkbook object.



Private Sub workbook_open()

'sub to force intermediate xlwb to update its links without opening it

'Dim msg As String

On Error GoTo ms

with Workbooks("G:\filepath\filepath\file.xls")

.UpdateLink Name:=.LinkSources

end with

Exit Sub

ms:
msg = "links may have not updated properly" & vbNewLine
msg = msg & "open relevant file"
MsgBox msg


End Sub

excellent - that works. If you have time - is there a way of stopping the
update links dialog box from appearing when the file is opened?

Thanks

"Alan Moseley" wrote:

From the help file, the Workbooks property:-

Returns a Workbooks collection that represents all the open workbooks.

Try changing your line to:-

With Workbooks.Open("G:\filepath\filepath\file.xls")

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"PBcorn" wrote:

The below gives error 9: subscript out of range (when error handling is not
used). I thought it might be to do with workbooks(path), but not sure. It is
located in the ThisWorkbook object.



Private Sub workbook_open()

'sub to force intermediate xlwb to update its links without opening it

'Dim msg As String

On Error GoTo ms

with Workbooks("G:\filepath\filepath\file.xls")

.UpdateLink Name:=.LinkSources

end with

Exit Sub

ms:
msg = "links may have not updated properly" & vbNewLine
msg = msg & "open relevant file"
MsgBox msg


End Sub

In the source workbook go to Edit, Links. Click on the Startup Prompt button
and change the setting to 'Dont display the alert and update the links'.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"PBcorn" wrote:

excellent - that works. If you have time - is there a way of stopping the
update links dialog box from appearing when the file is opened?

Thanks

"Alan Moseley" wrote:

From the help file, the Workbooks property:-

Returns a Workbooks collection that represents all the open workbooks.

Try changing your line to:-

With Workbooks.Open("G:\filepath\filepath\file.xls")

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"PBcorn" wrote:

The below gives error 9: subscript out of range (when error handling is not
used). I thought it might be to do with workbooks(path), but not sure. It is
located in the ThisWorkbook object.



Private Sub workbook_open()

'sub to force intermediate xlwb to update its links without opening it

'Dim msg As String

On Error GoTo ms

with Workbooks("G:\filepath\filepath\file.xls")

.UpdateLink Name:=.LinkSources

end with

Exit Sub

ms:
msg = "links may have not updated properly" & vbNewLine
msg = msg & "open relevant file"
MsgBox msg


End Sub