Best way using Macros

[Neil Holden]

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.

 

[Joel]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

Not sure what the problem. Give more details.

 

[Neil Holden]

It opens but nothing happened? When i go to close it manually its asking to
save? So i'm guessing the save or close isn't working.

Neil.

 

[Joel]

If the open event is working you should of gotten to the STOP statement.

Make sure you put the workbook open code into THISWORKBOOK VBA sheet. Open
the View - Project Explorer then double click on Thisworkbook.

What security setting do you have your Excel Applicattion set to. You would
have to have the setting to low security for the macro to run without
intervention? Ihaven't used Schedule Task before. I'm wondering if it runs
if the macro seetting is set to medium? I always set my security setting to
medium.

 

[Neil Holden]

Hi Joel, I have set my security to low, it was on medium.

I have gone into This Workbook and here is my code:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

Stop

Call MySub

End Sub

Sub MySub()

bk.Save
Application.Quit

End Sub

Now I am getting an object required and also the bk.save keeps hightlighting
in yellow.

Thanks,

 

[Neil Holden]

No it doesn't, the sheet just opens.

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

Not sure what the problem. Give more details.

 

[Joel]

bk was suppose to be the workbook object as I said in the comments. Instead
use thisworkbook

Thisworkbook.save
Application.Quit

 

[Neil Holden]

Hello again, it seems to be running fine now Joel.

Thanks very much for you help. I have said you were helpful, if there's
anything you need me to do let me know.

This is just a minor issue but when excel closes its only closes the inside
window and not all of excel so i'm left with a blank window of excel? In
time this will get fustrating.

Any ideas?

 

[LOFE]

Use
Sub Auto_Open()

Whenever you open the workbook, the macro will run automatically. If you
can schedule a job then this will get it working for you.