Excel(lent) Ignorance...

[rpeare]

Ok, I do not deal with excel files very often and I just can not figure out why an instance of excel is not closing properly on my machine. If I run the code below the instance of excel opens and closes correctly (nothing is in my processes window in task manager). If link the sheets, then delete them, the instance of Excel remains open after the code is done running (the two DOCMD lines). I have tried various methods of:

.activeworkbook.close False
.activeworkbook.close

etc but nothing I do seems to close the instance of excel after I've linked a worksheet into my access database. If anyone has encountered this or knows how to properly close the instance of excel after a link has been formed then deleted I would greatly appreciate some insight.

sFileName = Lst_Files.Column(1) & "\" & Lst_Files.Column(0)

Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open (sFileName)
    For i = 1 To .Worksheets.Count
        sSheetName = .Worksheets(i).Name
        'DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "CurrentTab_Link", sFileName, True, sSheetName & "!"
        '****** I AM PERFORMING OPERATIONS BETWEEN THESE TWO LINES ***********
        'DoCmd.DeleteObject acTable, "CurrentTab_Link"
    Next i
    .Quit
End With
Set xlApp = Nothing

[redbull]

Hmm

Shell "TaskKill /F /T -IM EXCEL.EXE"

That will close excel forcefully. Will close EVERY excel.exe on the machine though... So, it might work in your situation it might not. I have always felt this method is like using a chainsaw to cut a sandwich in half.. but it works

[rpeare]

Yeah that's exactly what I'm trying to avoid because the person that will be running this code on their machine might, in all likelyhood, have multiple instances of excel open already. It's a last resort, thanks.

[redbull]

Not sure, I'm sure someone will be able to assist.

[ssanfu]

A few years ago, I was getting my data from the client in an Excel WB. I also had problems with killing Excel. I think I switched to late binding...

Found this page that might help
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

[rpeare]

Yeah I found that page yesterday before I posted as well but it's not closing the instance of excel either here's the code I was using when I tried that option as well if you can spot a problem I was unable to

sFileName = Lst_Files.Column(1) & "\" & Lst_Files.Column(0)

Set xlApp = CreateObject("Excel.Application")
Set xlFile = xlApp.Workbooks.Open(sFileName, , False)

    For i = 1 To xlFile.Worksheets.Count
        Set xlSheet = xlFile.Worksheets(i)
        sSheetName = xlSheet.Name
        DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "CurrentTab_Link", sFileName, True, sSheetName & "!"
        '****** I AM PERFORMING OTHER OPERATIONS HERE ******
        DoCmd.DeleteObject acTable, "CurrentTab_Link"
        Set xlSheet = Nothing
    Next i
    xlFile.Close False
    Set xlFile = Nothing
xlApp.Quit
Set xlApp = Nothing

Though it has the same effect (linking all the sheets in order) the instance of excel is still not closed if I link, then remove the link from my access database.

[redbull]

I am going to suggest something that I don't know how to do but seems "plausible"


So I am wondering if it COULD/WOULD be possible to identify the current Process ID that this version of Excel is running.....

Declare Function GetCurrentProcessId Lib "kernel32" () As Long

and we can confirm the correct PID is given BY.....

MsgBox("Excel PID is " + CStr(app.Run("GetCurrentProcessId")))

So what does this do.... well you remember the chainsaw????
Shell "TaskKill /F /T -IM EXCEL.EXE"
Well we can narrow that shell command down to..... a specific Process ID....

Shell "TASKKILL /F /PID XXXX"

Now if there is a way to put ...

Shell "TASKKILL /F /PID "GetCurrentProcessId""
or not I am 100% unsure... but might be another way of looking at it...

[rpeare]

This might be just what I want, what reference do I need to for the kernel32 declare function? The only API I can find in my list is for windows messenger.

[redbull]

From what I am finding reference is for COM libraries and Kernal32 is NOT a com library.. Still searching. Were you able to implement any of this? Please keep me in the loop.

[rpeare]

it's failing on the declare function and the most obvious reason seems to be a missing reference which I can't locate in my list so I'm wondering if I've got to install the reference as well, I'm still looking as well.

[rpeare]

hrm maybe I'm declaring it incorrectly. I'm putting the declare function in the code for my form (this is going to be a UI for someone) though none of what I'm reading says that you'd have to declare it as a public function and it should work as I have it.

[redbull]

Okay, so I started doing some testing... I created a new module in my exsisting application... Paste this at the top...

Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Sub GetPID()
MsgBox GetCurrentProcessId
End Sub

Then run it.. mine came back with the 4 digit PID, right on the money.

I tried the one I post before, it failed for me too..

[rpeare]

Yep you're right, so it's not a missing reference it just doesn't seem to want to run as part of my code running my form, but I can work around that, I'll get back to you if I get it working the way I want.

thanks

[redbull]

I don't mean to pat myself on the back... But

[rpeare]

I'm having such a mental block with this, I just can not seem to get it working trying to detect external processes, getting the current processID detects the access applications' ID which I don't care about I just can not seem to get the PID for the excel file I have opened with my code.