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.

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

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

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

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

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

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

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

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

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