Solved

How to save-as using a batch file

Posted on 2009-05-20
17
946 Views
Last Modified: 2012-05-07
I'm trying to figure out how to save an excel file as a tab delimited text file using a batch file.
0
Comment
Question by:zacIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 24433962
You can't (with DOS batch commands). WSH (VBS or JS) or PowerShell should be able to do, as they have access to the Excel System Object and can command it.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 24434356
Store the following script into a .vbs file, e.g. XLS2Tab.vbs, and start it with the fully qualified (!) file name. If you only use the file name without path, it will be stored in the Excel default directory. E.g.

XLS2Tab.vbs  c:\xls\Myfile.xls

The XLS file may not have more than one workbook, else you will get a prompt and a warning message.

Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false
file = WScript.Arguments.Item(0)
set wbs = excel.Workbooks.Open(file)
wbs.SaveAs file & ".txt", -4158
wbs.Close 0
excel.quit
set excel = nothing

Open in new window

0
 
LVL 16

Expert Comment

by:t0t0
ID: 24434459
I'm not saying nothing!!
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 70

Expert Comment

by:Qlemo
ID: 24434490
Got you saying something! Ahh, "not ... nothing" is "something" ...
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24434604
hmmm.... :)
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 24434614
Are you referring to the last line, t0t0? "said x-l = nothing"
0
 

Author Comment

by:zacIT
ID: 24434754
Thanks a bunch.. this worked.
Last question, how do i close a file using the same batch file?
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24434772
Qlemo willl answer that question for you....
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 24434831
That might be difficult, if you mean you have Excel opened, changed something, and want to close that open Excel.
The above script opens a new one, invisible, and closes the workbook (wbs.Close) and Excel (excel.quit). Maybe you can do that yourself? Else you have to tell me a little more about.
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24434848
He''ll probably say something along the lines of: "DOS automatically opens and closes files for you. You just send output to the file. Once the data has been written to the file, the file is closed without us having to explicitly close it ourselves"

The command :"ECHO This is a test line >file.txt" will output the string: "This is a test line" to the file: file.txt. And that's all there is to it. No need to open. No need to close. No need to worry. As simple as that!
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24434869
Ooops sorry. I've been posting in the wrong question. Please ignore my comments.
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24435563
zacIT
Qlemo

Apologies.... I was being flippant for a moment however, it backfired on me....

I thought the question (about closing the file) related to redirection in DOS.

I had posted the wrong reply addressed to the wrong person regarding the wrong topic in the wrong thread. Basically, I couldn't have got it more wrong.

I think I'm going to leave it at that for the night!

 
0
 

Author Comment

by:zacIT
ID: 24493715
Guys i'm having a problem with the excel file i'm trying to save as text. My preprocess that creates the file leaves the new report (in excel) open. When i run the sript you provided, it reopens the old (unsaved) version as read-only since there already a file with the same name openned. How do i close this already openned excel file using your script?
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 125 total points
ID: 24494018
Use this code. It will open the file, if not already opened, and hence work anyway. After work, it closes the Worksheet and the corresponding Excel instance, just for cleanup.

file = WScript.Arguments.Item(0)
set wbs = GetObject(file)
wbs.SaveAs file & ".txt", -4158
set excel = wbs.Application
wbs.Close 0
excel.quit
set excel = nothing

Open in new window

0
 

Author Comment

by:zacIT
ID: 24495242
Where do i pass my file name as argument?
0
 

Author Comment

by:zacIT
ID: 24495271
i got it to work this way:

'file = WScript.Arguments.Item(0)
set wbs = GetObject("C:\#Workaround\vendors.xls")
wbs.SaveAs "C:\#Workaround\vendors.txt", -4158
set excel = wbs.Application
wbs.Close 0
excel.quit
set excel = nothing
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 24496309
It was intended to be called either by
cscript <vbsfile>.vbs <excelfile>.xls
or just
<vbsfile>.vbs <excelfile>.xls
However, if the file name is fixed, your adaption is ok.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

VALIDATING DATES One method of validating dates is to jam the date into the DATE command and see if it accepts it by examining the system's errorlevel value. A non-zero result indicates failure. A typical example might look something like the fol…
Being a system administrator some time we require to do things remotely, one of them is installing software. Here I am going to tell you how to install software through wmic (Windows management instrument console). I am not at all saying that this i…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 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