» Control Excel from Word using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions The two example macros below demonstrates how you can send information to Excel from Word
(e.g. creating a new workbook) and how you can retrieve information from Excel (e.g. reading information from a workbook).
Note! Read and edit the example code before you try to execute it in your own project!

Sub CreateNewExcelWB()
' to test this code, paste it into a Word module
' add a reference to the Excel-library
' create a new folder named C:\Foldername or edit the filnames in the code
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWB = xlApp.Workbooks.Add ' create a new workbook
    ' or
    'Set xlWB = xlApp.Workbooks.Open("C:\Foldername\Filename.xls") 
    ' open an existing workbook
    ' example excel operations
    With xlWB.Worksheets(1)
        For i = 1 To 100
            .Cells(i, 1).Formula = "Here is a example test line #" & i
        Next i
        If Dir("C:\Foldername\MyNewExcelWB.xls") <> "" Then
            Kill "C:\Foldername\MyNewExcelWB.xls"
        End If
        .SaveAs ("C:\Foldername\MyNewExcelWB.xls")
    End With
    xlWB.Close False ' close the workbook without saving
    xlApp.Quit ' close the Excel application
    Set xlWB = Nothing
    Set xlApp = Nothing
End Sub


Sub OpenAndReadExcelWB()
' assumes that the previous procedure has been executed
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim tString As String, r As Long
    Documents.Add ' create a new document
    Set xlApp = CreateObject("Excel.Application")
    'xlApp.Visible = True
    'xlApp.ScreenUpdating = False
    Set xlWB = xlApp.Workbooks.Open("C:\Foldername\MyNewExcelWB.xls") 
    ' open an existing workbook
    ' example excel operations
    r = 1
    With xlWB.Worksheets(1)
        While Cells(r, 1).Formula <> ""
            tString = Cells(r, 1).Formula
            With ActiveDocument.Content
                .InsertAfter tString
                .InsertParagraphAfter
            End With
            r = r + 1
        Wend
    End With
    xlWB.Close False ' close the workbook without saving
    xlApp.Quit ' close the Excel application
    Set xlWB = Nothing
    Set xlApp = Nothing
End Sub