Word opens Excel workbook - how to close it?


John Smith

I'm trying to pass values from Word to Excel. The following Word vba
codes do not give an error message, though it doesn't do what I want it
to do (that's another problem).

After the code finished running, I tried to open test.xlsm but got a
message saying the file is open. I was offered the option of opening the
file as read only.

How do I close the file test.xlsm? Thanks.

Option Explicit

Sub count_words()
Dim oRange As Word.Range
Dim iTotalWords1 As Integer
Dim iTotalWords2 As Integer
Dim iLoop As Integer
Dim n As Integer

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\Documents and Settings\me\My Documents\test.xlsm"


'If Excel is running, get a handle on it; otherwise start a new instance
of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible =
True here; but your code will run faster if you don't make it visible

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

iLoop = ActiveDocument.ComputeStatistics(wdStatisticParagraphs)

For n = 1 To iLoop

iTotalWords1 = ActiveDocument.ComputeStatistics(wdStatisticWords)

Set oRange = ActiveDocument.Paragraphs(1).Range


iTotalWords2 = ActiveDocument.ComputeStatistics(wdStatisticWords)

oXL.ActiveWorkbook.Worksheets("sheet8").Cells(n, 4).Value = iTotalWords1
- iTotalWords2

Next n

If ExcelWasNotRunning Then
End If

'Make sure you release object references.

Set oWB = Nothing
Set oXL = Nothing

Exit Sub

MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
End If

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question