automated data entry to a s/s
 

i have a daily requirement to

1) collate data on a solaris (unix) system to a datafile
2) import the datafile (ascii) to a excel spreadsheet
3) create a graph in the s/s of the data

1) is automated
3) is built on the fly in the s/s already (ie the necesaary stuff is
in place to convert the data to a graph)

The bugbear is the importing manually of the data to the s/s. Not a
huge task (I have omitted some tedious steps above but they can
eventually be automated too...) but I would like to automate the
entire proces such that w/out human intervention - by a script that
runs automatically at a set time - the data file is imported to the
s/s and so the s/s complete with data and graph is available for
whoever might need to view it later.

So - is there some interface/command process that can be issued
(scripted) to import the data to a s/s without the s/s having to be opened
first?

Long shot I know but if I don't ask....

ian

automated data entry to a s/s
 

If your source file is a comma delimited file and has the extension CSV, it
can be opened directly in Excel.

"Ian Diddams" wrote:

i have a daily requirement to

1) collate data on a solaris (unix) system to a datafile
2) import the datafile (ascii) to a excel spreadsheet
3) create a graph in the s/s of the data

1) is automated
3) is built on the fly in the s/s already (ie the necesaary stuff is
in place to convert the data to a graph)

The bugbear is the importing manually of the data to the s/s. Not a
huge task (I have omitted some tedious steps above but they can
eventually be automated too...) but I would like to automate the
entire proces such that w/out human intervention - by a script that
runs automatically at a set time - the data file is imported to the
s/s and so the s/s complete with data and graph is available for
whoever might need to view it later.

So - is there some interface/command process that can be issued
(scripted) to import the data to a s/s without the s/s having to be opened
first?

Long shot I know but if I don't ask....

ian

automated data entry to a s/s
 

"AA2e72E" wrote in message ...
If your source file is a comma delimited file and has the extension CSV, it
can be opened directly in Excel.


yes - I know that - but that's nopr what i am asking.

If the s/s file is in place, and the comma delimited file is in place,
is there some sort of command line command (ppobably scripted,
probably scheduled) that would load the datafile into the s/s without
the s/s ever being opened.

That is, a totally automtaed situation such that

1) datafile is created automatically by scheduled background processes
2) suitable command is used by a scheduled background process to load
datafile into s/s.

There would be no human interaction whatsoever (hence the s/s couldn't
be "opened" ) but the end result would be a s/s that when it WAS
opened would already hold all the dta (and the subsequent natty graphs
etc).

cheers

ian

automated data entry to a s/s
 

Maybe you can record a macro when you import the data and build your graphs.

Then name that macro auto_open.

That macro will start whenever that file is opened (if macros are enabled).

Make sure you continue through saving the new workbook. End your macro with:

....
Application.quit
end sub

(closing all the other workbooks that you may have opened, too.)

Then use Windows Scheduler to start this excel workbook.

Ian Diddams wrote:

i have a daily requirement to

1) collate data on a solaris (unix) system to a datafile
2) import the datafile (ascii) to a excel spreadsheet
3) create a graph in the s/s of the data

1) is automated
3) is built on the fly in the s/s already (ie the necesaary stuff is
in place to convert the data to a graph)

The bugbear is the importing manually of the data to the s/s. Not a
huge task (I have omitted some tedious steps above but they can
eventually be automated too...) but I would like to automate the
entire proces such that w/out human intervention - by a script that
runs automatically at a set time - the data file is imported to the
s/s and so the s/s complete with data and graph is available for
whoever might need to view it later.

So - is there some interface/command process that can be issued
(scripted) to import the data to a s/s without the s/s having to be opened
first?

Long shot I know but if I don't ask....

ian

--

Dave Peterson

automated data entry to a s/s
 

Dave Peterson wrote in message Maybe you can record a macro when you import the data and build your graphs.

Then name that macro auto_open.

That macro will start whenever that file is opened (if macros are enabled).

Make sure you continue through saving the new workbook. End your macro with:

...
Application.quit
end sub

(closing all the other workbooks that you may have opened, too.)

Then use Windows Scheduler to start this excel workbook.


that's excellent dave - thanks.

one question ...

presumably this is a every-time hit though... ie when the s/s is
opened several days later it will read in the datafiles again? Thing
is the datafiles change on a daily basis BUT the s/s will be needed
for posterity for a partciular day

i.e. 1st december s/s will use the datafiles created on 1st december,
2nd december s/s uses datafiles created on 2nd december etc... but if
the 1st december s/s is subsequently opened on the 2nd it will
presumably be overwritten by the 2nd december's datafiles ?

which leads me to... can a macro as a grand finale delete itself? ie
open s/s on december 1, macro auto starts and loads the datafiles
(available for december 1st) - then deletes the macro ie itself.

???

ian

automated data entry to a s/s
 

Dave Peterson wrote in message Maybe you can record a macro when you import the data and build your graphs.

Then name that macro auto_open.

That macro will start whenever that file is opened (if macros are enabled).

Make sure you continue through saving the new workbook. End your macro with:

...
Application.quit
end sub

(closing all the other workbooks that you may have opened, too.)

Then use Windows Scheduler to start this excel workbook.


that's excellent dave - thanks.

one question ...

presumably this is a every-time hit though... ie when the s/s is
opened several days later it will read in the datafiles again? Thing
is the datafiles change on a daily basis BUT the s/s will be needed
for posterity for a partciular day

i.e. 1st december s/s will use the datafiles created on 1st december,
2nd december s/s uses datafiles created on 2nd december etc... but if
the 1st december s/s is subsequently opened on the 2nd it will
presumably be overwritten by the 2nd december's datafiles ?

which leads me to... can a macro as a grand finale delete itself? ie
open s/s on december 1, macro auto starts and loads the datafiles
(available for december 1st) - then deletes the macro ie itself.

???

ian

automated data entry to a s/s
 

Can a macro delete itself?

Yeah, see Chip Pearson's site for programming to the VBE:
http://www.cpearson.com/excel/vbe.htm

But I don't think I'd do it that way. I'd use the macro to create a new
workbook each time. The macro would be in its own workbook and the newly
created workbook wouldn't contain any macros. (Seems lots simpler.)

===
And if you're lucky, each file that is created will use the same name. Then
your macro doesn't have to do any thinking at all. Just open the file with that
same name each time.

If the files are created in separate folders or have names that vary, your macro
will have to know the logic behind the naming convention.

If you use a basename followed by a date:
myFile_2004_12_01.text

The macro could build that kind of string and find it.

So I'd say it depends on what that other program does.


Ian Diddams wrote:

Dave Peterson wrote in message Maybe you can record a macro when you import the data and build your graphs.

Then name that macro auto_open.

That macro will start whenever that file is opened (if macros are enabled).

Make sure you continue through saving the new workbook. End your macro with:

...
Application.quit
end sub

(closing all the other workbooks that you may have opened, too.)

Then use Windows Scheduler to start this excel workbook.

that's excellent dave - thanks.

one question ...

presumably this is a every-time hit though... ie when the s/s is
opened several days later it will read in the datafiles again? Thing
is the datafiles change on a daily basis BUT the s/s will be needed
for posterity for a partciular day

i.e. 1st december s/s will use the datafiles created on 1st december,
2nd december s/s uses datafiles created on 2nd december etc... but if
the 1st december s/s is subsequently opened on the 2nd it will
presumably be overwritten by the 2nd december's datafiles ?

which leads me to... can a macro as a grand finale delete itself? ie
open s/s on december 1, macro auto starts and loads the datafiles
(available for december 1st) - then deletes the macro ie itself.

???

ian

--

Dave Peterson