correct formatting for excel spreadsheet [duplicat

Question

Possible Duplicate:
create excel spreadsheet

What is the correct formatting for a document so that it is recognized in excel? What character, if you are manually creating it, denotes the end of a column and/or create a new line?

I am manually creating a .xls file, using the file system, and populating it with data. However, I don't know what excel expects. No, I don't want to create a .csv file because I don't want the user to be asked to format it when opening it.

Thanks

Answer 1

Xls (and the other types of Excel 2007/2010 are binary fiels that you can not easily create like that, you need to use Com objects to create and manipulate them. Here an example from the scripting guys http://blogs.technet.com/b/heyscriptingguy/archive/2005/01/31/how-can-i-make-changes-to-and-then-re-save-an-existing-excel-spreadsheet.aspx

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1).Value = Now
objWorkbook.SaveAs("C:\Scripts\Test.xls")
objExcel.Quit

see http://msdn.microsoft.com/en-us/library/aa223697(v=office.11).aspx for a list of vba functions you can use

eg autoformatting all columns is like this

objWorksheet.columns.autofit;