Automatically open, update and save an Excel workbook

Question

I want to change the column number format automatically in excel.

Set excel = CreateObject("Excel.Application") Set oWB = excel.Workbooks.Open("E:\Docs\Invoice.csv") /* Excel Macro starts */ Columns("G:G").Select Selection.NumberFormat = "m/d/yyyy" Columns("H:H").Select Selection.NumberFormat = "0.00" /* Excel Macro ends */ oWB.save oWB.Application.Quit 

I run this .vbs using the command line. Excel document is not updating.
Can someone help me in solving this problem?

Thank you in advance

Answer 1

What you are missing in the code above is that you do not fully qualify Excel objects.

How does vbs understand what Columns("G:G") ?

Is that what you are trying? ( Not indexed - just typed it directly )

 Dim objXLApp, objXLWb, objXLWs Set objXLApp = CreateObject("Excel.Application") Set objXLWb = objXLApp.Workbooks.Open("E:\Docs\Invoice.csv") '~~> Working with Sheet1 Set objXLWs = objXLWb.Sheets(1) With objXLWs '/* Excel Macro starts */ .Columns("G:G").NumberFormat = "m/d/yyyy" .Columns("H:H").NumberFormat = "0.00" '/* Excel Macro ends */ End With objXLWb.Save objXLWb.Close (False) Set objXLWs = Nothing Set objXLWb = Nothing objXLApp.Quit Set objXLApp = Nothing 

EDIT : My only question is that numberformat will not remain, as it is a CSV file. Can you save it as an excel file?

TEST AND TEST

 Dim objXLApp, objXLWb, objXLWs Set objXLApp = CreateObject("Excel.Application") objXLApp.Visible = True Set objXLWb = objXLApp.Workbooks.Open("E:\Docs\Invoice.csv") '~~> Working with Sheet1 Set objXLWs = objXLWb.Sheets(1) With objXLWs .Columns("G:G").NumberFormat = "m/d/yyyy" .Columns("H:H").NumberFormat = "0.00" End With '~~> Save as Excel File (xls) to retain format objXLWb.SaveAs "C:\Sample.xls", 56 '~~> File Formats '51 = xlOpenXMLWorkbook (without macro in 2007-2010, xlsx) '52 = xlOpenXMLWorkbookMacroEnabled (with or without macro in 2007-2010, xlsm) '50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb) '56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls) objXLWb.Close (False) Set objXLWs = Nothing Set objXLWb = Nothing objXLApp.Quit Set objXLApp = Nothing