How to save a copy of a *.CSV to *.XLS

[Linda Chin]

I use a VB script to format a MS EXCEL file and then save a copy of it to *.xls. The following command give us a messy look Excel file. while I use "SAVE AS to *.xls" manually, it works fine. Please give me some tips.


objExcel.ActiveWorkbook.SaveCopyAs("D:BANNER_FINANCEGRANT REPORTSBGR777Enewbook.xls")

 

[Hui]

Linda


Welcome to the Chandoo.org Forums


a few questions:


Your post is titled "How to save a copy of a *.CSV to *.XLS" but nowhere in the post do you mention CSV files?


When you say "Messy", I assume you mean unformatted columns of Numbers, please elaborate?


Where are you running this code from? as within Excel you don't need the objExcel. part of the line

 

[Linda Chin]

when I ran the following codes inside MS EXCEL, it worked fine. but, it does not work when I ran in a VB script from WINDOW. I am planning to schedule running this script nightly to convert 20 input *.csv files to Excel files with beautiful fonts and colors.


oBook.SaveAs "C:Book1.xls", xlWorkbookNormal <===this command is not working in VB script when I ran from WINDOW


oExcel.ActiveWorkbook.SaveAs "C:Book1.xls" <=This command does not give any error, but the output file is command delimited format. It complains error if I add this:xlWorkbookNormal


Sub test2()


'Create a new instance of Excel

Dim oExcel As Object

Dim oBook As Object

Dim oSheet As Object

Set oExcel = CreateObject("Excel.Application")

'Set oSheet = CreateObject("Excel.Sheet")


'Open the text file

Set oBook = oExcel.Workbooks.Open("D:BANNER_FINANCEGRANT REPORTSBGR777EBGR77CC.csv")


'Save as Excel workbook and Quit Excel

oBook.SaveAs "C:Book1.xls", xlWorkbookNormal


End Sub