Solved

EXCEL  worksheet.updatelink

Posted on 2002-03-04
5
1,821 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
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Title # Comments Views Activity
Visual Studio 2005 text editor 10 44
How to make an ADE file by code? 11 94
Create Files based on Cell Values in a Range in Excel 12 43
Send outlook email from VBS Script 2 42
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

856 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