Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing a workbook without saving from Word
try
oWB.Close SaveChanges:=False If ExcelWasNotRunning Then oXL.Quit End If then release your variables. -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing a workbook without saving from Word
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing a workbook without saving from Word
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving and Closing | Excel Worksheet Functions | |||
VBA - closing without saving changes | Excel Programming | |||
closing excel after closing a workbook | Excel Programming | |||
Closing Workbook without Saving | Excel Programming | |||
Closing a workbook without saving | Excel Programming |