» 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
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Learn MS Excel 2002 VBA/XML Programming
