用VBA从Word编辑Excel电子表格

首先需要设置对正在运行的Excel版本的引用。在VBE中,转到“工具”>“引用”,然后单击“Microsoft Excel 12.0对象库”(12.0表示2007,11.0表示2003)等。

然后您可以编写类似的代码(打开一个新的Excel实例,打开、编辑并保存一个新工作簿)。您可以使用GetObject访问正在运行的Excel实例:

Sub EditExcelFromWord()

Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set appExcel = CreateObject("Excel.Application")
With appExcel
    .Visible = True
    Set wb = .Workbooks.Add
    Set ws = wb.Worksheets(1)
    ws.Range("A1").Value2 = "Test"
    wb.SaveAs ThisDocument.Path & Application.PathSeparator & "temp.xls"
    Stop 'admire your work and then click F5 to continue
    Set ws = Nothing
    Set wb = Nothing
    Set appExcel = Nothing

End With

End Sub