Creating a .bat file to name and save a file to excel.

Hello,

The program I am running creates an excel file with the name CLSEXCEL.XLS.  How do I save this file with a specific name within a specific folder and close the file that comes up using a .bat file?
WO2015Asked:
Who is Participating?
 
NVITConnect With a Mentor Commented:
Why is there an ! mark on the excel file it created...

Not sure where that ! mark is coming from. I don't see using ! in the code.

Can you post the exact filename?

...is there a way to close the original file that opened?
See my earlier post on this: http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_28604215.html#a40572868
In this version, I've added the line to close excel.

Another thing, you may get an error if you run it before 12 PM. Here's an updated version:
REM Version 6
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
set FilePath=e:\temp
set TgtPath=G:\CLSINC

set FFName=%FilePath%\CLSEXCEL.XLS
if exist %FFName% call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
taskkill /f /im excel.exe
copy /y %FFName% "%TgtPath%\CLSEXCEL2.XLS" >nul
if %errorlevel% equ 0 echo Successfully copied %FFName% to "%TgtPath%\CLSEXCEL2.XLS"
call :Pause
pushd "%TgtPath%"
set fn=CLSEXCEL2.XLS
set fn=%fn:~0,-4%
set fe=%~x1
for /f "tokens=2-4 delims=/ "  %%a in ("%date%") do (set MM=%%a& set DD=%%b& set YY=%%c)
for /f "tokens=1-4 delims=/:." %%a in ("%time%") do (set /a Hr=%%a& set Min=%%b& set Sec=%%c& set Mic=%%d)
REM If the hour is single digit, prefix it with a zero.
if %Hr% lss 10 set Hr=0%Hr%

set FNNew=%fn%_%YY%%MM%%DD%_%Hr%%Min%%Sec%%fe%
ren "%fn%%fe%" "%FNNew%"
if %errorlevel% equ 0 echo Successfully renamed %fn%%fe% to "%FNNew%"
call :Pause
popd
goto :eof

:Pause
REM Pause 3 seconds
ping 127.0.0.1 -n 3 >nul
goto :eof

Open in new window

0
 
NVITCommented:
How do I save this file with a specific name within a specific folder...
Modify the program that creates CLSEXCEL.XLS

...and close the file that comes up..

Please clarify.

Do already have a .bat you want to revise?

Do you want to do these after CLSEXCEL.XLS is created?
0
 
WO2015Author Commented:
Yes, I want to save and close the file after it comes up. I cant edit the report name, this is how our program software is set up. Every report comes out with that naming convention. Unless you rename and/or close the file it will not allow any other reports to run.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
NVITCommented:
Does it open Excel and opens CLSEXCEL.XLS also?

Would renaming CLSEXCEL.XLS suffice? e.g.
Make a RenCLSEXCEL.bat file. It renames CLSEXCEL.XLS to CLSEXCEL.XLS with a date & time suffix. Put this:
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
if exist CLSEXCEL.XLS call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
set fn=%1
set dt=%date%
set tm=%time%
ren %fn% %fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%

Open in new window

0
 
WO2015Author Commented:
That is exactly what I need to do, rename the file that the program creates and rename/save the file. Would the above coding work? I will have to test it tomorrow.
0
 
NVITCommented:
It will rename CLSEXCEL.XLS to something like CLSEXCEL_20150126_121205.XLS

However, if excel has CLSEXCEL.XLS opened, it will not worked.

Before running it for real, remove the ECHO prefix in front of "ECHO ren"

Revised:
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
if exist CLSEXCEL.XLS call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
set fn=%1
set fn=%fn:~0,-4%
set fe=%~x1
set dt=%date%
set tm=%time%
ECHO ren %fn%%fe% %fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%%fe%

Open in new window

0
 
WO2015Author Commented:
I will try this, but it is opening in excel so maybe the logic should be saving that file with the name it comes out with "CLSEXCEL.XLS" somewhere like my c:\document and then I can move and rename, which I know how to write. Do you know how I would save the output file into a folder of my choosing?
0
 
