Is it a way to schedule an excel spreadsheet to update automatical

Hi all,
I'd like to have my spreadsheet to update automatically (don't know if
that's possible).
In order to do so, it needs to be able to do the following:

1) Is it a way to pass it arguments when lauching Excel, e.g. file name,
list of parameter such as date, mode, ..etc? Currently, the update is
triggered by clicking a commandbtn, if there is a way to pass in arguments
maybe I can get that to update automatically at a scheduled time (using a
batch file)?
2) print out 2 preselected area (2 different sections in the same
spreadsheet).


Any help will be greatly appreciated.

Regarding scheduled updates, it's easiest to do this through Windows' Scheduled Tasks. It's accessible through Control Panel.

You would schedule Windows to open an Excel file and have your code run on the Workbook_Open() event for that file.

ok i'm not sure exactly what you are asking, what do you mean by passing in
arguments, what are you trying to pass and how? yes you can schedule
lookup in help the
application.ontime keyword
this of course requires that the excel document in question be open.
however, you can use the windows scheduled settings to place that workbook on
a schedule to load and place the desired macro into the workbook_open
event.... this requires that macros be set to low, or you have the project
signed and trusted, to enable automation.

"Jeff" wrote:

> Hi all,
> I'd like to have my spreadsheet to update automatically (don't know if
> that's possible).
> In order to do so, it needs to be able to do the following:
>
> 1) Is it a way to pass it arguments when lauching Excel, e.g. file name,
> list of parameter such as date, mode, ..etc? Currently, the update is
> triggered by clicking a commandbtn, if there is a way to pass in arguments
> maybe I can get that to update automatically at a scheduled time (using a
> batch file)?
> 2) print out 2 preselected area (2 different sections in the same
> spreadsheet).
>
>
> Any help will be greatly appreciated.
>
>

Use the windows schedular to open the file (and open excel) at the specified
time

Put code in the workbook_Open event in the thisworkbook module to perform
the actions you required.

http://www.cpearson.com/excel/events.htm
Chip Pearson's page on events.

--
Regards,
Tom Ogilvy

"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
> I'd like to have my spreadsheet to update automatically (don't know if
> that's possible).
> In order to do so, it needs to be able to do the following:
>
> 1) Is it a way to pass it arguments when lauching Excel, e.g. file name,
> list of parameter such as date, mode, ..etc? Currently, the update is
> triggered by clicking a commandbtn, if there is a way to pass in arguments
> maybe I can get that to update automatically at a scheduled time (using a
> batch file)?
> 2) print out 2 preselected area (2 different sections in the same
> spreadsheet).
>
>
> Any help will be greatly appreciated.
>
>

Guys,
Thanks for the replies. Sorry that I should have given more details.

I can't really put the code on open, it's because one of the tasks is to
print all the reports (over 80 of them), and printing on open isn't going to
work.

I am wondering if there is a way to pass in arguments (before the
spreadsheet is even lauched (e.g. c:\..\excel.exe test.xls printMode
date=12/31/04) things along that line. I know I can schedule an excel
spreadsheet to launch using scheduler, but not sure if I can pass in
arguments (variables) so the spreadsheet knows to perform some specific
actions.


Thanks

"Tom Ogilvy" wrote:

> Use the windows schedular to open the file (and open excel) at the specified
> time
>
> Put code in the workbook_Open event in the thisworkbook module to perform
> the actions you required.
>
> http://www.cpearson.com/excel/events.htm
> Chip Pearson's page on events.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jeff" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> > I'd like to have my spreadsheet to update automatically (don't know if
> > that's possible).
> > In order to do so, it needs to be able to do the following:
> >
> > 1) Is it a way to pass it arguments when lauching Excel, e.g. file name,
> > list of parameter such as date, mode, ..etc? Currently, the update is
> > triggered by clicking a commandbtn, if there is a way to pass in arguments
> > maybe I can get that to update automatically at a scheduled time (using a
> > batch file)?
> > 2) print out 2 preselected area (2 different sections in the same
> > spreadsheet).
> >
> >
> > Any help will be greatly appreciated.
> >
> >
>
>
>

