?
Solved

.BAT file to Open Excel Files in a folder, Save and Close

Posted on 2009-02-20
19
Medium Priority
?
15,787 Views
Last Modified: 2012-05-06
I need to open multiple EXCEL files in a folder, Save all of them upon closing.  Could you provide batch script that accomplish this?

Folder Name: Z:
File Name: (could have spaces) *.xls
0
Comment
Question by:sqlreport
  • 8
  • 4
  • 4
  • +2
19 Comments
 
LVL 21

Expert Comment

by:AmazingTech
ID: 23696697
Can't with a batch file but you can so this with vbs.
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 1500 total points
ID: 23696852
with a vbs you can open all xls files in a given file like so, manipulate them, save them and exit
Cheers
Dave

Dim objFSO, objFolder, objFile
Dim objExcel, objWB
Set objExcel = CreateObject("Excel.Application")
Set objFSO = CreateObject("scripting.filesystemobject")
   MyFolder = "C:\test"
Set objFolder = objfso.getfolder(myfolder)
For Each objFile In objfolder.Files
If Right(objFile.Name,3) = "xls" Then
Set objWB = objExcel.Workbooks.Open(objFile)
'do stuff
objWB.save
objWB.close
End If
Next 
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing 

Open in new window

0
 
LVL 1

Author Comment

by:sqlreport
ID: 23696911
I have the vb script as well.  Just wanted to find out if there's a way to get batch file to do the same.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 21

Expert Comment

by:AmazingTech
ID: 23697059
The batch file would need to call something so it could control Excel. Your batch file could call VBS but what's the point.

With batch file you could probably get some kind of sendkeys.exe pressing the buttons in Excel but vbs is going to be the best/consistent for this solution
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23704206
Yes you can do it with batch files.

To show you how, I will use EXCEL.EXE to open 3 excel files named: file1.xls, file2.xls and file3.xls.

On a typical setup, Microsoft installs excel in the following folder (you might need to t alter this):

   C:\Program Files\Microsoft Office\OFFICE11

Suppose your three excel files are in the following folder:

   C:\Documents and Settings\t0t0\My Documents

Then the following will open all 3 files in excel:


@echo off
set excel_program=C:\Program Files\Microsoft Office\OFFICE11\excel.exe
set xls_files=C:\Documents and Settings\Paul\My Documents
:
cd %xls_files%
start %excel_program% file1.xls; file2.xls; file3.xls
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23704211
Oops!! must include double-quotes....

Yes you can do it with batch files.

To show you how, I will use EXCEL.EXE to open 3 excel files named: file1.xls, file2.xls and file3.xls.

On a typical setup, Microsoft installs excel in the following folder (you might need to t alter this):

   C:\Program Files\Microsoft Office\OFFICE11

Suppose your three excel files are in the following folder:

   C:\Documents and Settings\t0t0\My Documents

Then the following will open all 3 files in excel:


@echo off
set excel_program=C:\Program Files\Microsoft Office\OFFICE11\excel.exe
set xls_files=C:\Documents and Settings\Paul\My Documents
:
cd "%xls_files%"
start "%excel_program%" "file1.xls"; "file2.xls"; "file3.xls"
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 23704870
t0t0:

"Save all of them upon closing"
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23704923
when excel is closes, there is no need to save a file unless that file has been modified.

excel automatically prompts the user to save the file for each file that has been mdified.

won't that be sufficient?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 23707273
it would seem strange to half automate a process and then require manual intervention (on potentially many files) for the second portion
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23707359
brettdj

Let's look at it from another perspective....

You're talking about automatically saving files when exiting an application.... you want to take out the human interaction element.... well, the way i see it is, for the need to save on exit to exist it must be assumed the file is open in read and write mode. Suppose the user frops a huge bombshell and deletes half of the records.... would you really want the application to save on exit.... so, at what point do you want the human interactive element to be automated?

Just clicking 'close' itself requires human interaction. To completely safeguard a file's contents from the user it should be open in 'read only' mode, but if that was the case then there would be no need to save on exit.

In a nutshell then, I cannot understand anyone wanting to automate save on exit if there is a risk of writing bad data to a file. Unfortunately, the danger element that human interaction brings to a file is during the 'editing' part of an application - and there must be a mechanism to override this if need be. That is why we are prompted to save on exit.

I hope the author of this question reads this post.... for his own good.

brettdj and AmazingTech, if you still think I am wrong please discuss this further as it is important we, as developers, agree on a standard - and let's face, the standard has already been established hence, confirm save on exit.
 
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 23707402
Hi,
Running code over a batch of excel files to open them, peform a repetitive task, then save and close them is a routine task. The whole intent is automation. I've provided code to do exactly this, at least 5 times over the last two weeks
The OP has requested a batch script to do a 3 step open, save, close. Which as far as my limited knowledge goes in this area, is not doable
Cheers
Dave
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23707487
>>"Running code over a batch of excel files to open them, peform a repetitive task, then save and close them is a routine task."

I understand where you're coming from now.... you're actually opening the files, performing a set task using another process, saving, and then closing the files.... I agree under these circumstances filesave should be fully automated.

However, whatever process is performing the task while the excel file is open should also perform the save and close actions too as in:

DOS BATCH PROCESS
open excel file
start external process
end

EXTERNAL PROCESS
process data in excel file
save file
close file
end

it might be helpful to know what sqlreport is doing with the open excel file and what process/application is used to perform this.
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 23718506
Perhaps the asker can comment. We could only be assuming on what is the actual requirement.

I agree with what's the point in opening up an Excel document and then saving it without changes but it could be a simple as I have these important excel documents that I want backed up each day. People in charge of backups will only backup data on a differential or incremental schedule. SO if you really want your file backed up everyday. Open them, Save them, Close them ;)
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23718931
Good point AmazingTech.

Another train of thought is, opening an excel file can trigger look-up data updates which cells reference from external source material. This is then saved on close. However, if this was the case, it should be possible to trigger a macro to close the file. This could be tricky though because the macro would need to know when all, if any, any cells have been updated.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 23719542
>  agree with what's the point in opening up an Excel document and then saving it without changes
Maybe to appear like you've been doing some work :)
0
 
LVL 6

Expert Comment

by:Justin_W_Chandler
ID: 23750361
It really is a bit wierd to open an excel file, save it without changes, and then close it. It seems completely pointless to save something when there's no changes to save...... If the point was to make a backup in a different location, couldn't you simply copy the files to that location? I'd love to hear the author's explanation on why he's trying to automate what seems to accomplish nothing. Hmmmmm *thoughtful*
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23750486
Hello Mr Chandler....

I'm frightened to look over my shoulder in case you're standing right behind me!

It might be something simple such as a macro firing up when the excel sheet opens.

I think we would all love to know.... but, that's probably just curiosity on my part which could only serve to suggest alternatives, as you have done, where applicable.

Please sqlreport, put us out of our misery....
0
 
LVL 1

Author Closing Comment

by:sqlreport
ID: 31549444
I had to use VB script as well.  Thank you all.
0
 
LVL 16

Expert Comment

by:t0t0
ID: 23923192
We are still none the wiser.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question