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

Posted on 2005-05-05
Last Modified: 2009-03-03
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

'format the data

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

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.
Question by:RDeWolfe
    LVL 26

    Expert Comment

    Try this

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

    Accepted Solution


    VBScript doesn't understand the constant "xlText", so is probably treating that as a 0.  xlText has the value of -4158, so you could probably use
     objExcel.ActiveWorkbook.SaveAs Filename:="C:\MyWorkbook.txt", FileFormat:=-4158
    I've had problems in the past specifying arguments as well, and since Filename and FileFormat are the first two arguments, you could also just use
     objExcel.ActiveWorkbook.SaveAs "C:\MyWorkbook.txt", -4158

    For a list of constants' values, see or just open excel VBA and enter "debug.print xltext" in the immediate window.

    Author Comment

    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.
    LVL 35

    Expert Comment

    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!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now