Control Excel from Word

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

Document last updated 2000-04-12 12:49:31 Printerfriendly version