Synchronizing Two Workbooks

O

OK Bob

I'm not sure of how to word this but I will try

I use Office 2000, and Excel 2000 on my Windows 2000 computer

I have a Workbook (the master workbook) with several tabs (Worksheets)
This Workbook is in a folder, in a folder, on a shared drive

(In other words --- This folder resides within a Folder which has another folder too; i.e. one folder with two folders in it.) One of the 2 folders has the master Workbook, which can be modified by several designated people per Permissions. The other folder has a copy of the Workbook where several people may 'look at' the workbook (read only)

What I'd like to do is find a way that whenever the Master Workbook is updated (e.g. a row of info added, info in a cell is changed, etc) the "Copy" is automatically updated to read like the Master Workbook

Does anyone know if this can be done, and how to do it

Would appreciate any ideas or suggestions

Thanks
OK Bob
 
O

OK Bob

Thanks Dave
I already thought of that, but was looking for a more immediate way. That's why I thought there may have been a way to make the changes from the original workbook appear automatically in the copy.
 
D

Dave Peterson

It sounds like you may not have looked at that link included in the other
response.

If you did look at it, sorry it didn't do what you wanted.
 
O

OK Bob

I'm glad you responded the way you did
I had missed the Link
I have it now and will give it a good hard look
This may work for me
I'll post back once I give it detailed look and try it out
Thanks,
 
O

OK Bob

Dave,

After reading the information at the Link, I believe this would work for me.
However, I think I may have bit off more than I can chew!
I am not up to the level you are, unfortunately.
It sounds like just the thing for me - I think. But I have no idea how to use code etc.
For example - you say "... I put this code behind the ThisWorkbook module:..."
Right away I knew I was 'in the right church but certainly in the wrong pew' !!

Maybe I'll hang on to the informatiion at the Link and see if I can snoop around for more information that can lead me to the general area you are talking about.

Thanks so very much for your help thus far.

I will keep my eye on this thread in case some other ideas pop up.
 
D

Dave Peterson

Open your excel workbook
Hit alt-f11 to get to the VBE (where macros live)
hit ctrl-F4 to see the project explorer

Find your workbook's project.
It should look like: VBAProject (book1.xls)
(Kind of like windows explorer.)

Click on it once and then hit the asterisk on the numeric keypad to expand all
its branches.

You'll see the "ThisWorkbook" under the Microsoft Excel Objects branch.
Right click on it and choose View code.

Past that first macro's code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now + TimeSerial(0, 0, 3), "SaveACopy"
End Sub

Then right click on "VBAProject (book1.xls)"
select Insert, then Module
Paste this in the code window.

Option Explicit
Sub SaveACopy()

Dim myPath As String

myPath = "c:\my documents\excel\test\"

With ThisWorkbook
If .Saved Then
On Error Resume Next
.SaveCopyAs Filename:=myPath & .Name
If Err.Number <> 0 Then
MsgBox "Something bad happened"
Err.Clear
Else
MsgBox "Also saved to: " & myPath & .Name
End If
On Error GoTo 0
End If
End With

End Sub

Don't forget to adjust the path to where you want the backup saved.
 
D

Dave Peterson

past = paste (if you can't type!)
(but I did spell it correctly in the other spot).

I mean copy these next 4 lines from the newsgroup post:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now + TimeSerial(0, 0, 3), "SaveACopy"
End Sub

And past(e) them into that code window.

You can delete the "private sub workbook_open()" and associated "end sub" lines.

====
I like to keep "Option Explicit" in my suggested posted code. It's a directive
to the VBA compiler that tells it to expect every variable that I use to be
Dim'ed somewhere in my code.

It's very easy to type:

Dim myPath As String
myPath = "c:\my documents\excel\test\"

then later in the code refer to myPath as myPat (you've seen my typing
errors!). If I don't have that "option explicit" turned on, then VBA will see
that as an ok variable--and it hasn't been used yet. So it could be treated as
an empty string ""--instead of the real location "c:\....."
 
Top