Can't Close Excel Process

[NeoPa]

Will do, working on it.

Firstly, congratulations on doing that. It simplified the whole thing enormously. I expect it helped to focus your mind on the problem too.

Secondly, I can say that I was able to reproduce the problem you had with your code and this problem also certainly went away when running Denburt's.

I'm interested in finding out what, specifically, causes this behaviour so I may post again soon.

[NeoPa]

It seems it's to do with the order of your ...

.Visible = True
.ScreenUpdating = True

...lines. When I changed them around to ...

.ScreenUpdating = True
.Visible = True

...(as the reverse of ...

.Visible = False
.ScreenUpdating = False

...) that worked fine. Notice how the onion is unwrapped here.

My final and working code was ...

Option Compare Database
Option Explicit

Public Sub TestIt()
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim sTarget As String

    Call DoCmd.Hourglass(True)
    sTarget = "C:\Test.Xls"
    Set appExcel = CreateObject("Excel.Application")
    With appExcel
        Set wbk = .Workbooks.Open(sTarget)
        Set wks = wbk.ActiveSheet
        .Visible = False
        .ScreenUpdating = False

        wks.Range("A1").Activate
        'Cells(1, 1).Activate
        .ActiveCell.Formula = "TestIt"

        Call wbk.Save
        Call wbk.Close
        .ScreenUpdating = True
        .Visible = True
        Call .Quit
    End With

    Set wks = Nothing
    Set wbk = Nothing
    Set appExcel = Nothing
    DoCmd.Hourglass False
    MsgBox ("Test complete.")
End Sub

[Denburt]

Nicely done Neo and thanks, that will help me tremendously in the future as I code between the two constantly. I just finished a MAJOR macro in Excel I wanted to do it in Access and store the data but they want everything reformatted and totaled in Excel. I think the amount of data they want to rearrange will be to much for excel so that will be interesting. These tips will definitely add to that project.

I hope this helps you as well Robin and thanks for asking this question it will help prevent future hazards here at my workplace for sure.

Again thanks,
DB

[Lisa Z]

I can't imagine what you may be doing wrong one quick fix may be to check and see if Excel is still running after you close the first instance then close that instance. It appears you are opening two instances somehow.

Set xlApp = GetObject(, "Excel.Applicat ion")
xlApp.quit

In VB 6.0, how do you check and see if Excel.exe is already running? I would like to do this in my application but do not know the syntax. I want to check and see if Excel.exe is running before I start any of my own logic.

[RobinAG]

Denburt and Neo:
Thanks, explictly calling appExcel for each command (or using With) worked great in the debug code, but in the original code as I put it piece-by piece back together, it looks like I'm stuck on the following line:

iTargetColumn = Selection.Cells.Find(what:=dDate, after:=ActiveCell, lookin:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext).Column

where iTargetColumn is an integer, and dDate is set as Date, to correspond with the first row of the workbook (which is consecutive dates). I put iTargetColumn in the code as:

with appExcel
.Cells(1, iTargetColumn).Activate

When I put this in the debug code, this is where it is causing the Excel process to remain open. I'm not sure if within that line of code how to explicitly call appExcel.

Sorry to put you guys through the ringer! You're helping me out a ton.

Robin

[Denburt]

Robin have you tried using a range instead of a selection as in the help file?
Highlight the word "find" then hit F1 and you can see the full range of this topic.

Example
This example finds all cells in the range A1:A500 on worksheet one that contain the value 2 and changes it to 5.

With Worksheets(1).R ange("a1:a500")
Set c = .Find(2, lookin:=xlValue s)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

[Denburt]

In VB 6.0, how do you check and see if Excel.exe is already running? I would like to do this in my application but do not know the syntax. I want to check and see if Excel.exe is running before I start any of my own logic.

Lisa this should be a thread of it's own although I understand why you posted it here this is called hijacking a thread and is a big no-no.

To answer your question though I am pretty sure it is the same I did a few quick searches to be sure and found this thread. Hopefully it helps.

http://www.thescripts.com/forum/thread168253.html

