Format CSV File then Save as an XLS with VBScript

[gtdalsanto]

I am having trouble formatting a CSV file, then saving it as an Excel XLS file.

Currently I am using the VB code I have found on this site:

'Opens the Excel file in Excel
Sub OpenCSVFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = True
Set CSVBook = xlApp.Workbooks.open(filename)
end sub


What I am trying to do is Align/Left Justify all columns in the spreadsheet (select all cells, and left justify all cells), then perform a "Save As" on this reformatted csv worksheet to an Excel spreadsheet.

Below is my calling sequence:

VBRun>OpenCSVFile,c:\spool1.csv ' Open CSV file
VBRun>FormatSheet ' Perform associated formats
VBRun>SaveAsExcel,c:\spool1.xls ' Save formatted CSV to XLS

I have everything working except the syntax for the Allignment:

Sub FormatSheet
CSVBook.ActiveSheet.Range("A:A").Select ' Select Col A
xlApp.Selection.NumberFormat = "0000000000" ' Format to 10 dig
CSVBook.ActiveSheet.Range("A:L").Select ' Select Col A to L
xlApp.Selection.Columns.AutoFit ' Autofit all col
?? xlApp.Selection.Columns.AlignLeft??????? ' Left Justify cols

End Sub

And where I can't get the save to work correctly, in that it only saves the comma delimited data to the xls, not formatted.

Sub SaveAsExcel(FileName)
CSVBook.SaveAs FileName
EndSub

So in summary, the 2 problems I am having is once I open the CSV file, and do the formatting, If someone could give me the syntax for the following:

1) syntax to left justify the data in the selected columns
2) syntax to save the active formatted CSV to an Excel spreadsheet (xls).

I appreciate any help anyone can give me on this.

Thanks.

Gary

[Marcus Tettmar]

Align Left:

xlLeft = -4131
xlApp.Selection.HorizontalAlignment = xlLeft

Save as XLS - you need to tell it what format to save in. See the SaveAs method topic in the Excel help file.

xlExcel8 = 56
CSVBook.SaveAs FileName, xlExcel8

[gtdalsanto]

Works perfectly. Thanks for your quick response again.