Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
andyp161
 
Posts: n/a
Default Update Links problem


I have a spreadsheet that is linked to another spreadsheet using the
following:

=IF(ISBLANK('http://uknetfs/BenefitPlanReview/[Benefit_Plan_Review_Log.csv]Benefit_Plan_Review_Log'!$N2),"",'http://uknetfs/BenefitPlanReview/[Benefit_Plan_Review_Log.csv]Benefit_Plan_Review_Log'!$N2)

Whenever I open the spreadsheet, excel asks whether I want to update
links - if I press yes, a warning pops up to say that 'This workbook
contains one or more links that cannot be updated'.

Ideally what I would like is for the spreadsheet to automatically
update itself whenever it is opened without any manual intervention.

Hope you can help.

Kind regards


--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=400739

  #2   Report Post  
pdberger
 
Posts: n/a
Default

Andy --
You have to write a small program, using a language Office provides for the
purpose called 'VBA'. I solved the same problem, so I'm copying my code
below. In order for this to work, the two workbooks must be in the same
folder. I may be leaving out a step somewhere, but you should do the
following:

1) Open the workbook that will reference the other, source document.
2) Press Alt-F11. This step starts the VBA mechanisms.
3) On the left side of the screen, you'll see a list of the pages in your
workbook and, at the bottom, 'This Workbook'. Double-click on 'This
Workbook'.
4) At the top of the window, you'll see drop-down box with the word
'(General)' in it. Click the drop-down box and change it to 'Workbook'.
When you do that, the following text will appear in the main window, which
has been blank until now:

Private Sub Workbook_Open()

End Sub
5) Everything you type now needs to be in between those two lines. Put the
cursor between them and type the following:

Dim File_Path As String
Dim File_Name As String

File_Path = ThisWorkbook.Path
File_Name = File_Path & "\" & "YourFileName.xls"
Workbooks.Open Filename:=File_Name, UpdateLinks:=3

Again, it all has to be between the 'Private Sub' line and the 'End Sub'
line. Make sure your file name.xls is inside the quotations.

Exit out of VBA, exit out of the workbook saving as you go. Open the
workbook again and see how it works.

Good luck. hth
"andyp161" wrote:


I have a spreadsheet that is linked to another spreadsheet using the
following:

=IF(ISBLANK('http://uknetfs/BenefitPlanReview/[Benefit_Plan_Review_Log.csv]Benefit_Plan_Review_Log'!$N2),"",'http://uknetfs/BenefitPlanReview/[Benefit_Plan_Review_Log.csv]Benefit_Plan_Review_Log'!$N2)

Whenever I open the spreadsheet, excel asks whether I want to update
links - if I press yes, a warning pops up to say that 'This workbook
contains one or more links that cannot be updated'.

Ideally what I would like is for the spreadsheet to automatically
update itself whenever it is opened without any manual intervention.

Hope you can help.

Kind regards


--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=400739


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit Links - Combo Box Problem Sarah Excel Worksheet Functions 0 July 27th 05 11:44 PM
Why does my Excel workbook take so long to update links? Bannor Excel Discussion (Misc queries) 0 July 26th 05 11:47 AM
Auto Update Links [email protected] Excel Worksheet Functions 0 March 31st 05 08:37 AM
Links Problem DSE Excel Worksheet Functions 2 March 25th 05 02:53 AM
excel links update not working in auto, calculations in auto Mikey Boy Excel Worksheet Functions 0 December 8th 04 12:53 AM


All times are GMT +1. The time now is 11:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"