HELP PLEASE

  • Thread starter Thread starter Neil Holden
  • Start date Start date
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.
 
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.
 
Add some application event code to close that other workbook after a period
of inactivity perhaps.
 
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.
 
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
 
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
 
So your workbook open code save the workbook and quits Excel? What is the
point of that?
 
Back
Top