[Office] auto-convert .xls to .csv

[itzviper]

Hello all,

I have an interesting request. I'm pretty new to writing macro's for Office, but I'm no stranger to VB. The issue I'm having is:

I need to be able to convert a .xls file to a .csv file from a script. I know that office allows you to pass a macro to be executed from the command line via the /m switch. The problem is getting the macro to work.

I'm using Office 2003, and if i record a macro, then go to file_saveas, change the filetype to .csv, it all works fine. Except for the fact that it asks the user for input, example: If there are more than one sheets in the workbook, it will pop up with a message box informing you. Also another message box pops up saying "File may contain features that are not compatible with CSV ...." do you want to keep the .csv format? and it has a Yes, No, and Help button.

I was wondering if there is a way to force it to save in the format without asking for user input (so it is script friendly).

[westconn1]

you can automate excel to do what you want, but that is different from shelling the whole thing from the command line
as you know, you can only save 1 sheet to csv, so you can copy that sheet to temp workbook, or delete all other sheets first, when you saveAs the original workbook will be closed with no changes

you can use application.displayalerts = false to stop most warnings, but i doubt that will be satisfactory to avoid warning about too many sheets, there is also option in saveAs
from msdn

SaveAs Method


Saves changes to the sheet (Syntax 1) or workbook (Syntax 2) in a different file.

Syntax 1

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)

Syntax 2

syntax1 is to save a sheet, syntax 2 is to save workbook, the sheet syntax is what you want in this case

[RobDog888]

Here is the reverse csv to xls. Just an example but if you change it around it can easily be made to do xls to csv.

http://www.vbforums.com/showthread.php?t=408487