none
Use Excel command line to save worksheet as tab-delimited

    Question

  • I need to have a bat file (or a java program) use excel command line to access an excel file to save one of its worksheets as a tab-delimited file. 

    E.g. If I do this w/o the command line, I open the excel file (which has multiple worksheets), click on the worksheet that I want, select File - Save-As, and then select the file type (tab delimited).  When I lclick 'Save', a window pops up saying that the 'file type does not support workbooks  that contain multiple sheets ... to save only the active sheet, click OK' and ... I click OK.

    Thursday, January 20, 2011 3:43 PM

Answers

  • I would use a workbook in a trusted location with an Auto_Open macro to do the job.
    Your batch file can then run Excel and have it open that workbook.

    If the file with the sheet to be saved can have macros in it, that's the simplest.  Something like this, in a standard module in the workbook.

    Sub Auto_Open()
      Application.OnTime Now, "Continue_Open"
    End Sub
    Sub Continue_Open()
    ' run after opening has completed
      Application.DisplayAlerts = False ' prevent warning dialog
      ThisWorkbook.Sheets("Sheet1").SaveAs "C:\Test\File.txt", Fileformat:=xlTextWindows
      Application.DisplayAlerts = True
      ThisWorkbook.Saved = True  ' avoid Save changes?
      Application.Quit
    End Sub

    If the workbook cannot contain the macro then you need another workbook that does and it will have to open the target workbook, do the save and then close it before quitting.


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    • Marked as answer by Sally Tang Wednesday, January 26, 2011 7:21 AM
    Friday, January 21, 2011 5:39 PM