Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why QUIT method doesn't work after COPY method?
Hello everyone, I found that I can not unload Excel (I open Excel-instance from Access using objExcel.Quit method after I execute Copy method of Range object. There is part of my code (without error handler block) which copie cells from one worksheet, creates a new worksheet and pastes thos cells with transpose option: Public Function myTest2() As Long Dim objExcel As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim ws_source As Excel.Worksheet myTest2 = 0 Set objExcel = New Excel.Application objExcel.Workbooks.Open FileName:="D:\test.xls" Set wb = objExcel.Workbooks("test.xls") Set ws_source = wb.Worksheets(1) ws_source.Range(Cells(1, 1), Cells(10, 20)).*Copy* wb.Sheets.Add Set ws = wb.ActiveSheet ws.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone SkipBlanks:=False, Transpose:=True objExcel.CutCopyMode = False wb.Close SaveChanges:=True objExcel.*Quit* Set ws = Nothing Set ws_source = Nothing Set wb = Nothing Set objExcel = Nothing End Function Does anybody have an idea why? Thanks. surotki -- surotki ----------------------------------------------------------------------- surotkin's Profile: http://www.excelforum.com/member.php...fo&userid=2119 View this thread: http://www.excelforum.com/showthread.php?threadid=47943 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why QUIT method doesn't work after COPY method?
If you explicitly refer to the Excel object instead of implicitly then you
should be good to go. I noticed in your code that you are refering to a cell using - ws_source.Range(Cells(1, 1), Cells(10, 20)).*Copy*. Refering to cells like this would be implicit. So I usually run Excel from Access using code similiar to this - Dim xl As New Excel.Application Dim wrksheet As Excel.Worksheet With xl .Visible = True .DisplayAlerts = False .Workbooks.Open "C:\Test.xls" .Cell("A1").Select .Selection.Copy .Cell("A2").Select .Selection.Paste .ActiveWorkbook.Close .DisplayAlerts = True .Quit End With Set xl = Nothing Hope this helps. "surotkin" wrote: Hello everyone, I found that I can not unload Excel (I open Excel-instance from Access) using objExcel.Quit method after I execute Copy method of Range object. There is part of my code (without error handler block) which copies cells from one worksheet, creates a new worksheet and pastes those cells with transpose option: Public Function myTest2() As Long Dim objExcel As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim ws_source As Excel.Worksheet myTest2 = 0 Set objExcel = New Excel.Application objExcel.Workbooks.Open FileName:="D:\test.xls" Set wb = objExcel.Workbooks("test.xls") Set ws_source = wb.Worksheets(1) ws_source.Range(Cells(1, 1), Cells(10, 20)).*Copy* wb.Sheets.Add Set ws = wb.ActiveSheet ws.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True objExcel.CutCopyMode = False wb.Close SaveChanges:=True objExcel.*Quit* Set ws = Nothing Set ws_source = Nothing Set wb = Nothing Set objExcel = Nothing End Function Does anybody have an idea why? Thanks. surotkin -- surotkin ------------------------------------------------------------------------ surotkin's Profile: http://www.excelforum.com/member.php...o&userid=21197 View this thread: http://www.excelforum.com/showthread...hreadid=479431 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why QUIT method doesn't work after COPY method?
This kind of code creates a ghost (unreleasable reference) to Excel and can
cause this problem ws_source.Range(Cells(1, 1), Cells(10, 20)).Copy this should be written ws_source.Range(ws_source.Cells(1, 1), ws_source.Cells(10, 20)).*Copy* You certainly might have more such constructs - everything should be fully qualified all the way back to the Excel application object. ws_source already is, so you can start from there. -- Regards, Tom Ogilvy "surotkin" wrote in message ... Hello everyone, I found that I can not unload Excel (I open Excel-instance from Access) using objExcel.Quit method after I execute Copy method of Range object. There is part of my code (without error handler block) which copies cells from one worksheet, creates a new worksheet and pastes those cells with transpose option: Public Function myTest2() As Long Dim objExcel As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim ws_source As Excel.Worksheet myTest2 = 0 Set objExcel = New Excel.Application objExcel.Workbooks.Open FileName:="D:\test.xls" Set wb = objExcel.Workbooks("test.xls") Set ws_source = wb.Worksheets(1) ws_source.Range(Cells(1, 1), Cells(10, 20)).*Copy* wb.Sheets.Add Set ws = wb.ActiveSheet ws.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True objExcel.CutCopyMode = False wb.Close SaveChanges:=True objExcel.*Quit* Set ws = Nothing Set ws_source = Nothing Set wb = Nothing Set objExcel = Nothing End Function Does anybody have an idea why? Thanks. surotkin -- surotkin ------------------------------------------------------------------------ surotkin's Profile: http://www.excelforum.com/member.php...o&userid=21197 View this thread: http://www.excelforum.com/showthread...hreadid=479431 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why QUIT method doesn't work after COPY method?
Hi Tom Ogilvy and Conrad, thanks for responses. I started with Tom's recommendation. It works. I felt there is a ghost (unreleasable reference) to Excel in my code. I use full reference now: ws_source.Range(ws_source.Cells(1, 1), ws_source.Cells(10, 20)).Copy instead of: ws_source.Range(Cells(1, 1), Cells(10, 20)).Copy It made my code working properly. Many thanks. surotki -- surotki ----------------------------------------------------------------------- surotkin's Profile: http://www.excelforum.com/member.php...fo&userid=2119 View this thread: http://www.excelforum.com/showthread.php?threadid=47943 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please post this thread a complete correct method, method about te | New Users to Excel | |||
Excel.Application.Quit() method | Excel Programming | |||
Quit Method do not kill excel process | Excel Programming | |||
Quit Method do not kill excel process | Excel Programming | |||
EXCEL.exe stays open after Quit method in an HTA | Excel Programming |