View Single Post
  #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