D
dave.cuthill
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
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