NVITCommented:
It can be done per your idea of saving it to another folder, then rename. I.e.:
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
if exist CLSEXCEL.XLS call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
copy CLSEXCEL.XLS "%userprofile%\documents\CLSEXCEL2.XLS"
pushd "%userprofile%\documents\"
set fn=CLSEXCEL2.XLS
set fn=%fn:~0,-4%
set fe=%~x1
set dt=%date%
set tm=%time%
ECHO ren %fn%%fe% %fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%%fe%
popd

Open in new window


But, although I've not confirmed it, the above may not work as excel may deny copying an open .xls file. Another solution is...

...if only CLSEXCEL.XLS is running in excel and you aren't working on other .xls files, we can use TASKKILL to close excel before renaming, i.e. add this line right below the :RenCLSEXCEL line like:
:RenCLSEXCEL
taskkill /f /im excel.exe
set fn=%1
set fn=%fn:~0,-4%
...

Open in new window

0
 
WO2015Author Commented:
I have a documents folder on my desktop and ran the .bat file. The original file is still up and I do not see the renamed one anywhere.
0
 
NVITCommented:
Did you run it from a CMD prompt?
Any errors or messages?
Are you seeing just the ECHO'd command? You have to remove the ECHO prefix to run it for real.
Please post your batch code.
0
 
WO2015Author Commented:
I copied and pasted it into a .bat file. Have the .xls report up and tried running the bat file by double clicking it like I can with other .bat files.  No error messages, the CMD shows very fast then goes away.
0
 
NVITCommented:
1. Open a separate CMD prompt via Run, cmd
2. pushd (to the folder of the .bat file)
3. Run the .bat by typing RenCLSEXCEL.bat (or whatever name you gave it)
4. Review the errors, if any. Right-click in window. Select all. Enter. Paste here in EE.
0
 
NVITCommented:
Revised steps in last post
0
 
WO2015Author Commented:
Thank you, I will test this tomorrow.
0
 
WO2015Author Commented:
Ok, I have some time to look at this now. What if I place that file on my desktop? What are the command prompts? I am sorry I am not used to using CMD really.
0
 
NVITCommented:
Notes:
- After each command you Type: press ENTER to execute the command.
- your.bat = The name of your .BAT file.

1. Pick Windows Start

2. Type: cmd
This opens a cmd window

3. Type: pushd %userprofile%\desktop
This should change to your desktop folder

4. Type: dir *.BAT
You should see your.bat

5. Type: your.bat
This runs your.bat.

6. If you want to copy everything to send to me...
- Right-click in the black area of cmd window
- Pick Select All
- Right-click to finish
- Post it here on EE and Submit to me
0
 
WO2015Author Commented:
It didnt seem to work when I typed in your.bat

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\administrator.WOLAWOFFICE>pushd %userprofile%\desktop

C:\Users\administrator.WOLAWOFFICE\Desktop>dir*.BAT
'dir*.BAT' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\administrator.WOLAWOFFICE\Desktop>dir *.bat
 Volume in drive C is OS
 Volume Serial Number is 9055-ECA7

 Directory of C:\Users\administrator.WOLAWOFFICE\Desktop

01/27/2015  04:34 PM               393 32FULL.bat
               1 File(s)            393 bytes
               0 Dir(s)   5,790,949,376 bytes free

C:\Users\administrator.WOLAWOFFICE\Desktop>your.bat
'your.bat' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\administrator.WOLAWOFFICE\Desktop>dir *.BAT
 Volume in drive C is OS
 Volume Serial Number is 9055-ECA7

 Directory of C:\Users\administrator.WOLAWOFFICE\Desktop

01/27/2015  04:34 PM               393 32FULL.bat
               1 File(s)            393 bytes
               0 Dir(s)   5,790,662,656 bytes free

C:\Users\administrator.WOLAWOFFICE\Desktop>your.bat
'your.bat' is not recognized as an internal or external command,
operable program or batch file.

C:\Users\administrator.WOLAWOFFICE\Desktop>
0
 
NVITCommented:
See my Note: above
- your.bat = The name of your .BAT file, i.e. 32FULL.bat
0
 
WO2015Author Commented:
It didn't do anything, same as when I double click it. I have below the CMD code the bat file code again:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\administrator.WOLAWOFFICE>pushd %userprofile%\desktop

