HELP PLEASE

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

 

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

 

[Bob Phillips]

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

 

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

 

[Bob Phillips]

Can you show your code?

 

[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

 

[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

 

[Bob Phillips]

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