CODEWITHSUNDEEP
excelvbams-word
Arlen Beiler
Arlen Beiler

Reputation: 15866

Editing Excel spreadsheats from Word with VBA

How do I edit excel spreadsheets from word using VBA?

Upvotes: 1

Views: 2413

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

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

Upvotes: 3

Related Questions