Editing Excel spreadsheats from Word with VBA

Question

How do I edit excel spreadsheets from word using VBA?

Answer 1

First you need to set a reference to the version of Excel you are running. In the VBE go to Tools>References and click Microsoft Excel 12.0 Object Library (12.0 for 2007, 11.0 for 2003) etc.

Then you can code something like this (opens a new instance of Excel, opens, edits and saves a new workbook). You'd use GetObject to access a running instance of 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