• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Linda Chin

New Member
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

Excel Ninja
Staff member
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

New Member
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
 
Top