Hello, I have several excel spreadsheets linked to an ms access database. The excel spreadsheets needs to be updated prior running the access database so it uses the latest data. Normally I do this be opening and then saving the spreadsheets.
The spreadsheets have names like TIMASTER ACCESS, SERVHIST ACCESS, etc.
Is there a way I can do this automatically by doing only one or two actions instead of opening and closing numerous spreadsheets?
Regards, Rob

This may be a bit off topic, but if all you need do is open and close files,
it could be worth looking at windows scripts. I use them in many ways, the
beauty is that once set up they only need a double click, or even setup to
run when you start your computer, or as you start your database.

Regards Steve

"Rob Rozema" wrote in message
...

Hello, I have several excel spreadsheets linked to an ms access
database. The excel spreadsheets needs to be updated prior running the
access database so it uses the latest data. Normally I do this be
opening and then saving the spreadsheets.
The spreadsheets have names like TIMASTER ACCESS, SERVHIST ACCESS,
etc.
Is there a way I can do this automatically by doing only one or two
actions instead of opening and closing numerous spreadsheets?
Regards, Rob




--
Rob Rozema

Thanks, Steve. Sounds promising. When I open the Excel spreadsheet I have to wait awhile ( 10 sec to 5 minutes depending on which spreadsheet ) so it's data will update from another database. Then I can save and close again. Can you set this type of scenario with a windows script ?
Regards, Rob

OK

This works on my system, but no guarantees for you, I would advise caution,
and please use dummy files to test it first (or have a good backup)
copy below into notepad and save it as "test.vbs"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test1.xls")
objExcel.Application.Visible = True
objExcel.ActiveWorkbook.Save()
objExcel.ActiveWorkbook.Close
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test2.xls")
objExcel.Application.Visible = True
objExcel.ActiveWorkbook.Save()
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

This will open and close the two files shown within the script "test1 and
test2". The files have to be in the folder "C:\temp"
All this should do is open the two files and then close them again simple as
that, from your description that is all that you need to do. If you perform
any other actions this will not happen.



As an alternative you could try copying the following into notepad and
saving it as "test.bat"

for %%f in (C:\temp\*.xls) do start /wait C:\"Program Files"\"Microsoft
Office"\"Office12"\excel.exe "%%f"

This one will open all excel files in the folder "C:\temp" but you will have
to save and close them yourself, before the next one is automatically
opened.


I hope this gets you interested in the possibilities, but I will leave the
details to you.

Regards Steve

"Rob Rozema" wrote in message
...

Thanks, Steve. Sounds promising. When I open the Excel spreadsheet I
have to wait awhile ( 10 sec to 5 minutes depending on which
spreadsheet ) so it's data will update from another database. Then I
can save and close again. Can you set this type of scenario with a
windows script ?
Regards, Rob

steve;956160 Wrote:
This may be a bit off topic, but if all you need do is open and close
files,
it could be worth looking at windows scripts. I use them in many ways,
the
beauty is that once set up they only need a double click, or even setup
to
run when you start your computer, or as you start your database.

Regards Steve

"Rob Rozema" wrote in message
...-

Hello, I have several excel spreadsheets linked to an ms access
database. The excel spreadsheets needs to be updated prior running
the
access database so it uses the latest data. Normally I do this be
opening and then saving the spreadsheets.
The spreadsheets have names like TIMASTER ACCESS, SERVHIST ACCESS,
etc.
Is there a way I can do this automatically by doing only one or two
actions instead of opening and closing numerous spreadsheets?
Regards, Rob




--
Rob Rozema -




--
Rob Rozema

Hi Steve. That script works for me as well. We are almost there. Because the spreadsheet is updated through a network database it takes time before the update is finished. Can we add a script line saying that the spreadsheet should stay open for say 5 minutes before saving and closing? I like the script better then the alternative option because the process is completely automatic. Thank you so much for your help. Regards, Rob

It is a shame there is no other event that could be checked for when the
update is complete.
It means that even a file that updates in 5 seconds will have to stay open
for extra time, but you can alter the time for each file.
You will have to bear in mind that on a network somedays will be slower than
others.
If you add the line
Wscript.Sleep 300000
before the line with the save command it will pause the script for 5 minutes
(300000 milliseconds)

Regards Steve

"Rob Rozema" wrote in message
...

Hi Steve. That script works for me as well. We are almost there. Because
the spreadsheet is updated through a network database it takes time
before the update is finished. Can we add a script line saying that the
spreadsheet should stay open for say 5 minutes before saving and
closing? I like the script better then the alternative option because
the process is completely automatic. Thank you so much for your help.
Regards, Rob

steve;956170 Wrote:
OK

This works on my system, but no guarantees for you, I would advise
caution,
and please use dummy files to test it first (or have a good backup)
copy below into notepad and save it as "test.vbs"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test1.xls")
objExcel.Application.Visible = True
objExcel.ActiveWorkbook.Save()
objExcel.ActiveWorkbook.Close
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test2.xls")
objExcel.Application.Visible = True
objExcel.ActiveWorkbook.Save()
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