One way of passing these arguments would be to add them to the Window task
as comments.
If you get the superb .dll file tskschd.dll, written by Edanmo Morgilllo:
http://www.mvps.org/emorcillo/vb6/grl/index.shtml
this can be done quite easily.
If you have several arguments you will have to add them as comments
separated by for example comma's and then
read the comment into a string variable and do a split with the Split
function.
Another way would be to have a workbook (could be an add-in) with some
arguments written to a sheet and this
workbook will then read the arguments and launch your other workbook.

RBS


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Guys,
> Thanks for the replies. Sorry that I should have given more details.
>
> I can't really put the code on open, it's because one of the tasks is
> to
> print all the reports (over 80 of them), and printing on open isn't going
> to
> work.
>
> I am wondering if there is a way to pass in arguments (before the
> spreadsheet is even lauched (e.g. c:\..\excel.exe test.xls printMode
> date=12/31/04) things along that line. I know I can schedule an excel
> spreadsheet to launch using scheduler, but not sure if I can pass in
> arguments (variables) so the spreadsheet knows to perform some specific
> actions.
>
>
> Thanks
>
> "Tom Ogilvy" wrote:
>
>> Use the windows schedular to open the file (and open excel) at the
>> specified
>> time
>>
>> Put code in the workbook_Open event in the thisworkbook module to perform
>> the actions you required.
>>
>> http://www.cpearson.com/excel/events.htm
>> Chip Pearson's page on events.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Jeff" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi all,
>> > I'd like to have my spreadsheet to update automatically (don't know
>> > if
>> > that's possible).
>> > In order to do so, it needs to be able to do the following:
>> >
>> > 1) Is it a way to pass it arguments when lauching Excel, e.g. file
>> > name,
>> > list of parameter such as date, mode, ..etc? Currently, the update is
>> > triggered by clicking a commandbtn, if there is a way to pass in
>> > arguments
>> > maybe I can get that to update automatically at a scheduled time (using
>> > a
>> > batch file)?
>> > 2) print out 2 preselected area (2 different sections in the same
>> > spreadsheet).
>> >
>> >
>> > Any help will be greatly appreciated.
>> >
>> >
>>
>>
>>

RB,
That's exactly what I am looking for. However, that's an invalid link,
Edanmo is not listed as Excel MVPs anymore.
Do you have another link or another dll that will do the same thing?

Thanks

"RB Smissaert" wrote:

> One way of passing these arguments would be to add them to the Window task
> as comments.
> If you get the superb .dll file tskschd.dll, written by Edanmo Morgilllo:
> http://www.mvps.org/emorcillo/vb6/grl/index.shtml
> this can be done quite easily.
> If you have several arguments you will have to add them as comments
> separated by for example comma's and then
> read the comment into a string variable and do a split with the Split
> function.
> Another way would be to have a workbook (could be an add-in) with some
> arguments written to a sheet and this
> workbook will then read the arguments and launch your other workbook.
>
> RBS
>
>
> "Jeff" <[email protected]> wrote in message
> news:[email protected]...
> > Guys,
> > Thanks for the replies. Sorry that I should have given more details.
> >
> > I can't really put the code on open, it's because one of the tasks is
> > to
> > print all the reports (over 80 of them), and printing on open isn't going
> > to
> > work.
> >
> > I am wondering if there is a way to pass in arguments (before the
> > spreadsheet is even lauched (e.g. c:\..\excel.exe test.xls printMode
> > date=12/31/04) things along that line. I know I can schedule an excel
> > spreadsheet to launch using scheduler, but not sure if I can pass in
> > arguments (variables) so the spreadsheet knows to perform some specific
> > actions.
> >
> >
> > Thanks
> >
> > "Tom Ogilvy" wrote:
> >
> >> Use the windows schedular to open the file (and open excel) at the
> >> specified
> >> time
> >>
> >> Put code in the workbook_Open event in the thisworkbook module to perform
> >> the actions you required.
> >>
> >> http://www.cpearson.com/excel/events.htm
> >> Chip Pearson's page on events.
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >> "Jeff" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi all,
> >> > I'd like to have my spreadsheet to update automatically (don't know
> >> > if
> >> > that's possible).
> >> > In order to do so, it needs to be able to do the following:
> >> >
> >> > 1) Is it a way to pass it arguments when lauching Excel, e.g. file
> >> > name,
> >> > list of parameter such as date, mode, ..etc? Currently, the update is
> >> > triggered by clicking a commandbtn, if there is a way to pass in
> >> > arguments
> >> > maybe I can get that to update automatically at a scheduled time (using
> >> > a
> >> > batch file)?
> >> > 2) print out 2 preselected area (2 different sections in the same
> >> > spreadsheet).
> >> >
> >> >
> >> > Any help will be greatly appreciated.
> >> >
> >> >
> >>
> >>
> >>
>
>

