Solved

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

Posted on 2015-01-26
43
80 Views
Last Modified: 2015-02-02
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?
0
Comment
Question by:WO2015
  • 24
  • 19
43 Comments
 
LVL 23

Expert Comment

by:NVIT
ID: 40571459
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
 

Author Comment

by:WO2015
ID: 40571463
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40571485
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
 

Author Comment

by:WO2015
ID: 40571491
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40571520
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
 

Author Comment

by:WO2015
ID: 40572687
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40572868
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
 

Author Comment

by:WO2015
ID: 40573886
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40573919
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
 

Author Comment

by:WO2015
ID: 40573971
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40573996
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40576061
Revised steps in last post
0
 

Author Comment

by:WO2015
ID: 40576070
Thank you, I will test this tomorrow.
0
 

Author Comment

by:WO2015
ID: 40577450
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40577514
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
 

Author Comment

by:WO2015
ID: 40578164
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40578189
See my Note: above
- your.bat = The name of your .BAT file, i.e. 32FULL.bat
0
 

Author Comment

by:WO2015
ID: 40578206
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40578213
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
 

Author Comment

by:WO2015
ID: 40578220
Yes and then it just gave me another command line, as shown above.
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40578233
This version expects CLSEXCEL.XLS on your Desktop. Is it?
0
Shouldn't all users have the same email signature?

You wouldn't let your users design their own business cards, would you? So, why do you let them design their own email signatures? Think of the damage they could be doing to your brand reputation! Choose the easy way to manage set up and add email signatures for all users.

 

Author Comment

by:WO2015
ID: 40578397
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40578406
In excel, with CLSEXCEL.XLS open, pick File, Save As. In the dialog, does it show the folder CLSEXCEL is in?
0
 

Author Comment

by:WO2015
ID: 40578417
E:\Temp is what it says
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40578441
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
 

Author Comment

by:WO2015
ID: 40578454
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40578464
Documents folder
0
 

Author Comment

by:WO2015
ID: 40578557
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40578571
1. Open a CMD prompt
2. pushd "%userprofile%\documents"
3. dir *.xls /od

Is it there?
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40578583
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40578588
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
 

Author Comment

by:WO2015
ID: 40579907
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40580197
Please remove the @echo off line from your 32FULL.BAT and run again. This will show better what's happening.
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40580227
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
 
LVL 23

Expert Comment

by:NVIT
ID: 40580290
I just added the :Pause section. Be sure to copy everything again.
0
 

Author Comment

by:WO2015
ID: 40580405
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
 
LVL 23

Accepted Solution

by:
NVIT earned 500 total points
ID: 40580469
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
 

Author Comment

by:WO2015
ID: 40580723
I added the closed code on the last line, it did not close the file. Am I supposed to add it someplace else?
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40580743
Are you talking about the last code post I made?
Just copy the entire code post and replace yours with it.
0
 

Author Comment

by:WO2015
ID: 40580747
No, the code to close the open file after its renamed.
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40580761
Replace your code with my last code post.
0
 

Author Comment

by:WO2015
ID: 40584062
That works perfect!  Thank you, sorry it took so long. I appreciate the help!
0
 
LVL 23

Expert Comment

by:NVIT
ID: 40584443
No worries, WO2015... I'm glad it worked for you.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

To effectively work with Diskpart on a Server Core, it is necessary to write some small batch script's, because you can't execute diskpart in a remote powershell session. To get startet, place the Diskpart batch script's into a share on your loca…
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now