This will open and close the two files shown within the script "test1
and
test2". The files have to be in the folder "C:\temp"
All this should do is open the two files and then close them again
simple as
that, from your description that is all that you need to do. If you
perform
any other actions this will not happen.



As an alternative you could try copying the following into notepad and

saving it as "test.bat"

for %%f in (C:\temp\*.xls) do start /wait C:\"Program Files"\"Microsoft

Office"\"Office12"\excel.exe "%%f"

This one will open all excel files in the folder "C:\temp" but you will
have
to save and close them yourself, before the next one is automatically
opened.


I hope this gets you interested in the possibilities, but I will leave
the
details to you.

Regards Steve

"Rob Rozema" wrote in message
...-

Thanks, Steve. Sounds promising. When I open the Excel spreadsheet I
have to wait awhile ( 10 sec to 5 minutes depending on which
spreadsheet ) so it's data will update from another database. Then I
can save and close again. Can you set this type of scenario with a
windows script ?
Regards, Rob

steve;956160 Wrote:-
This may be a bit off topic, but if all you need do is open and
close
files,
it could be worth looking at windows scripts. I use them in many
ways,
the
beauty is that once set up they only need a double click, or even
setup
to
run when you start your computer, or as you start your database.

Regards Steve

"Rob Rozema" wrote in message
...--

Hello, I have several excel spreadsheets linked to an ms access
database. The excel spreadsheets needs to be updated prior
running-
the-
access database so it uses the latest data. Normally I do this be
opening and then saving the spreadsheets.
The spreadsheets have names like TIMASTER ACCESS, SERVHIST
ACCESS,
etc.
Is there a way I can do this automatically by doing only one or
two
actions instead of opening and closing numerous spreadsheets?
Regards, Rob




--
Rob Rozema ---




--
Rob Rozema -




--
Rob Rozema

Hi Rob you could try this one, In place of the sleep command insert this
line
objExcel.ActiveWorkBook.refreshall
before the line with the save command, this may be better

Regards Steve

"steve" wrote in message
...
It is a shame there is no other event that could be checked for when the
update is complete.
It means that even a file that updates in 5 seconds will have to stay open
for extra time, but you can alter the time for each file.
You will have to bear in mind that on a network somedays will be slower
than others.
If you add the line
Wscript.Sleep 300000
before the line with the save command it will pause the script for 5
minutes (300000 milliseconds)

Regards Steve

"Rob Rozema" wrote in message
...

Hi Steve. That script works for me as well. We are almost there. Because
the spreadsheet is updated through a network database it takes time
before the update is finished. Can we add a script line saying that the
spreadsheet should stay open for say 5 minutes before saving and
closing? I like the script better then the alternative option because
the process is completely automatic. Thank you so much for your help.
Regards, Rob

steve;956170 Wrote:
OK

This works on my system, but no guarantees for you, I would advise
caution,
and please use dummy files to test it first (or have a good backup)
copy below into notepad and save it as "test.vbs"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test1.xls")
objExcel.Application.Visible = True
objExcel.ActiveWorkbook.Save()
objExcel.ActiveWorkbook.Close
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test2.xls")
objExcel.Application.Visible = True
objExcel.ActiveWorkbook.Save()
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

This will open and close the two files shown within the script "test1
and
test2". The files have to be in the folder "C:\temp"
All this should do is open the two files and then close them again
simple as
that, from your description that is all that you need to do. If you
perform
any other actions this will not happen.



As an alternative you could try copying the following into notepad and

saving it as "test.bat"

for %%f in (C:\temp\*.xls) do start /wait C:\"Program Files"\"Microsoft

Office"\"Office12"\excel.exe "%%f"

This one will open all excel files in the folder "C:\temp" but you will
have
to save and close them yourself, before the next one is automatically
opened.


I hope this gets you interested in the possibilities, but I will leave
the
details to you.

Regards Steve

"Rob Rozema" wrote in message
...-

Thanks, Steve. Sounds promising. When I open the Excel spreadsheet I
have to wait awhile ( 10 sec to 5 minutes depending on which
spreadsheet ) so it's data will update from another database. Then I
can save and close again. Can you set this type of scenario with a
windows script ?
Regards, Rob

steve;956160 Wrote:-
This may be a bit off topic, but if all you need do is open and
close
files,
it could be worth looking at windows scripts. I use them in many
ways,
the
beauty is that once set up they only need a double click, or even
setup
to
run when you start your computer, or as you start your database.

Regards Steve

"Rob Rozema" wrote in message
...--

Hello, I have several excel spreadsheets linked to an ms access
database. The excel spreadsheets needs to be updated prior
running-
the-
access database so it uses the latest data. Normally I do this be
opening and then saving the spreadsheets.
The spreadsheets have names like TIMASTER ACCESS, SERVHIST
ACCESS,
etc.
Is there a way I can do this automatically by doing only one or
two
actions instead of opening and closing numerous spreadsheets?
Regards, Rob




--
Rob Rozema ---




--
Rob Rozema -




--
Rob Rozema