No it doesn't, the sheet just opens.

"Joel" wrote:

If you open the workbook does it get to the stop statement?

Not sure what the problem. Give more details.

"Neil Holden" wrote:

Hi again Joel, firstly just like to say thanks for all your help.

I have now set the scheldule to run over night, i will be placing this on
the server so I will be on anyway.

I still cant get it to open and automatically save and close.

bk.save
Application.Quit

But it does save and close : (

So close but still no there.

Thanks.


"Joel" wrote:

Don't use close. It will keep the excel application open. Save the workbook
then quit application

Use
bk.save wher bk it the workbook
Application.Quit

"Neil Holden" wrote:

Hi again.. Ok I think i might have this sorted but do you know the code to
save and close the excel once opened?

Thanks.

"Joel" wrote:

Didn't think of that. You could have a workbook open event to run the macro.
The PC that is runing the event must be left on overnight for it to run.
You would need a workbook open event. Place this code in the VBA sheet
Thisworkbook and change the name of the called macro. I placed a stop
command so you cna make sure it is working. When the stop is reached just
press F5 to continue. Remove the stop after it has been tested so it runs
when you are not around

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
stop
call Macro1
End Sub


"Neil Holden" wrote:

I can set up a scheldule tash within control panel to open up the excel sheet
during the night? I can do that no problem, once opened i need it to update
the cells and close automatically? Or am i talking rubbish?

Thanks for all your help Joel.

"Joel" wrote:

To do this in excel means you would have to open the workbook manually and
have it open all the time. Since you probably have outlook open all the time
putting the event into outlook makes more sense.

I don't do this often from outlook and don';t have code available. It would
be better if you get the outlook code form the experts at the outlook
programming website.

From outlook opening the excel object is simply
obj = Getobject(filename:="c:\temp\book1.xls")

The macro language in outlook is very similar to excel.

"Neil Holden" wrote:

I've no idea how to do this in outlook so i'm doing it all in excel.

I'm really lost with this : (

All i need now is code to automatically open an excel sheet at a certain
time and update links and save and close without having to do anything.

Please help genius!! : )

Neil.

"Joel" wrote:

Not sure if you are writing the code in Excel or Outlook VBa

You need to have the code in a subroutine like this

Sub MySub
'enter your code here

end sub



"Neil Holden" wrote:

Hello again, i have created VB Code which should update certain cells and
save and close: The code is below but when I open the excel sheet its having
problems with the first word Set. Please help!

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\Excel Schelduled Tasks\test-pulledthrough.xls")

objExcel.Application.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

"Joel" wrote:

If you need something done on a regular basis I would write the macro in
Outlook and not excel. the code will be very similar but you can't schedule
events in Excel.


You have two choices. One is to write all the code in Outlook. Two is to
write just the event in outlook and then have outlook open a workbook a run a
macro in the opened workbook.

"Neil Holden" wrote:

Morning all, I have an excel sheet which pulls certain information from
another excel on a regular basic and needs updating in order for all the
information to be pulled through.

Ideally, what I would like to achieve is to set a scheduled task for my
excel sheet to open at a certain time and automatically update the
information from the other excel sheet and automatically close.
Can some genius please tell me how I can do this?
Much appreciated.