Batch file to run a folder of Excel programs sequencially

[WSC4ME]

Hello,
This likely is an easy one. I need to have a batch file to run a dozen or so excel spreasheets in a folder, one at a time. I need them to start up, refresh and then shut down, doing this one at a time until the last one. Can someone help me with this one?

Thanks...

 

[smah]

Please clarify 'refresh'. They could be opened 1 at a time from a batch file, but I'm not sure they could closed nicely that way.

 

[WSC4ME]

The worksheet is linked to an external data source that refreshes when the spreadsheet is ran. (auto-update is turned on in external data range properties)

I was thinking about the clean closing of the spreadsheet too. I have two of the spreadsheet automated now in XP scheduler and I am using taskkill right now and it seems to work well. I had about 400 more spreadsheets to add to the folder here so if I had a script the opened the first spreadsheet, it updated and when the update was finished, it closed, then went to the next,and did the same that would be great...

 

[smah]

I assume that you'll want to wait for a bit to make sure the data is refreshed. There's no good way to way to 'wait' in a batch file, so you might want to use sleep.exe from

http://www.computerhope.com/dutil.htm.

My expect you'll want something like this (fix the path settings & taskkill as required)

@echo off
set excel="C:\Program Files\Microsoft Office\Office10\EXCEL.exe"
set storedir=%homedrive%%homepath%\My Documents
for /F %%F in ('dir /b /o:n "%storedir%\*.xls"') do (
%excel% "%storedir%\%%F"
sleep.exe 10
taskkill /f /im excel.exe
::Wait a little in case of problems
sleep.exe 5
)

There's a few problems with that (like spaces in the filenames), but it's too late for me to figure out now

 

[gbaughma]

You know, I have a script that will open, print, and close a folder full of Excel files (I wrote it for my boss a LONG time ago, because he had to print each spreadsheet every week or something like that)... it might be worth taking a look at.

It's written in VBS

' Script to print multiple excel files

Dim DocToPrint, oExcel, oDoc

Welcome_MsgBox_Message = "This script will print all Excel files in the folder this" & _
	" script was run from."
Welcome_MsgBox_Title = "File Printer"
Call Welcome()

' Get the full path and filename of the script file

scriptfile = WScript.ScriptFullName 

' Get the path to the script file

Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(scriptfile)
scriptpath = f.ParentFolder
Set f = Nothing

Set fl = fso.GetFolder(scriptpath)
Set fc = fl.Files

' Create the excel object
Set oExcel = CreateObject("Excel.Application")

For Each f in fc

DocToPrint = scriptpath & "\" & f.name
If Right(DocToPrint,3) = "xls" Then


	Set oExcelActiveDoc = oExcel.Workbooks.Open("" & DocToPrint)
	oExcelActiveDoc.PrintOut
	oExcel.Workbooks.Close

End If
Next

Set oExcel = Nothing
WScript.Quit(0)


' ********************************************************
' *
' * Welcome
' *
' ********************************************************
Sub Welcome()
     Dim intDoIt
     intDoIt = MsgBox(Welcome_MsgBox_Message, _
          vbOKCancel + vbInformation, _
          Welcome_MsgBox_Title )
     If intDoIt = vbCancel Then
          WScript.Quit
     End If
End Sub

Just my 2¢

"In order to start solving a problem, one must first identify its owner." --Me
--Greg