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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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.

surotkin