Word opens Excel workbook - how to close it?

J

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"


Windows("test.docx").Activate

'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

oRange.Delete

iTotalWords2 = ActiveDocument.ComputeStatistics(wdStatisticWords)


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

Next n



If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.

Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
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

Top