Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
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
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 49
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   Report Post  
Junior Member
 
Posts: 3
Default

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

Quote:
Originally Posted by steve View Post
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
  #4   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 49
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   Report Post  
Junior Member
 
Posts: 3
Default

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

Quote:
Originally Posted by steve View Post
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


  #6   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 49
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 49
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Spreadsheets formulas don't update GGirl Excel Discussion (Misc queries) 2 September 20th 10 09:15 AM
Excel Spreadsheets formulas don't update GGirl Excel Discussion (Misc queries) 1 April 21st 10 04:39 AM
Automatically Update Excel Links Rodman Excel Discussion (Misc queries) 0 March 11th 10 08:30 PM
Update one worksheet tab to automatically update all other tabs? shoechic1 Excel Worksheet Functions 1 May 24th 09 03:55 PM
I'm trying to link two excel spreadsheets so they update automati Michelle Excel Worksheet Functions 1 June 8th 06 08:04 PM


All times are GMT +1. The time now is 02:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"