why won't this work?

  • Thread starter Thread starter PBcorn
  • Start date Start date
P

PBcorn

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")
 
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
 
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'.
 
Back
Top