EXCEL worksheet.updatelink

My VBA project opens 4 seperate XL application objects and between them about 12 huge workbook  objects are openned. Sounds crazy doesn't it.  All of the workbooks are linked to atleast one other workbook.

Any way I cant seem to get the updatelink method to work once the workbook/s are open.

eg

ap(i).Workbooks(ii).UpdateLink ap(i).Workbooks(ii).LinkSources

I have changed the syntax of this statement several times and checked it against the help and i know its correct.

The problem lies  elsewhere ??
 
The links are successfully updated when the workbooks are opened.  
eg.

ap(0).Workbooks.Open sourcedir & "FX 330 (T-1)&(T-1)2.xls", True

I just need to be able to update the links once the sheets are open. The error message i get is

runtime error 1004
Method 'UpdateLink' of object '_Workbook' failed
.

Some thing that gave me a clue was :
when i into the edit>links menu in Excel the update now button is grayed out.  This  button is greyed out for all the links that are to xvl files  that are currently open.

  any ideas ??

 

LVL 2
skhorshidAsked:
Who is Participating?
 
bruintjeCommented:
Hi skhorshid,

Paul Bedford had the following to say about this in another thread

<snippet>
recereated a similar scenario and the error you've mentioned only seems to occur when a workbook does not exist that you are trying to refresh.  If you have one missing then this may be a concern to you, but if you want the macro to refresh every book that it can, then try this in your macro:

Private Sub RefreshLinks()

On Error Goto ErrHandler

Dim strCurrent  as string
Dim strMsg as string

strCurrent = "C:\Book1.XLS"  'Location of first workbook
ActiveWorkbook.UpdateLink Name:=strCurrent, Type:= _
        xlExcelLinks
strCurrent = "C:\Book2.XLS"  '2nd workbook
ActiveWorkbook.UpdateLink Name:=strCurrent, Type:= _
        xlExcelLinks
'Repeat this for all 38 workbooks

If strMsg = "" then
    Msgbox "Completed successfully"
Else
    msgbox strMsg
end if

Exit sub

ErrHandler:

strMsg = strMsg & vbCrlf & strCurrent
Resume Next

End Sub

This code will refresh each link.  If an error occurs it will add the name of the link into a string.  At the end it will show you a list of all the failed links.  This will at least save you some time.

</snippet>

You can now see the failed links and eventually sort out the problem

HTH:O)Bruintje
0
 
leojlCommented:
It does indeed sound crazy.

4 applications and 12 huge workbooks is just too much.

You must segregate this into some logical subsets
and then update links etc. If need be you can test at each
subset if another subset should be considered.

leo
0
 
bruintjeCommented:
any update on this?
0
 
skhorshidAuthor Commented:
Hi, I found what the problem was.

I was trying to use the update link method when the workbook i was linking to was allready open in the same session.  IE you dont need to update links, just call calculate method.  

thanks
0
 
bruintjeCommented:
glad you solved it and thanks for the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.