[AccessD] Excel process hangs

Stuart McLachlan stuart at lexacorp.com.pg
Fri Jun 4 17:33:31 CDT 2010


Looks like a variation on the  old "unqualified reference"
http://support.microsoft.com/kb/319832

<quote> When you write code to use an Excel object, method, or property,
you should always precede the call with the appropriate object variable.
If you do not, Visual Basic establishes its own reference to Excel.
</quote>

Looking at the Run command in Excel Help, I found:
<quote>If a string is used, the string will be evaluated in the context of the active 
sheet.<quote>  

So as a WAG, you may need something like:
xl.Application.Run xl.ActiveSheet.Macros("Macro1')

-- 
Stuart

On 4 Jun 2010 at 15:32, Kaup, Chester wrote:

> Problem has been solved for now. Seems that when access runs the excel
> macro it open another instance of Excel. Ended up killing the process
> with the following. 
> 
> 
> Dim RetVal
> RetVal = Shell("taskkill /IM EXCEL.EXE /f")
> RetVal = Shell("tasklist")
> 
> Thanks for the help.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
> Sent: Thursday, June 03, 2010 7:11 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [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.
> _______________________________________________________________________________________
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 






More information about the AccessD mailing list