Hi James,
I'd like to give you a little more detail. The following macro demontrates
how to get data from cells in an Excel workbook. It iterate through all the
used cells and copies their content into Word. It can be modified to retrieve
data from any specific cell or cells and copy the data to any location in a
Word document.
Sub GetExcelFromWord()
Const Error_FileNotFound = 1004
Const Error_NotRunning = 429
Const Error_NotInCollection = 9
Dim fileName As String
Dim wkbName As String
Dim xlApp As Object
Dim wkBook As Object
Dim newInstance As Boolean
Dim fileOpened As Boolean
Dim x As Long
Dim i As Long
Dim j As Long
Dim k As Integer
With Application.FileDialog(msoFileDialogFilePicker)
If .Show Then
fileName = .SelectedItems(1)
Else
MsgBox "You didn't select an Excel file to open."
GoTo ErrorHandler
End If
End With
k = InStrRev(fileName, "\", -1, vbTextCompare)
If k > 0 Then
wkbName = Right(fileName, Len(fileName) - k)
Else
MsgBox "A suitable file was not selected."
GoTo ErrorHandler
End If
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = Error_NotRunning Then
Set xlApp = CreateObject("Excel.Application")
newInstance = True
Else
newInstance = False
End If
On Error GoTo 0
xlApp.Visible = True
On Error Resume Next
Set wkBook = xlApp.Workbooks(wkbName)
If Err.Number = Error_NotInCollection Then
Err.Clear
Set wkBook = xlApp.Workbooks.Open(fileName)
If Err.Number = Error_FileNotFound Then
MsgBox "The file specified could not be opened.", _
vbCritical Or vbOKOnly, "File Not Opened"
GoTo ErrorHandler
Else
fileOpened = True
End If
End If
wkBook.Activate
On Error GoTo 0
i = 1
j = 1
For x = 1 To wkBook.WorkSheets.Count
With wkBook.WorkSheets(x)
For i = 1 To .UsedRange.Rows.Count
For j = 1 To .UsedRange.Columns.Count
With .Cells(i, j)
XlData = .Value
' Add code to copy the contents
' to somewhere in Word, for example
Selection.InsertAfter i & ", " & j & ":" & XlData &
vbCrLf
End With
Next
Next
End With
Next
ErrorHandler:
If fileOpened = True Then
wkBook.Close
End If
If newInstance = True Then
xlApp.Quit
End If
Set wkBook = Nothing
Set xlApp = Nothing
End Sub