Viewing The Same Workbook on Two Computers

[Panda]

Hi All,

Basically i have a workbook containing macros that logs components going in and out of an oven, this spreadsheet is running on a pc in the manufacturing cell. However I want to be able to setup another PC in my office running the same spreadsheet so that I can see live what is being booked in and out.

I cant share the workbook because then the macros do not work properly. Is there a way I can do this? If not, is there some code that will interogate the spreadsheet and effectivly copy and paste the data into another workbook on a regular basis?

Thanks

Phil

[Bob Phillips]

You don't tell us what the problem is, or what you are doing exactly that doesn't work.

[Stargazer]

Here's a crazy idea...

Build two workbooks: Your FrontEnd workbook goes in the Manufacturing Cell and is updated the peeps in there.

When the FrontEnd workbook is updated and saved, it could write a text file containing the information to a shared network location. The advantage of this is that .txt files are easy to read from, take up minimal space (Less than 1kb for a very basic file) and are reliable.

Your BackEnd workbook goes in your office for you to look at. Build a simple timer function that you can set going in it and then all it has to do is on each tick, is scan the contents of your shared network location and then display the file information on your worksheet.

No access conflicts, no sharing violations, but a fair bit of tweaking and developing. Could be worth it though if you really need to monitor this stuff. Let me know if you want me to elaborate or try and help with how to code some of it. We have something somewhere that does things similar to this so may be able to dig something up if it's the way you want to go.

Good luck,

Rob.

[Panda]

Hey Stargazer, thanks for the reply. Do you have some code that will copy a worksheet from one PC into another worksheet set on a network drive somewhere?

And then some code (contained within the display spreadsheet) to copy and paste from this file every minute or so?

[Stargazer]

Not to hand, but I'm bored so give me a few mins and I'll bang something together... If you don't see another reply by 17:15... Check back mid morning.

[Stargazer]

Hiya,

Please find below my version of a quick/easy solution. To all those out there who do VB at a professional level, I apologise and I know I'm a hack. But I get by.

The code below is very simple and it will do a job. I would strongly recommend you take it away, change it, upgrade it, and make it generally more efficient.

Okay... To get this working like I have here. You will need:-

2x .xlsm workbooks
1x Network share that is always available
And change the Excel options to not use compatibility mode since this method is very imprecise and compatibility will break it. Alternatively, use some LastCol and LastRow calculation to grab only the range you require.

For example purposes, my file for the Manufacturing Cell is called ManufacturingFile.xlsm and the Office File is called OfficeFile.

In the code for ManufacturingFile, put the following into the ThisWorkbook module/class

[vba]
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Call CreateCopy

End Sub


Sub CreateCopy()

Dim NetworkLoc As String
NetworkLoc = "R:\MBB LP\RobW\VBAX\" '<< Set this to your own Network Location >>'

Dim OriginalFile As String
Dim CopyOfFile As String

OriginalFile = "ManufacturingFile.xlsm"
CopyOfFile = "ManufacturingFile_NetworkCopy.xlsm"

Workbooks.Add
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (NetworkLoc & CopyOfFile), FileFormat:=52
Application.DisplayAlerts = True

Dim WbkOrig As Worksheet
Set WbkOrig = Workbooks(OriginalFile).Sheets("ManuCell")

WbkOrig.Copy Before:=Workbooks(CopyOfFile).Sheets(1)

Workbooks(CopyOfFile).Close True

End Sub

[/vba]
Before you're done with this, rename 'Sheet1' to 'ManuCell'. Now we're done here.

Now, in the OfficeFile workbook, You want two tabs. Name 'Sheet1' 'ManuCell' and Sheet2 'Timer On-Off'

In Sheet 'Timer On-Off' create two ActiveX command buttons. Name one of them cmdStart and the other cmdStop. Leave cmdStart Enabled but set the default property for cmdStop to be Disabled. Then in the code Module/Class for Sheet2 paste the following...

[vba]
Sub cmdStart_Click()

