Synchronization Best Practice

[Ron Rosenfeld]

We have two homes and I have a computer at both homes. I had them set up
fairly identically.

(Among others) I have several Excel files that I have been keeping synchronized
on two different machines (in two different locations) by carrying the data
files back and forth. This has worked reasonably well to date.

Given the amount of data, and the broadband speed available to me,
synchronization over the Internet would not be practical.

Since the Data storage folder structure was the same, it was trivial to run the
worksheets on either computer.

However, I have recently had to upgrade one of the computers to Windows 7. So
the data storage folder structure is different. For example, a file at:

C:\Documents and Settings\Ron\My Documents\DATA\EHC\Investment Committee

is now stored, on one machine only, at something like

C:\Users\Ron\......

(Non-Microsoft) Add-ins are also stored in different places on the two
machines.

The consequences are that the Add-ins don't load; and the links within some of
the worksheets don't point to the correct location, depending on where the file
was last saved.

I am trying to decide the best way to deal with this.

My first thought, with regard to the links within the worksheets, was to

1. Save the "Computer Saved On" in the document properties section of the
workbook.

2. If the "Computer Saved On" doesn't match the current computer, then update
the links within the workbook on the File Open event.

My second thought would be to somehow restructure the data folders so that they
are "NAME'd" the same on the two machines. That would be tedious, but doable.

I'm not sure what to do with the personal add-ins, and other add-ins (like
morefunc and others) that vary in location between the two machines. One
thought would be to move them all to a commonly named location, such as
C:\PersonalAddIns\. But I would be open to other thoughts.

Another possibility would be to test and change them from personal.xlsm (which
I do not ordinarily use).

In any event, I'm sure others have dealt with this issue in the past, and I
would be grateful for suggestions.

Thanks.
--ron

 

[Peter T]

One way would be to look at links when the wb opens, compare to a lookup
table of "like-dud" and good and change if/as required. The lookup table
(see code) would be reveresed in the two systems.

This is quickly thrown together (ie lightly tested), could go in an addin or
say Personal on both systems.

' normal module
Dim mClsApp As clsAppEvents

Sub SetEvents()
'call in thisworkbook's open event

Set mClsApp = New clsAppEvents
Set mClsApp.xlApp = Application
End Sub

Sub test()
SetEvents
mClsApp.UpdateLinks ActiveWorkbook

End Sub


'' clsAppEvents
Option Explicit
Public WithEvents xlApp As Excel.Application

Private Sub xlApp_WorkbookOpen(ByVal wb As Workbook)
UpdateLinks wb
End Sub

Public Sub UpdateLinks(wb As Workbook)
Dim pos As Long, i As Long
Dim sNewLink As String
Dim arrLookUp
Dim arrLinks, vLink

' somthing unique to a potential dud link in col-a of the lookup,
' eg part of path, workbook-name
' the good path link in col-b of the lookup

' note as written asssumes the linked book name will be common to
' to both dud and good links, but adapt as required
' double check the separator is right, maybe include trailing "\"
' in the table and remove below

arrLinks = wb.LinkSources(xlExcelLinks)
If IsEmpty(arrLinks) Then Exit Sub

arrLookUp = ThisWorkbook.Worksheets(1).Range("A1:B2") ' << lookup
Application.DisplayAlerts = False
On Error GoTo errH

For Each vLink In arrLinks

For i = 1 To UBound(arrLookUp)
If InStr(1, vLink, arrLookUp(i, 1), vbTextCompare) _
And Len(arrLookUp(i, 1)) Then

pos = InStrRev(vLink, "\")
sNewLink = Mid$(vLink, pos + 1, Len(vLink) - pos)
sNewLink = arrLookUp(i, 2) & "\" & sNewLink

wb.ChangeLink vLink, sNewLink
End If
Next
Next
done:

Application.DisplayAlerts = True
Exit Sub
errH:
' Stop: Resume ' only for testing
Resume done
End Sub


Wouldn't like to say this is "bset practice", but when needs must!

Regards,
Peter T

 

[Ron Rosenfeld]

One way would be to look at links when the wb opens, compare to a lookup
table of "like-dud" and good and change if/as required. The lookup table
(see code) would be reveresed in the two systems.

This is quickly thrown together (ie lightly tested), could go in an addin or
say Personal on both systems.

' normal module
Dim mClsApp As clsAppEvents

Sub SetEvents()
'call in thisworkbook's open event

Set mClsApp = New clsAppEvents
Set mClsApp.xlApp = Application
End Sub

Sub test()
SetEvents
mClsApp.UpdateLinks ActiveWorkbook

End Sub


'' clsAppEvents
Option Explicit
Public WithEvents xlApp As Excel.Application

Private Sub xlApp_WorkbookOpen(ByVal wb As Workbook)
UpdateLinks wb
End Sub

Public Sub UpdateLinks(wb As Workbook)
Dim pos As Long, i As Long
Dim sNewLink As String
Dim arrLookUp
Dim arrLinks, vLink

' somthing unique to a potential dud link in col-a of the lookup,
' eg part of path, workbook-name
' the good path link in col-b of the lookup

' note as written asssumes the linked book name will be common to
' to both dud and good links, but adapt as required
' double check the separator is right, maybe include trailing "\"
' in the table and remove below

arrLinks = wb.LinkSources(xlExcelLinks)
If IsEmpty(arrLinks) Then Exit Sub

arrLookUp = ThisWorkbook.Worksheets(1).Range("A1:B2") ' << lookup
Application.DisplayAlerts = False
On Error GoTo errH

For Each vLink In arrLinks

For i = 1 To UBound(arrLookUp)
If InStr(1, vLink, arrLookUp(i, 1), vbTextCompare) _
And Len(arrLookUp(i, 1)) Then

pos = InStrRev(vLink, "\")
sNewLink = Mid$(vLink, pos + 1, Len(vLink) - pos)
sNewLink = arrLookUp(i, 2) & "\" & sNewLink

wb.ChangeLink vLink, sNewLink
End If
Next
Next
done:

Application.DisplayAlerts = True
Exit Sub
errH:
' Stop: Resume ' only for testing
Resume done
End Sub


Wouldn't like to say this is "bset practice", but when needs must!

Regards,
Peter T

Thanks, Peter. Testing for a bad link, and correcting it, certainly seems like
a viable option.
--ron

 

[Mike Middleton]

Ron -

Until early this year, I was synchronizing among three work location
desktops plus a travel laptop. I avoided the default Windows folder
structure, and I used a single folder in the root directory C:\mrm (my
initials) of each computer for my work files. Of course, that single folder
had many subfolders. Then I used Second Copy synchronization software on
each computer to synch C:\mrm, and I carried a USB drive from location to
location. This arrangement worked really well for me. A side effect was that
I had multiple recent backups in several locations in case a drive crashed.

Now I have two work locations, and I'm using a single laptop that docks to
dual monitors and a keyboard at each location. I keep only the laptop up to
date, and I install new software only on the laptop instead of on several
desktops. For backups I'm now using Replica external hard drive and Mozy
online. The laptop has a solid state drive, and I'm amazed at the difference
in noise level, mostly due to the absence of the desktop power supply fan.
This arrangement is working really, really, really well for me.

- Mike
http://www.MikeMiddleton.com