Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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" 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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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" wrote: 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" 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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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, "Joel" wrote: 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" wrote: 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" 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. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
bk was suppose to be the workbook object as I said in the comments. Instead
use thisworkbook Thisworkbook.save Application.Quit "Neil Holden" wrote: 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. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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? "Neil Holden" wrote: 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, "Joel" wrote: 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" wrote: 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" 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. |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way using Macros
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. "Neil Holden" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
Macros not appearing in the Tools Macro Macros list | Excel Programming | |||
Suppress the Disable Macros / Enable Macros Dialog | Excel Programming |