How to save-as using a batch file

I'm trying to figure out how to save an excel file as a tab delimited text file using a batch file.
zacITAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
t0t0Commented:
I'm not saying nothing!!
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Got you saying something! Ahh, "not ... nothing" is "something" ...
0
 
t0t0Commented:
hmmm.... :)
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Are you referring to the last line, t0t0? "said x-l = nothing"
0
 
zacITAuthor Commented:
Thanks a bunch.. this worked.
Last question, how do i close a file using the same batch file?
0
 
t0t0Commented:
Qlemo willl answer that question for you....
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
t0t0Commented:
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
 
t0t0Commented:
Ooops sorry. I've been posting in the wrong question. Please ignore my comments.
0
 
t0t0Commented:
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
 
zacITAuthor Commented:
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
 
zacITAuthor Commented:
Where do i pass my file name as argument?
0
 
zacITAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
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.