[AccessD] Excel process hangs

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________



Hi Cheser,

When you say the process is still running, do you mean the Excel Application is still running, or the something else?

You can make you code a bit sharper if you are interested. I also recommend setting the workbook as well as the app.  Code not tested.
'--------------------------------------------------------------------------------------------------------------------
Dim objExcelApp As Object
Dim objExcelWB as Excel.workbook

Set objExcelApp= CreateObject("Excel.Application")

With objExcelApp
	.WorkBooks.Open "C:\Files\Excel\NOAA Data.xls"
	.Visible = False ' Do you need to see this happen at all?  Set to true if you do.
End with

Set objExcelWB = objExcelApp.ActiveWorkbook

With objExcelWB 
	.Sheets("Sheet1").Range("A1").QueryTable.Refresh
	.Run "Macro1"
	strSql = "DELETE * from [tbl Temperature];"
	DBEngine(0)(0).Execute strSql, dbFailOnError
	DoCmd.TransferSpreadsheet acImport, 8, "tbl Temperature", "c:\files\excel\NOAA Data.xls", True, "Sheet3!a1:b32767"
	.Close True ' This will close and save the workbook without prompts
End with

Set objExcelWB = Nothing
objExcelApp.Quit
Set objExcelApp = Nothing

'----------------------------------------------------------------

Cheers
Darryl
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Friday, 4 June 2010 6:10 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Excel process hangs

I have the following module that opens Excel does some work and then closes Excel. Problem is that even though Excel closes the process is still running. What am I missing on killing the process. Thanks.

Function GetTemperature()

Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.WorkBooks.Open "C:\Files\Excel\NOAA Data.xls"
xl.Visible = True
xl.Sheets("Sheet1").Select
xl.Range("A1").Select
xl.ActiveCell.QueryTable.Refresh
xl.Application.Run "Macro1"
strSql = "DELETE * from [tbl Temperature];"
DBEngine(0)(0).Execute strSql, dbFailOnError
DoCmd.TransferSpreadsheet acImport, 8, "tbl Temperature", "c:\files\excel\NOAA Data.xls", True, "Sheet3!a1:b32767"
xl.DisplayAlerts = False
xl.Save
xl.DisplayAlerts = True
xl.Quit
Set xlWorkBooks = Nothing
Set xl = Nothing


End Function

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

 
No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________