VBA update links code

  • Posted:
  • Proposals: 9
  • Remote
  • #1173386
  • Expired
Tejas T.
Pulkit G.Brian G.Ubair U.Alejandro C. + 4 others have already sent a proposal.
  • 0


Experience Level: Intermediate
General information for the business: Need VBA code to update links from other workbooks open on other computers from a shared drive
Kind of development: Customization of existing program
Description of every module: Workbook 1 has information in it and auto saves every 1.5 mins and is always running on computer 1

workbook2 needs some information from workbook 1 and needs to update from workbook 1 every 1.5 mins, workbook 2 is on a computer 2 also
Description of requirements/functionality: This is the current code

Sub UpdateIt()
''This stops any messages popping up on the screen
Application.DisplayAlerts = False

''This updates all the links in the workbook
ActiveWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources, Type:=xlExcelLinks

''This allows Excel to show any other messages again
Application.DisplayAlerts = True

''The time feature is used again and after 5 minutes the code will call itself and start over
Application.OnTime Now + TimeValue("00:01:30"), "UpdateIt"
End Sub

The code runs fine if the workbook where the information is coming from is closed.
However when the workbook is open (even if on the same computer), i get a debugging error

When the updateIt sub is run I get a run-time error `1004`
Method`UpdateLink` of object`_Workbook`failed

The code highlighted when the error happens is

ActiveWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources, Type:=xlExcelLinks

What i want to happen is the links in workbook 2 to get updated regardless if anyone else has workbook 1 open anywhere else
Extra notes:

New Proposal

Create an account now and send a proposal now to get this project.

Sign up

Clarification Board Ask a Question

  • Rodrigo B.

    What is the path of the file on the network? Usually it starts "\\" if stored on a local computer

  • JM R.


    I've tried your code and i dont have this issue when the origin file is opened, only when is being edited in the same machine while using it, maybe want the macro to check the status before updating?

    Kind regards!