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
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