C:\Users\administrator.WOLAWOFFICE\Desktop>dir *.BAT
 Volume in drive C is OS
 Volume Serial Number is 9055-ECA7

 Directory of C:\Users\administrator.WOLAWOFFICE\Desktop

01/27/2015  04:34 PM               393 32FULL.bat
               1 File(s)            393 bytes
               0 Dir(s)   5,788,553,216 bytes free

C:\Users\administrator.WOLAWOFFICE\Desktop>32FULL.bat

C:\Users\administrator.WOLAWOFFICE\Desktop>


32Full.bat
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
if exist CLSEXCEL.XLS call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
copy CLSEXCEL.XLS "%userprofile%\documents\CLSEXCEL2.XLS"
pushd "%userprofile%\documents\"
set fn=CLSEXCEL2.XLS
set fn=%fn:~0,-4%
set fe=%~x1
set dt=%date%
set tm=%time%
ECHO ren %fn%%fe% %fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%%fe%
popd
0
 
NVITCommented:
After you typed 32FULL.bat, did you press ENTER to execute the command?
Also, per my earlier post, this version will only echo the command. Again, this is just for visual confirmation.  It will not execute it. To execute it, remove the ECHO in front of "ECHO ren"

Still, please run 32FULL.bat and see what it echos to the screen.
0
 
WO2015Author Commented:
Yes and then it just gave me another command line, as shown above.
0
 
NVITCommented:
This version expects CLSEXCEL.XLS on your Desktop. Is it?
0
 
WO2015Author Commented:
Once our program completes the report it opens an EXCEL file named CLSEXCEL.XLS. Its just open, I do not know where its at but I do not know its open. Its not save any place, which is my dilemma on how to get this to work.
0
 
NVITCommented:
In excel, with CLSEXCEL.XLS open, pick File, Save As. In the dialog, does it show the folder CLSEXCEL is in?
0
 
WO2015Author Commented:
E:\Temp is what it says
0
 
NVITCommented:
Revise your 32FULL.bat to this:
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
set FilePath=e:\temp
set FFName=%FilePath%\CLSEXCEL.XLS
if exist %FFName% call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
copy /y %FFName% "%userprofile%\documents\CLSEXCEL2.XLS"
pushd "%userprofile%\documents"
set fn=CLSEXCEL2.XLS
set fn=%fn:~0,-4%
set fe=%~x1
set dt=%date%
set tm=%time%
ren %fn%%fe% %fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%%fe%
popd
goto :eof

Open in new window

0
 
WO2015Author Commented:
Looks like it copied.. where am I to look for it?

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\administrator.WOLAWOFFICE>pushd %userprofile%\desktop

C:\Users\administrator.WOLAWOFFICE\Desktop>dir *.BAT
 Volume in drive C is OS
 Volume Serial Number is 9055-ECA7

 Directory of C:\Users\administrator.WOLAWOFFICE\Desktop

01/29/2015  04:29 PM               451 32FULL.bat
               1 File(s)            451 bytes
               0 Dir(s)   5,771,915,264 bytes free

C:\Users\administrator.WOLAWOFFICE\Desktop>32FULL.bat
        1 file(s) copied.

C:\Users\administrator.WOLAWOFFICE\Desktop>
0
 
NVITCommented:
Documents folder
0
 
WO2015Author Commented:
I see the CMD saying 1 file copied but I do not see it in the documents folder. If I wanted to put it to something like G:\CLSINC what would I put that in place of in the .bat file?
0
 
NVITCommented:
1. Open a CMD prompt
2. pushd "%userprofile%\documents"
3. dir *.xls /od

Is it there?
0
 
NVITCommented:
REM Version 3
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
set FilePath=e:\temp
set FFName=%FilePath%\CLSEXCEL.XLS
set TgtPath=G:\CLSINC
if exist %FFName% call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
copy /y %FFName% "%TgtPath%\CLSEXCEL2.XLS"
if %errorlevel% equ 0 echo Successfully copied %FFName% to "%TgtPath%\CLSEXCEL2.XLS"
pushd "%TgtPath%"
set fn=CLSEXCEL2.XLS
set fn=%fn:~0,-4%
set fe=%~x1
set dt=%date%
set tm=%time%
set FNNew=%fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%%fe%
ren %fn%%fe% %FNNew%
if %errorlevel% equ 0 echo Successfully renamed %FFName% to "%FNNew%"
popd
goto :eof