[NeoPa]

Nicely done Neo and thanks, that will help me tremendously in the future as I code between the two constantly. I just finished a MAJOR macro in Excel I wanted to do it in Access and store the data but they want everything reformatted and totaled in Excel. I think the amount of data they want to rearrange will be to much for excel so that will be interesting. These tips will definitely add to that project.

I hope this helps you as well Robin and thanks for asking this question it will help prevent future hazards here at my workplace for sure.

Again thanks,
DB

Firstly, I need to catch up with all these posts since Friday as I was away.

Den,
I'm very pleased this was helpful. I do almost as much work in Excel as I do in Access so if you have any queries on that score, please feel free to ask (be sure to bring my attention to any thread as I don't get to go through them all as a matter of course any more I'm afraid).
I don't do much in App Automation mind, but Excel is an area of expertise you could say.

On to the other comments ;)

[NeoPa]

Denburt and Neo:
Thanks, explictly calling appExcel for each command (or using With) worked great in the debug code, but in the original code as I put it piece-by piece back together, it looks like I'm stuck on the following line:

iTargetColumn = Selection.Cells.Find(what:=dDate, after:=ActiveCell, lookin:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext).Column

where iTargetColumn is an integer, and dDate is set as Date, to correspond with the first row of the workbook (which is consecutive dates). I put iTargetColumn in the code as:

with appExcel
.Cells(1, iTargetColumn).Activate

When I put this in the debug code, this is where it is causing the Excel process to remain open. I'm not sure if within that line of code how to explicitly call appExcel.

Sorry to put you guys through the ringer! You're helping me out a ton.

Robin

Robin,

You did such good work before in stripping out unnecessary code. Here we need some context to know what you're talking about (I certainly do). If DenBurt's last post doesn't get you sorted, you'll need to repost this info so that we (at least I) can understand what you're talking about.

Good luck.

[Denburt]

Robin,

You did such good work before in stripping out unnecessary code. Here we need some context to know what you're talking about (I certainly do). If DenBurt's last post doesn't get you sorted, you'll need to repost this info so that we (at least I) can understand what you're talking about.

Good luck.

Rereading this I thought of something that may be useful. This is off the top of my head so I apologize for any errors. Instead of selecting then finding formating etc. Use something like the following:

with appExcel 'you may need to specify a worksheet or workbook or both
      iTargetColumn = .Cells.Find(what:=dDate, after:=[B]appExcel.Cells(1,1)[/B], lookin:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext).Column

Hope this helps some.

[RobinAG]

Hi guys

I just got back to working on this, and I had to go through everything in my code to see if your suggestions would work with everything in it. Sorry it took a while.

So I started putting my code back together based off of the stripped down version. Denburt's suggestion helped, I changed the find method to search for Range, instead of cells. But as I put the code together, it looks like I'm having difficulty now when I try to loop through my different recordset selections.

Basically, I pull up a recordset that has 3-5 different names (see rst.Fields("Nam e") below). I run this code placing the word "Test" in a column corresponding to the date and row corresponding to the name. If the row is not named in the first column, the rst Name goes in there. When it runs through the code once, Excel seems to close fine. But once it loops through, the process doesn't close. Is somehow my loop not complete so that it's leaving something open?

[Code=vb]starget = "C:\test"
Set appExcel = CreateObject("E xcel.Applicatio n")
Set wbk = appExcel.Workbo oks.Open(starge t)
Set wks = wbk.Worksheets( 1)
With appExcel
.ScreenUpdating = False
.Visible = False
.Range("A1").Ac tivate

Do Until rst.EOF
sTargetName = rst.Fields("Nam e").Value

iTargetColumn = wks.Range("A1:z 1").Find(dDate) .Column
iTargetRow = 3
Do
If sTargetName = .Cells(iTargetR ow, 1).Formula Then
If .Cells(iTargetR ow, iTargetColumn). Formula = "" Then
.Cells(iTargetR ow, iTargetColumn). Formula = "Test"
Exit Do
Else
iTargetRow = iTargetRow + 1
End If
ElseIf .Cells(iTargetR ow, 1).Formula = "" Then
.Cells(iTargetR ow, 1).Formula = sTargetName
.Cells(iTargetR ow, iTargetColumn). Formula = "Test"
Exit Do
Else
iTargetRow = iTargetRow + 1
End If
Loop
rst.MoveNext
Loop
End With

