自动打开，更新和保存Excel工作簿 [英] Open, Update and Save Excel workbook automatically

问题描述

我想在excel中自动修改列的数字格式.

I want to modify the column's 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

我使用命令行运行此.vbs. Excel文档未更新.
有人可以帮我解决这个问题吗?

I run this .vbs using command line. Excel doc does not get updated.
Could anyone please help me in resolving this issue ?

预先感谢

推荐答案

上面的代码中缺少的是您没有完全限定Excel对象.

What you are missing in the above code is you are not fully qualifying the Excel Objects.

VBS如何理解什么是Columns("G:G")?

How would vbs understand what is Columns("G:G")?

这是您要尝试的吗? (未经测试-只需直接输入)

Is this what you are trying? (Untested - 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

编辑:我唯一关心的是数字格式将不会保留，因为它是CSV文件.您可能想将其另存为Excel文件?

EDIT: My Only concern is that the numberformat will not stay as it is a CSV file. You might want to save it as an Excel file?

尝试并测试

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's in 2007-2010, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's 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

这篇关于自动打开，更新和保存Excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！