Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

EXCEL  worksheet.updatelink

Posted on 2002-03-04
5
Medium Priority
?
1,917 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 75 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

715 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