Can't find any download site now.
If you want I can mail you the zipped file.

RBS


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> RB,
> That's exactly what I am looking for. However, that's an invalid link,
> Edanmo is not listed as Excel MVPs anymore.
> Do you have another link or another dll that will do the same thing?
>
> Thanks
>
> "RB Smissaert" wrote:
>
>> One way of passing these arguments would be to add them to the Window
>> task
>> as comments.
>> If you get the superb .dll file tskschd.dll, written by Edanmo Morgilllo:
>> http://www.mvps.org/emorcillo/vb6/grl/index.shtml
>> this can be done quite easily.
>> If you have several arguments you will have to add them as comments
>> separated by for example comma's and then
>> read the comment into a string variable and do a split with the Split
>> function.
>> Another way would be to have a workbook (could be an add-in) with some
>> arguments written to a sheet and this
>> workbook will then read the arguments and launch your other workbook.
>>
>> RBS
>>
>>
>> "Jeff" <[email protected]> wrote in message
>> news:[email protected]...
>> > Guys,
>> > Thanks for the replies. Sorry that I should have given more
>> > details.
>> >
>> > I can't really put the code on open, it's because one of the tasks
>> > is
>> > to
>> > print all the reports (over 80 of them), and printing on open isn't
>> > going
>> > to
>> > work.
>> >
>> > I am wondering if there is a way to pass in arguments (before the
>> > spreadsheet is even lauched (e.g. c:\..\excel.exe test.xls printMode
>> > date=12/31/04) things along that line. I know I can schedule an excel
>> > spreadsheet to launch using scheduler, but not sure if I can pass in
>> > arguments (variables) so the spreadsheet knows to perform some specific
>> > actions.
>> >
>> >
>> > Thanks
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> >> Use the windows schedular to open the file (and open excel) at the
>> >> specified
>> >> time
>> >>
>> >> Put code in the workbook_Open event in the thisworkbook module to
>> >> perform
>> >> the actions you required.
>> >>
>> >> http://www.cpearson.com/excel/events.htm
>> >> Chip Pearson's page on events.
>> >>
>> >> --
>> >> Regards,
>> >> Tom Ogilvy
>> >>
>> >> "Jeff" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hi all,
>> >> > I'd like to have my spreadsheet to update automatically (don't
>> >> > know
>> >> > if
>> >> > that's possible).
>> >> > In order to do so, it needs to be able to do the following:
>> >> >
>> >> > 1) Is it a way to pass it arguments when lauching Excel, e.g. file
>> >> > name,
>> >> > list of parameter such as date, mode, ..etc? Currently, the update
>> >> > is
>> >> > triggered by clicking a commandbtn, if there is a way to pass in
>> >> > arguments
>> >> > maybe I can get that to update automatically at a scheduled time
>> >> > (using
>> >> > a
>> >> > batch file)?
>> >> > 2) print out 2 preselected area (2 different sections in the same
>> >> > spreadsheet).
>> >> >
>> >> >
>> >> > Any help will be greatly appreciated.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>

You do not need any DLL's. Add the following code to a module and call it
from your
ThisWorkbook Private Sub Workbook_Open() procedure

Sub Get_Args()
' Extracts arguments from command line
' Heavily modified from an original post by By Laurent Longre(with Excel
97).
' the required format of the command line call is (Example only):
' START /WAIT Excel /e/^^TYX_W.csv/^^TYX_W.csv/C:\GSI\DEVEL\/
Yahoo_Ldr_Basic2.xls
' Probably will not work for more that 3 parameters w/o mods
Dim Args() As String
Dim CmdLine As String 'command-line string
Dim ArgCount As Integer 'number of parameters
Dim Pos1 As Integer, PosN As Integer
Dim Temp As Integer
Dim Temp2 As Integer
CmdLine = GetCommandLineA 'get the cmd-line string
CmdLine = Mid(CmdLine, 1, 255) 'Truncate the command line
On Error Resume Next 'for the wksht-function "Search"
Pos1 = Application.WorksheetFunction.Search("/e", CmdLine, 1) + 1 'search
"/e"
Pos1 = Application.WorksheetFunction.Search("/", CmdLine, Pos1) + 1 '1st
param