Open in new window

0
 
NVITCommented:
REM Version 4
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
REM set FilePath=e:\temp
set FilePath=c:\local
set FFName=%FilePath%\CLSEXCEL.XLS
set TgtPath=G:\CLSINC
if exist %FFName% call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
copy /y %FFName% "%TgtPath%\CLSEXCEL2.XLS"
if %errorlevel% equ 0 echo Successfully copied %FFName% to "%TgtPath%\CLSEXCEL2.XLS"
REM Pause 3 seconds
ping 127.0.0.1 -n 3 >nul
pushd "%TgtPath%"
set fn=CLSEXCEL2.XLS
set fn=%fn:~0,-4%
set fe=%~x1
set dt=%date%
set tm=%time%
set FNNew=%fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%%fe%
ren %fn%%fe% %FNNew%
if %errorlevel% equ 0 echo Successfully renamed %FFName% to "%FNNew%"
REM Pause 3 seconds
ping 127.0.0.1 -n 3 >nul
popd
goto :eof

Open in new window

0
 
WO2015Author Commented:
I still am not seeing the file anyplace.  below is the CMD data:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\administrator.WOLAWOFFICE>pushd %userprofile%\desktop

C:\Users\administrator.WOLAWOFFICE\Desktop>dir *.BAT
 Volume in drive C is OS
 Volume Serial Number is 9055-ECA7

 Directory of C:\Users\administrator.WOLAWOFFICE\Desktop

01/30/2015  10:36 AM               758 32FULL.bat
               1 File(s)            758 bytes
               0 Dir(s)   5,805,547,520 bytes free

C:\Users\administrator.WOLAWOFFICE\Desktop>32FULL.bat

C:\Users\administrator.WOLAWOFFICE\Desktop>REM Version 4

C:\Users\administrator.WOLAWOFFICE\Desktop>
0
 
NVITCommented:
Please remove the @echo off line from your 32FULL.BAT and run again. This will show better what's happening.
0
 
NVITCommented:
There is an error in Version 4. Please use this one.
REM Version 5
@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
set FilePath=e:\temp
set TgtPath=G:\CLSINC

set FFName=%FilePath%\CLSEXCEL.XLS
if exist %FFName% call :RenCLSEXCEL CLSEXCEL.XLS
goto :eof

:RenCLSEXCEL
copy /y %FFName% "%TgtPath%\CLSEXCEL2.XLS" >nul
if %errorlevel% equ 0 echo Successfully copied %FFName% to "%TgtPath%\CLSEXCEL2.XLS"
call :Pause
pushd "%TgtPath%"
set fn=CLSEXCEL2.XLS
set fn=%fn:~0,-4%
set fe=%~x1
set dt=%date%
set tm=%time%
set FNNew=%fn%_%dt:~10,4%%dt:~4,2%%dt:~7,2%_%tm:~0,2%%tm:~3,2%%tm:~6,2%%fe%
ren %fn%%fe% %FNNew%
if %errorlevel% equ 0 echo Successfully renamed %fn%%fe% to "%FNNew%"
call :Pause
popd
goto :eof

:Pause
REM Pause 3 seconds
ping 127.0.0.1 -n 3 >nul
goto :eof

Open in new window

0
 
NVITCommented:
I just added the :Pause section. Be sure to copy everything again.
0
 
WO2015Author Commented:
That looks to have worked. Why is there an ! mark on the excel file it created and is there a way to close the original file that opened?  Thank you, glad we got this working!
0
 
WO2015Author Commented:
I added the closed code on the last line, it did not close the file. Am I supposed to add it someplace else?
0
 
NVITCommented:
Are you talking about the last code post I made?
Just copy the entire code post and replace yours with it.
0
 
WO2015Author Commented:
No, the code to close the open file after its renamed.
0
 
NVITCommented:
Replace your code with my last code post.
0
 
WO2015Author Commented:
That works perfect!  Thank you, sorry it took so long. I appreciate the help!
0
 
NVITCommented:
No worries, WO2015... I'm glad it worked for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.