sqlreport
asked on
.BAT file to Open Excel Files in a folder, Save and Close
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
Folder Name: Z:
File Name: (could have spaces) *.xls
Can't with a batch file but you can so this with vbs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
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
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
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"
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"
t0t0:
"Save all of them upon closing"
"Save all of them upon closing"
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?
excel automatically prompts the user to save the file for each file that has been mdified.
won't that be sufficient?
it would seem strange to half automate a process and then require manual intervention (on potentially many files) for the second portion
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.
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.
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
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
>>"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.
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.
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 ;)
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 ;)
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.
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.
> 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 :)
Maybe to appear like you've been doing some work :)
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*
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....
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....
ASKER
I had to use VB script as well. Thank you all.
We are still none the wiser.