Word opens Excel workbook - how to close it?
 

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(wdStatisticParagr aphs)

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

Word opens Excel workbook - how to close it?
 

oWB.Close True

Tim


"John Smith" wrote in message
...
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(wdStatisticParagr aphs)

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