Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get ProcessID for Excel Instance started by Access Session

Posted on 2012-09-13
12
Medium Priority
?
2,368 Views
Last Modified: 2012-09-13
Is there some way I can retrieve the ProcessID for an instance of Excel started within an Access Session?
for instance, in the code below I would be looking for the code for function GetExcelProcessID:

Dim xls As Excel.Application
Set xls = New Excel.Application
x = GetExcelProcessID(xls)
0
Comment
Question by:shambalad
  • 6
  • 4
  • 2
12 Comments
 
LVL 6

Accepted Solution

by:
fhlio_admin earned 2000 total points
ID: 38397466
this works in a standard module for me....

Option Compare Database
Option Explicit

Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
     
Function Q942058()
Dim xlApp As Excel.Application, lpdwProcessId As Long

Set xlApp = New Excel.Application
xlApp.Visible = True

GetWindowThreadProcessId xlApp.hwnd, lpdwProcessId
Debug.Print lpdwProcessId

Stop
xlApp.Quit

End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38397482
if this have something to do with your previous question that is giving you error, try this codes

Private Sub LoadTPR(strFile As String)
      Const strProcedure As String = "LoadTPR"
      Dim xlObj as Object

      Dim strCsv As String
    On Error GoTo ErrorHandler
 
    strCsv = CurrentProject.Path & "\TPR.csv"
      ' Delete csv file
    On Error Resume Next
    Kill strCsv
    Err.Clear
    On Error GoTo ErrorHandler
	set xlObj=createobject("excel.application")
		xlObj.workbooks.open strFile, False, False
		xlObj.visible=true  ' if you don't want to see the excel application, just comment this line
		
			with xlObj
				.worksheets(1).select
				.columns("L:L").numberFormat="0"
				.columns("P:P").numberFormat="0"
				.columns("O:O").numberFormat="0.00"
				.columns("M:N").numberFormat="0.000000"
			    .activeworkbook.saveas strcsv,FileFormat:=6, createbackup:=false
				.activeworkbook.saved=true
			end with
			
			xlObj.quit
			set xlObj=nothing
			
   DoCmd.SetWarnings False
   DoCmd.TransferText acImportDelim, "TPR Import Specification", "tblTPR", strCsv
	DoCmd.SetWarnings True

ErrExit:
		xlObj.quit
		set xlObj=nothing
        exit sub

ErrorHandler:
   DoCmd.Hourglass False
   Debug.Print strProcedure & " Error: " & Err.Description & " (" & Err & ") on line " & Erl
   Err.clear
   Resume ErrExit
End Sub

Open in new window

0
 
LVL 7

Author Comment

by:shambalad
ID: 38397497
I'm at home now, working in Access 2010 on a Windows 7 Ultimate platform. Having trouble finding GetWindowThreadProcessId. Is there some reference I should be adding to the db?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38397502
GetWindowThreadProcessId was declared on the 4th line of code that I gave.  It is embedded in Windows...

I use Windows 7 Home Premium... Access 2010... I included all the code in my standard module.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38397513
Yes I included the declare, but Access isn't finding it. Won't compile(?). I am running 64 bit.
0
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38397515
what does the error message say?
0
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38397525
As a matter of note, I am running 64 bit as well...
0
 
LVL 7

Author Comment

by:shambalad
ID: 38397528
I tried compiling again. Got the error. Copied the code into a separate module so I could get a better snapshot to post, and it now compiled. I'm doing some runs right now with more than one instance of Excel in the stack. Will get back to you in a couple of minutes.
0
 
LVL 7

Author Comment

by:shambalad
ID: 38397543
Works great, fhlio_admin. Let me wrap some things up here then I'll award the points and close this out in the next couple of minutes.
Thank you very much
0
 
LVL 7

Author Comment

by:shambalad
ID: 38397610
Capicorn1: I guess you could say this question is tangentially related to the earlier question. That code code is working; but I continued to dig in since I was trying to figure out why the code would work on the first iteration and not for any subsequent tries. That, in turn, led me to working on managing multiple instances of Excel in the stack.
I'm looking at ways to ensure that any instances of Excel started within an Access session are explicitly ended when I'm done with the instance. I've found that quitting the application ('xlsApp.Quit') works pretty well, but I want to cover my bases.
The intent behind getting the ProcessID is to be able to kill the specific Excel instance.
For some time I've had the following KillExcel sub in my toolbox:

Public Sub KillExcel()
   Dim s As String
   On Error Resume Next
   s = "TASKKILL /F /IM Excel.exe"
   Shell s, vbHide
   On Error GoTo 0
End Sub

But that sub is a little ham-fisted. Users often have multiple workbooks opened up on the desktop along with the Access database, and I can't have my apps indiscriminately blowing their opened workbooks out of the water. Knowing the ProcessID allows me to modify the sub as follows:

Public Sub KillExcel(Optional lngPID As Long = 0)
   Dim s As String
   On Error Resume Next
   If Not lngPID = 0 Then
      s = "TASKKILL /pid " & lngPID
   Else
      s = "TASKKILL /F /IM Excel.exe"
   End If
   Shell s, vbHide
   Err.Clear
End Sub

Now, so long as I know the ProcessID, I can surgically force the correct instance of Excel off the stack.
0
 
LVL 7

Author Closing Comment

by:shambalad
ID: 38397611
fhlio_admin: Thank you very much.
Todd
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38397654
Todd,
test the codes i posted and  see if the you still get the error you were getting before.
i been using the same coding in my app and never had a problem.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question