Updating Linked Objects

[dragotech]

Hello,

I would imagine this is fairly simple, but have had no luck unfortunately.

I have two excel files saved on the network. Test1.xls and Test2.xls. Test1 is the source and test2 is the destination.

When i update a cell in test1, this cell is referenced in test2 - so anybody who opens test2 will see the data i changed in test1.

Here's my problem. I need a way to manually update the cell in test2 without having to close and reopen Excel.

Only opening and closing Excel will ask me if i wish to update the data.

How do i force manual update?

Below is the link in test2 using Excel 2003.

='\\servername\share\[test1.xls]Sheet1'!$A$1


Thanks a million in advance!

 

[dragotech]

Bump?

 

[RoryA]

Do you mean that someone has test2 open, you change test1 and you need their copy of test2 to update?

 

[dragotech]

Yeah, that's what I was trying to explain sorry about.

I need a way for them to manually update it without having to close and open Excel. Is it possible to put a command button in the sheet to do this?

 

[RoryA]

You could run a macro like this:

Sub UpdateFileLinks()
   Dim arrLinks, i As Long
   arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
   If Not IsEmpty(arrLinks) Then
      For i = LBound(arrLinks) To UBound(arrLinks)
        ActiveWorkbook.UpdateLink arrLinks(i)
      Next i
   End If
End Sub

or manually use the Edit-Links, Update Values dialog.

 

[dragotech]

That worked. You're AWESOME! Thank you soo much.

 

[mike31z]

rorya, Sorry for the late but in but I have the same problem.

If I put your code in a module for the xls file that I need to update automatically will that work or do I need to manually run that code.?

I am new to this but have some excel experience.


mike in wisconsin, USA

 

[RoryA]

You have to run the code. How and when is up to you!

 

[mike31z]

Repeadely Updating Linked Objects

Thanks for replying RORYA. My request is directly related I need the excel links to update every 10 minutes automatically not just once by running a macro/VBA code.


Mike in wisconsin

 

[mike31z]

Solved: Repeatly renewing Linked Objects

I have figured out how to run a slide show and have the links repeatly update and have the associated excel file repeatly update.

1St of 3 Problem’s: Having a PowerPoint slideshow update links during (real time within 3 to 5 Minutes) the slide show without having to stop the slide show. In this example all the links are to a intermediate excel file located within the same folder and the spreadsheet is opened.

Solution: down load and install this PowerPoint add-in from Shyam PILLAI located at

Web Site: http://skp.mvps.org/updtlinks.htm

File Name is : updatelinks.zip

Instructions for add-in are located on the above web site. Once activated the slides update at the repeat of slide 1. That is why you have your source data in the same folder.

2nd of 3 Problems: Repeatedly renewing the Links to the main excel files located elsewhere on you LAN. In order to update you links you must stop the slide show go into excel and update the links under EDIT | LINKS | Update Now. Or by a Macro/Vba code you created. Everyone viewing the slide show can see this process.

Solution: Create a code that will allow you the option to change the frequency that the excel file automatically and repeatedly updates based on the time frame you set ( Hours, Minutes, or Seconds)

Word of caution: If you chose to use this code and turn on the internal timer you also have to turn off the timer.

Word of caution: If you chose to use this code and turn on the internal timer you also have to turn off the timer.

Source of the code is:
http://www.java2s.com/Code/VBA-Exce...naregularbasisyoucanmakethemacrorunitself.htm


Details of how the code works and words of caution can be found in the link below.

http://vbadud.blogspot.com/2007/08/automatically-event-repeat-in-excel-vba.html


The code below is to be placed on a Module in the intermediate excel file that you have in the same folder with you PowerPoint file.

I have entered some of my comments into the code to help the Weekend Power Point users follow how to adapt the code to their situation.

Copy Below:

Dim mdteScheduledTime As Date
’ The sub below is the start timer
Sub RefreshData()
' The line below is were you enter the location of were your getting
‘ your original data from Go to EDIT | LINKS look in that window and you
‘ see exactly what you have to enter.
ThisWorkbook.UpdateLink Name:="C:\YourExcel2007File.xlsx", Type:= xlExcelLinks

‘ In the line below the numbers in ( 0 = Hr, 1 = Min, 0 = Sec)
mdteScheduledTime = Now + TimeSerial(0, 1, 0)
Application.OnTime mdteScheduledTime, "RefreshData"
End Sub


‘ The sub below is the Stop Timer
Sub StopRefresh()
Application.OnTime mdteScheduledTime, "RefreshData",, False
End Sub

End Copy.


3Rd Problem : Establishing the internal network thru your and keeping everything working. Sharing folder is a problem in XP because you have to re Share on every reboot from with Windows Explorer. After you have opened all you excel files you must go to Edit| Links | Change Source to revalidate that the links are present and pointing to the correct file in you “My Network Places”


Mike in Wisconsin.