wbk.Save
wbk.Close
appExcel.Visibl e = True
appExcel.Screen Updating = True
appExcel.Quit[/code]

[Denburt]

See post 17 it appears that Neo managed to nail it down in that post....

O.K. read his post but simply reverse the following lines in your code in both places:

.ScreenUpdating = True
.Visible = True

Hope that helps. :)

[RobinAG]

Yes! It works, wonderful. But...

...my original code calls a separate function in the Do, instead of:

.Cells(iTargetRow, iTargetColumn).Formula = "Test"

it does the following function, which places and formats the cell in the appropriate way. It's not a lot, so I could do without, but this same code structure is used at different places in the database, which perform more complex methods, and I have the same problem closing the excel process there. Any ideas here?

Public Function MeetingPlacer(wks As Worksheet, iTargetRow As Integer, iTargetColumn As Integer)
wks.Cells(iTargetRow, iTargetColumn).Activate
With ActiveCell
    .BorderAround LineStyle:=xlContinuous
    .Font.Size = 9
    .Font.Name = "Arial Narrow"
    .Font.Color = RGB(255, 255, 255)
    .Interior.Color = RGB(0, 0, 0)
    .Formula = "Meeting"
End With

End Function

[Denburt]

Hey glad were moving along... :)

Lets start here:
wks As Worksheet O.K. where is the app? Get that and you should be good.

[NeoPa]

Yes! It works, wonderful. But...

...my original code calls a separate function in the Do, instead of:

.Cells(iTargetRow, iTargetColumn).Formula = "Test"

it does the following function, which places and formats the cell in the appropriate way. It's not a lot, so I could do without, but this same code structure is used at different places in the database, which perform more complex methods, and I have the same problem closing the excel process there. Any ideas here?

Public Function MeetingPlacer(wks As Worksheet, iTargetRow As Integer, iTargetColumn As Integer)
  wks.Cells(iTargetRow, iTargetColumn).Activate
  With ActiveCell
    .BorderAround LineStyle:=xlContinuous
    .Font.Size = 9
    .Font.Name = "Arial Narrow"
    .Font.Color = RGB(255, 255, 255)
    .Interior.Color = RGB(0, 0, 0)
    .Formula = "Meeting"
  End With
End Function

Robin,

You're doing a fairly good job of complying with our requests, which makes our job less of a strain. Could I ask you to remember to use the &#91;CODE] tags in future when you post code. This saves us from redoing all your posts and will give us more time to help you and others like you with their real problems.

As to your ongoing problems, I would like to help more but I confess I still don't seem to have from you a clear indication of what's happening where. I will offer some suggestions, but without more clear indication of what's what I can't direct my answer very well.

Firstly, in case your problem is connected with the earlier issue, I considered taking out the :

ScreenUpdating = False
and
ScreenUpdating = True

... lines completely. If this doesn't effect the performance adversely then it should certainly not have any other effect. It's definitely code worth using within Excel normally, but if the application window is already hidden it may not have any effect.

As to your function, try using :

Public Sub MeetingPlacer(wks As Worksheet, _
                         iTargetRow As Integer, _
                         iTargetColumn As Integer)
  With wks.Cells(iTargetRow, iTargetColumn)
    'Call .Activate
    Call .BorderAround(LineStyle:=xlContinuous)
    .Font.Size = 9
    .Font.Name = "Arial Narrow"
    .Font.Color = RGB(255, 255, 255)
    .Interior.Color = RGB(0, 0, 0)
    .Formula = "Meeting"
  End With
End Sub

I've changed it from a Function to a Sub as you don't seem to be providing a return value anyway.
It doesn't activate the cell it's working on. If that's required simply uncomment line #5.