Solved

EXCEL  worksheet.updatelink

Posted on 2002-03-04
5
1,842 Views
Last Modified: 2007-11-27
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 ??

 

0
Comment
Question by:skhorshid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 3

Expert Comment

by:leojl
ID: 6840942
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
 
LVL 44

Accepted Solution

by:
bruintje earned 25 total points
ID: 6870688
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6891280
any update on this?
0
 
LVL 2

Author Comment

by:skhorshid
ID: 6893477
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6893505
glad you solved it and thanks for the points
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question