Solved

EXCEL  worksheet.updatelink

Posted on 2002-03-04
5
1,832 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…

735 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