HELP PLEASE

N

Neil Holden

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.
 
J

Joel

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.
 
B

Bob Phillips

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

Neil Holden

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.
 
J

john

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
 
N

Neil Holden

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
 
B

Bob Phillips

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

Simon Lloyd

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"), procedure:="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

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

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

--
__________________________________
HTH

Bob
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
 
Top