Do While ArgCount < 3
PosN = Application.WorksheetFunction.Search("/", CmdLine, Pos1) 'Next
param
ArgCount = ArgCount + 1
ReDim Preserve Args(ArgCount)
Temp2 = Err.Number
Temp = IIf(Err.Number <> 0, Len(CmdLine) - Pos1, PosN) - Pos1
Args(ArgCount) = Mid(CmdLine, Pos1, Temp)
Argsp(ArgCount - 1) = Args(ArgCount) ' transfer to the Public array
' MsgBox "Argument " & ArgCount & " : " & Args(ArgCount)
Pos1 = PosN + 1
Loop

End Sub
--
Hope this helps/Thanks for your help


"RB Smissaert" wrote:

> Can't find any download site now.
> If you want I can mail you the zipped file.
>
> RBS
>
>
> "Jeff" <[email protected]> wrote in message
> news:[email protected]...
> > RB,
> > That's exactly what I am looking for. However, that's an invalid link,
> > Edanmo is not listed as Excel MVPs anymore.
> > Do you have another link or another dll that will do the same thing?
> >
> > Thanks
> >
> > "RB Smissaert" wrote:
> >
> >> One way of passing these arguments would be to add them to the Window
> >> task
> >> as comments.
> >> If you get the superb .dll file tskschd.dll, written by Edanmo Morgilllo:
> >> http://www.mvps.org/emorcillo/vb6/grl/index.shtml
> >> this can be done quite easily.
> >> If you have several arguments you will have to add them as comments
> >> separated by for example comma's and then
> >> read the comment into a string variable and do a split with the Split
> >> function.
> >> Another way would be to have a workbook (could be an add-in) with some
> >> arguments written to a sheet and this
> >> workbook will then read the arguments and launch your other workbook.
> >>
> >> RBS
> >>
> >>
> >> "Jeff" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Guys,
> >> > Thanks for the replies. Sorry that I should have given more
> >> > details.
> >> >
> >> > I can't really put the code on open, it's because one of the tasks
> >> > is
> >> > to
> >> > print all the reports (over 80 of them), and printing on open isn't
> >> > going
> >> > to
> >> > work.
> >> >
> >> > I am wondering if there is a way to pass in arguments (before the
> >> > spreadsheet is even lauched (e.g. c:\..\excel.exe test.xls printMode
> >> > date=12/31/04) things along that line. I know I can schedule an excel
> >> > spreadsheet to launch using scheduler, but not sure if I can pass in
> >> > arguments (variables) so the spreadsheet knows to perform some specific
> >> > actions.
> >> >
> >> >
> >> > Thanks
> >> >
> >> > "Tom Ogilvy" wrote:
> >> >
> >> >> Use the windows schedular to open the file (and open excel) at the
> >> >> specified
> >> >> time
> >> >>
> >> >> Put code in the workbook_Open event in the thisworkbook module to
> >> >> perform
> >> >> the actions you required.
> >> >>
> >> >> http://www.cpearson.com/excel/events.htm
> >> >> Chip Pearson's page on events.
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Tom Ogilvy
> >> >>
> >> >> "Jeff" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Hi all,
> >> >> > I'd like to have my spreadsheet to update automatically (don't
> >> >> > know
> >> >> > if
> >> >> > that's possible).
> >> >> > In order to do so, it needs to be able to do the following:
> >> >> >
> >> >> > 1) Is it a way to pass it arguments when lauching Excel, e.g. file
> >> >> > name,
> >> >> > list of parameter such as date, mode, ..etc? Currently, the update
> >> >> > is
> >> >> > triggered by clicking a commandbtn, if there is a way to pass in
> >> >> > arguments
> >> >> > maybe I can get that to update automatically at a scheduled time
> >> >> > (using
> >> >> > a
> >> >> > batch file)?
> >> >> > 2) print out 2 preselected area (2 different sections in the same
> >> >> > spreadsheet).
> >> >> >
> >> >> >
> >> >> > Any help will be greatly appreciated.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
>
>