ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address