Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit Links - Combo Box Problem | Excel Worksheet Functions | |||
Why does my Excel workbook take so long to update links? | Excel Discussion (Misc queries) | |||
Auto Update Links | Excel Worksheet Functions | |||
Links Problem | Excel Worksheet Functions | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions |