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.
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.
Application.OnTime Now, "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?
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 bySally TangWednesday, January 26, 2011 7:21 AM
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.