asked on

Trouble saving Excel file as tab delimited text file using .vbs

I have written a .vbs that will open an Excel file and reformat the raw data in the file (copy & paste cells etc.). I need to then save the sheet as a tab delimited .txt file to be read into another application. I keep getting an error, here is some sample code:

Set objExcel = createobject("Excel.application")
objExcel.Visible = false
objExcel.DisplayAlerts = False
objExcel.Workbooks.Open("C:\Sales.xls")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'format the data
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

objExcel.ActiveWorkbook.SaveAs Filename:= "C:\MyWorkbook.txt", FileFormat:=xlText
objExcel.quit

I keep getting an error on the 'SaveAs' line. It says "Error: Expected statement", "Code: 800A0400", "Source: Microsoft VBScript compilation error". It works fine if I use "objExcel.ActiveSheet.SaveAs ("C:\MyWorkbook.txt")", but it saves in a different type of 'Excel Text' because it is unreadable in any other applications (i.e. - notepad). This has been driving me crazy for the past few days, and any help is greatly appreciated. Let me know if you need anything else.
Thanks in advance.

EDDYKT

Try this

objExcel.Workbooks(("C:\Sales.xls").SaveAs Filename:= "C:\MyWorkbook.txt", FileFormat:=xlText

ASKER CERTIFIED SOLUTION

mvidas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RDeWolfe

ASKER

EDDYKT: I tried your solution, but there is a syntax error .... missing ")"

mvidas: Thanks a lot! It is always some little detail that has been overlooked.

mvidas

When I make my vbs files I generally assume that any constant won't be understood so I use the value and would put something like '-4158=xlText at the end, just for future changes if necessary. Glad to help!