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