Format CSV File then Save as an XLS with VBScript

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
gtdalsanto
Pro Scripter
Posts: 51
Joined: Tue Oct 03, 2006 4:22 pm
Location: Akron, Ohio
Contact:

Format CSV File then Save as an XLS with VBScript

Post by gtdalsanto » Tue Dec 11, 2007 8:42 pm

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
Gary T. DalSanto
Inventive Software Designs, LLC
[email protected]

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Post by Marcus Tettmar » Tue Dec 11, 2007 8:59 pm

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
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

gtdalsanto
Pro Scripter
Posts: 51
Joined: Tue Oct 03, 2006 4:22 pm
Location: Akron, Ohio
Contact:

Post by gtdalsanto » Tue Dec 11, 2007 10:04 pm

Works perfectly. Thanks for your quick response again.
Gary T. DalSanto
Inventive Software Designs, LLC
[email protected]

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts