Closing a workbook without saving from Word

I am controling excel from word - first I copy the contents of a table
from word then open up a new workbook in excel. I paste the table from
word into sheet1 of the excel workbook and then create a chartsheet in
excel and then copy it back into the word document. I then want to
close the workbook that I was using without saving it or being prompted
to save it. I have tried running the code a number of times with Excel
already open and none of the created workbooks are closed at the end of
the code. If excel is not open prior to running the code in word then
it seems to work okay. Here is the code I am using ... any ideas what I
am doing wrong? I have borrowed a portion of the code from the MVP
site.

Sub WorkOnAWorkbook()
Dim oWordDoc As Word.Document
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean


'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
oXL.DisplayAlerts = True

Set oWordDoc = ActiveDocument
TabCnt = oWordDoc.Tables.Count

Set rng = ActiveDocument.Tables(1).Range
rng.Collapse wdCollapseEnd
rng.InsertAfter "" & vbCrLf
rng.Collapse wdCollapseEnd
BMName = "BarChart" & Z
ActiveDocument.Bookmarks.Add Name:=BMName, Range:=rng
ActiveDocument.Tables(1).Select
Selection.Copy

'Open the workbook
oXL.Workbooks.Add
Set oWB = oXL.ActiveWorkbook

Set oRng = oXL.ActiveWorkbook.Worksheets(1).Cells(1, 1)
oRng.Select
oXL.ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False

'Do some stuff with the newly created workbook and copy a chart that
has been created
With oXL.ActiveWorkbook
' .....
.Sheets("Chart1").Select
.ActiveChart.ChartArea.Copy
End With


If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.

'Neither of these seem to close the workbook
oWB.Close (False)
oXL.ActiveWorkbook.Close (False)

Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'Paste the chartsheet from Excel into the word document
rng.Select
Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject,
Placement _
:=wdInLine, DisplayAsIcon:=False '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

try

oWB.Close SaveChanges:=False
If ExcelWasNotRunning Then
oXL.Quit
End If

then release your variables.

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> I am controling excel from word - first I copy the contents of a table
> from word then open up a new workbook in excel. I paste the table from
> word into sheet1 of the excel workbook and then create a chartsheet in
> excel and then copy it back into the word document. I then want to
> close the workbook that I was using without saving it or being prompted
> to save it. I have tried running the code a number of times with Excel
> already open and none of the created workbooks are closed at the end of
> the code. If excel is not open prior to running the code in word then
> it seems to work okay. Here is the code I am using ... any ideas what I
> am doing wrong? I have borrowed a portion of the code from the MVP
> site.
>
> Sub WorkOnAWorkbook()
> Dim oWordDoc As Word.Document
> Dim oXL As Excel.Application
> Dim oWB As Excel.Workbook
> Dim oSheet As Excel.Worksheet
> Dim oRng As Excel.Range
> Dim ExcelWasNotRunning As Boolean
>
>
> '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
> oXL.DisplayAlerts = True
>
> Set oWordDoc = ActiveDocument
> TabCnt = oWordDoc.Tables.Count
>
> Set rng = ActiveDocument.Tables(1).Range
> rng.Collapse wdCollapseEnd
> rng.InsertAfter "" & vbCrLf
> rng.Collapse wdCollapseEnd
> BMName = "BarChart" & Z
> ActiveDocument.Bookmarks.Add Name:=BMName, Range:=rng
> ActiveDocument.Tables(1).Select
> Selection.Copy
>
> 'Open the workbook
> oXL.Workbooks.Add
> Set oWB = oXL.ActiveWorkbook
>
> Set oRng = oXL.ActiveWorkbook.Worksheets(1).Cells(1, 1)
> oRng.Select
> oXL.ActiveSheet.PasteSpecial Format:="Text", Link:=False,
> DisplayAsIcon:=False
>
> 'Do some stuff with the newly created workbook and copy a chart that
> has been created
> With oXL.ActiveWorkbook
> ' .....
> .Sheets("Chart1").Select
> .ActiveChart.ChartArea.Copy
> End With
>
>
> If ExcelWasNotRunning Then
> oXL.Quit
> End If
>
> 'Make sure you release object references.
>
> 'Neither of these seem to close the workbook
> oWB.Close (False)
> oXL.ActiveWorkbook.Close (False)
>
> Set oRng = Nothing
> Set oSheet = Nothing
> Set oWB = Nothing
> Set oXL = Nothing
>
> 'Paste the chartsheet from Excel into the word document
> rng.Select
> Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject,
> Placement _
> :=wdInLine, DisplayAsIcon:=False '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
>

This does not seem to work either - everytime I run the code another
worksheet is generated and the previous one is never closed. If I prune
the code down to adding a workbook and then closing the workbook using
your suggestion it seems to work so there must be something in there
that doesn't allow the workbook to be closed but I am at a loss.

Any ideas?

David

I figured out my problem - some of the code that was referencing a
chart sheet that didn't exist (not included in the original post) seems
be to locking excel up to the point that it was not responding to the
request to close the workbook. Funny that I was not getting any kind of
error message on this. So, yes Tom your code did work - thanks for the
help.