skhorshid
asked on
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).Update
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 ??
Any way I cant seem to get the updatelink method to work once the workbook/s are open.
eg
ap(i).Workbooks(ii).Update
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 ??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
any update on this?
ASKER
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
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
glad you solved it and thanks for the points
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