Please help, i have an excel sheet which is full of data, I have set up a new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel sheet and I
need the data from his sheet, I have set up a scheldule to automatically open
my sheet during the night and close automatically, but it stays open in the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would be
greatly appreciated.

Neil.

maybe it is best to make a copy of the workbook and work from the copy rather
than the original. Then you won't have the problem of sharing a workbook.

"Neil Holden" wrote:

Please help, i have an excel sheet which is full of data, I have set up a new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel sheet and I
need the data from his sheet, I have set up a scheldule to automatically open
my sheet during the night and close automatically, but it stays open in the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would be
greatly appreciated.

Neil.

Add some application event code to close that other workbook after a period
of inactivity perhaps.

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
Please help, i have an excel sheet which is full of data, I have set up a
new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel sheet and
I
need the data from his sheet, I have set up a scheldule to automatically
open
my sheet during the night and close automatically, but it stays open in
the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would be
greatly appreciated.

Neil.

hi. thanks for your answers, I can't make a copy of it because i need up to
date information from it on a daily basis so i can report from Crystal.

Not sure if some application even code would work as I already have code to
close the excel once updated but it stays open.

"Bob Phillips" wrote:

Add some application event code to close that other workbook after a period
of inactivity perhaps.

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
Please help, i have an excel sheet which is full of data, I have set up a
new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel sheet and
I
need the data from his sheet, I have set up a scheldule to automatically
open
my sheet during the night and close automatically, but it stays open in
the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would be
greatly appreciated.

Neil.

Can you show your code?

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
hi. thanks for your answers, I can't make a copy of it because i need up
to
date information from it on a daily basis so i can report from Crystal.

Not sure if some application even code would work as I already have code
to
close the excel once updated but it stays open.

"Bob Phillips" wrote:

Add some application event code to close that other workbook after a
period
of inactivity perhaps.

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
Please help, i have an excel sheet which is full of data, I have set up
a
new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel sheet
and
I
need the data from his sheet, I have set up a scheldule to
automatically
open
my sheet during the night and close automatically, but it stays open in
the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would be
greatly appreciated.

Neil.

neil,
rather than opening the workbook, try linking to it instead using formula.
Something like following may be of some help to you:

Sub GetData()
Dim mydata As String
'workbook location
'& range to copy
'change as required
mydata = "='C:\[MybookName.xls]Sheet1'!$A$1:$F$12"

'link to worksheet
With ThisWorkbook.Worksheets(1).Range("A1:F12")
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub
--
jb


"Neil Holden" wrote:

Please help, i have an excel sheet which is full of data, I have set up a new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel sheet and I
need the data from his sheet, I have set up a scheldule to automatically open
my sheet during the night and close automatically, but it stays open in the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would be
greatly appreciated.

Neil.

My code is very simple, simply open the excel sheet update and close.

Private Sub Workbook_Open()


Call MySub

End Sub

Sub MySub()

ActiveWorkbook.Save
Application.Quit


End Sub


"Bob Phillips" wrote:

Can you show your code?

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
hi. thanks for your answers, I can't make a copy of it because i need up
to
date information from it on a daily basis so i can report from Crystal.

Not sure if some application even code would work as I already have code
to
close the excel once updated but it stays open.

"Bob Phillips" wrote:

Add some application event code to close that other workbook after a
period
of inactivity perhaps.

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
Please help, i have an excel sheet which is full of data, I have set up
a
new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel sheet
and
I
need the data from his sheet, I have set up a scheldule to
automatically
open
my sheet during the night and close automatically, but it stays open in
the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would be
greatly appreciated.

Neil.

So your workbook open code save the workbook and quits Excel? What is the
point of that?

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
My code is very simple, simply open the excel sheet update and close.

Private Sub Workbook_Open()


Call MySub

End Sub

Sub MySub()

ActiveWorkbook.Save
Application.Quit


End Sub


"Bob Phillips" wrote:

Can you show your code?

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
hi. thanks for your answers, I can't make a copy of it because i need
up
to
date information from it on a daily basis so i can report from Crystal.

Not sure if some application even code would work as I already have
code
to
close the excel once updated but it stays open.

"Bob Phillips" wrote:

Add some application event code to close that other workbook after a
period
of inactivity perhaps.

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
Please help, i have an excel sheet which is full of data, I have set
up
a
new
excel workbook which pulls information from the other excel sheet.

The problem i have is this user is constantly in the main excel
sheet
and
I
need the data from his sheet, I have set up a scheldule to
automatically
open
my sheet during the night and close automatically, but it stays open
in
the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it would
be
greatly appreciated.

Neil.

If you want to create a shutdown on no activity give this a try, all the
code goe's in the Thisworkbook module, adjust the time to suit:

Code:
--------------------
Private Changed As Boolean
Private Sub Workbook_Open()
'start with the workbook showing unchanged
Changed = False
' create our shutdown timer
Application.OnTime Now + TimeValue("00:00:15"), procedu="ThisWorkbook.Auto_Close"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
'if something is changed then restart the timer
Changed = True
End Sub
Private Sub Auto_Close()
'if no changes detected then save and close
If Changed = False Then
ThisWorkbook.Close SaveChanges:=True
End If
Changed = False
'cancel the timer
Call Application.OnTime(Now + TimeValue("00:00:15"), "ThisWorkbook.Auto_Close")
End Sub

--------------------


Bob Phillips;500351 Wrote:
So your workbook open code save the workbook and quits Excel? What is
the
point of that?

--
__________________________________
HTH

Bob

"Neil Holden" wrote in message
...
My code is very simple, simply open the excel sheet update and close.

Private Sub Workbook_Open()


Call MySub

End Sub

Sub MySub()

ActiveWorkbook.Save
Application.Quit


End Sub


"Bob Phillips" wrote:

Can you show your code?

--
__________________________________
HTH

Bob

"Neil Holden" wrote in
message
...
hi. thanks for your answers, I can't make a copy of it because i
need
up
to
date information from it on a daily basis so i can report from
Crystal.

Not sure if some application even code would work as I already
have
code
to
close the excel once updated but it stays open.

"Bob Phillips" wrote:

Add some application event code to close that other workbook
after a
period
of inactivity perhaps.

--
__________________________________
HTH

Bob

"Neil Holden" wrote in
message
...
Please help, i have an excel sheet which is full of data, I
have set
up
a
new
excel workbook which pulls information from the other excel
sheet.

The problem i have is this user is constantly in the main excel
sheet
and
I
need the data from his sheet, I have set up a scheldule to
automatically
open
my sheet during the night and close automatically, but it stays
open
in
the
open files and this basically doesnt work.

If you know a better way of doing this please let me know it
would
be
greatly appreciated.

Neil.








--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=137437