A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

automatically update several excel spreadsheets



 
 
Thread Tools Display Modes
  #1  
Old May 26th, 2010, 04:02 AM posted to microsoft.public.excel.links
Rob Rozema
external usenet poster
 
Posts: 1
Default automatically update several excel spreadsheets


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
Ads
  #2  
Old May 26th, 2010, 04:05 PM posted to microsoft.public.excel.links
steve
external usenet poster
 
Posts: 28
Default automatically update several excel spreadsheets

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



  #3  
Old May 27th, 2010, 05:02 AM posted to microsoft.public.excel.links
Rob Rozema[_2_]
external usenet poster
 
Posts: 1
Default automatically update several excel spreadsheets


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
  #4  
Old May 27th, 2010, 10:24 AM posted to microsoft.public.excel.links
steve
external usenet poster
 
Posts: 28
Default automatically update several excel spreadsheets

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



  #5  
Old May 28th, 2010, 12:28 AM posted to microsoft.public.excel.links
Rob Rozema[_3_]
external usenet poster
 
Posts: 1
Default automatically update several excel spreadsheets


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
  #6  
Old May 28th, 2010, 08:31 AM posted to microsoft.public.excel.links
steve
external usenet poster
 
Posts: 28
Default automatically update several excel spreadsheets

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



  #7  
Old May 28th, 2010, 09:34 AM posted to microsoft.public.excel.links
steve
external usenet poster
 
Posts: 28
Default automatically update several excel spreadsheets

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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.