Solved: Auto Update Links on a Mapped Drive

[mike31z]

I need some help in how to auto update links between two excel files on 2 seperate computers on a LAN no interent access. One one of the computers we enter the scores and on the second computer we run one linked excel file and one power point file all the files are in the same folder.
I shared the folder in explorer and the Mapped the folder to a drive letter X.

could somepoint me in the direction were I can learn about auto updating links.
Computer are win xp and excell is 2000 sp3.


Mike in Wisconsin

[mike31z]

Let me refine my question/request. Win xp office 2000 sp3
on laptop 1 excel file "xls2ppt.xls" is linked to a powerpoint slide show that is running.
on laptop 2 excel file "team09.xls" is the original source for data on "xls2ppt.xls"

How can I create a VBA code that forces laptop 1 to update the links in file "xls2ppt.xls" while working on laptop 2 from within file "team09.xls".

both file are located in the same folder on a mapped drive "X".

The network is a local LAN with no internet access. Laptop is wired to the rourter and laptop 2 is wireless (if that helps).

within the xls2ppt file when I click EDIT|LINKS there is only one entry "X:\Team09.xls" .

Is it possible to create vba code to force the link update from a second computer?

[mike31z]

I found a article in the knowledge base and It would be an good useable work around.

article http://www.vbaexpress.com/kb/getarticle.php?kb_id=162

Title: Schedule a Macro to run after a specific Period of time.

the code is small in size which is good:[VBA]
Option Explicit

Sub ScheduleAProcedure()

'TimeSerial(hours, minutes, seconds)
Application.OnTime Now + TimeSerial(0, 0, 10), "TimedMacro"

End Sub

Sub TimedMacro()

'This should be the macro that runs; we simply show a message box
MsgBox "The code was just executed!", vbInformation

End Sub

[/VBA]
The macro I want it to run is called "links"

[mike31z]

I found the fix to auto renew links. I must give credit to http://www.java2s.com/Code/VBA-Excel...orunitself.htm

The actual code is below, I added some comment within the code to help the weekend power point users like me.

[vba]
Dim mdteScheduledTime As Date
’ The sub below is the start timer
Sub RefreshData()
ThisWorkbook.UpdateLink Name:="C:\YourExcel2007File.xlsx", Type:= xlExcelLinks
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

[/vba]
There are 2 things in the code above you need to change.
1. The name and location of your file in the example its "C:\YourExcel2007File.xlsx"
2. The refresh time interval currently its set at 1 minutes. Break down of the
( 0 = hrs, 1 = min, 0 = sec)

You must use caution if you start a timer you must turn off the timer, BOTH codes are provided.

I used this on excel 2000 and it works for me.


I should also mention that from within Powerpoint to get those links to excell to refresh repeatedly you should use
this PowerPoint add-in from Shyam PILLAI located at


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

The file name is: updatelinks.zip The links are updated on loop of the slide show.

I hope this helps


Mike in Wisconsin

[mdmackillop]

Thanks for the info Mike.