cmdStart.Enabled = False
Call StartTimer
cmdStop.Enabled = True

End Sub

Sub cmdStop_Click()

cmdStop.Enabled = False
Call StopTimer
cmdStart.Enabled = True

End Sub
[/vba]
Then, still in the office file, create a new module. I called mine TimerModule but I don't think the name is particularly important.

What is important that you put the following code into the new module you just created...

[vba]
Dim OfficeFile As String
Dim CopyOfFile As String
Dim NetworkLoc As String

Dim NextTick
Dim GraceTime

Sub Tick()

NextTick = Now + TimeValue("00:00:01")

Sheet2.Range("A1").Value = NextTick

If Dir(NetworkLoc & CopyOfFile) <> "" Then 'Exists if True
GraceTime = GraceTime + 1
If GraceTime = 3 Then
Workbooks.Open (NetworkLoc & CopyOfFile)

Dim WbkOffice As Worksheet
Dim WbkCopy As Worksheet
Set WbkOffice = Workbooks(OfficeFile).Sheets("ManuCell")
Set WbkCopy = Workbooks(CopyOfFile).Sheets("ManuCell")

WbkOffice.Name = "Delete"

WbkCopy.Copy Before:=Workbooks(OfficeFile).Sheets("Delete")

Workbooks(CopyOfFile).Close False

Application.DisplayAlerts = False
Sheets("Delete").Delete
Application.DisplayAlerts = True

ThisWorkbook.Save

Kill (NetworkLoc & CopyOfFile)

GraceTime = 0

End If
End If

Application.OnTime NextTick, "Tick"


End Sub

Sub StopTimer()

On Error Resume Next
Application.OnTime NextTick, "Tick", , False

End Sub

Sub StartTimer()

NetworkLoc = "R:\MBB LP\RobW\VBAX\" '<< Set this to your own Network Location >>'
CopyOfFile = "ManufacturingFile_NetworkCopy.xlsm"
OfficeFile = "OfficeFile.xlsm"

Call Tick

End Sub
[/vba]
So now, when the manufacturing file saves, it opens a new workbook, saves it on the network and then copies the main worksheet into the new workbook. It then saves the new workbook and carries on. This happens very quickly.

the office file, when the timer is running, checks the network location each second and when it find the file, it counts to 3 and then opens the file.

the office file then tags the current ManuCell sheet instelf for deletion, pulls in the one that was just saved by the peeps in the Cell, and then closes and deletes the copy. it then also deletes the old Kiln/oven manifest it pulled in then resumes looking for the netowrk file again.

Is this any good?

Rob.

[Stargazer]

Hmm... Have just realised that this is one of those Utopia'esque forums that allow attachments... To save needign to build the workbooks like I described above, you could download these that I have attached and just do the other bits.

it also makes it quicker and easier for you to test it on your network this way since all you need to do is modify a location before transferring the code into your live workbooks.

Rob.

ProjectForPanda.zip

[Panda]

Thanks Stargazer, this looks awesome and it sounds like it does exactley what I wanted it to do. However my workplace are still using office 2003 so we still have excel 2003 will this work still? (Sorry I am a bit of a new trying to teach myself VB)

I cant thank you enough for taking the time to help me out =

[Stargazer]

As for helping... Nay bother! It's rare I have the insight to help other people so if I can, I'm happy to. I'm also soul crushingly bored so little distractions like this actually help me out.

As for the Office 2003 question... I shall defer that to someone else's wisdom. I've only ever coded in Office 2007 and the xlsm format so I'm not sure how that plays out in earlier versions.

Off the top of my head, I assume then that where I specify 'FileFormat:= 52' in the ManufacturingFile won't be a valid statement in 03, but I have no idea what it would need to be changed to.

Hope you're able to get it sorted.

Rob.

[justdriving]

Rob, you are excellent. I doubt, you might be close friend of my favorite expert, Bob. You just showed you are too one of those Genius experts in this forum.