[AccessD] Access Function To Create Spreadsheet Fails On 2nd Run

I used the code and I get the Excel Running message, the basis of my code is

Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook 

Set objExcel = New Excel.Application
Set objWB = objExcel.Workbooks.Open(strProd) ' strProd is a string
containing the path to the workbook template.

objExcel.Workbooks.Close
Set objWB = Nothing
        
objExcel.Application.Quit
Set objExcel = Nothing

And I have really tried every way I could think of of closing the workbook
and the application.  Anyone know if I am closing the workbook and the
application incorrectly..

Paul Hartland

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: 07 December 2005 20:22
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access Function To Create Spreadsheet Fails On 2nd
Run

I usually get error 1004 when I am trying to open an Excel workbook that is
already open. Check and make sure the workbook really is closed. Run the
following script after you run the first function but before the YTD
function to see if the Excel workbook is still open. HTH Jim Hale

Dim objXL
	Dim strMessage
	
	On Error Resume Next
	
	'	Try to grab a running instance of
	'	Excel...
	Set objXL = GetObject(, "Excel.Application")
	
	'	What did we find?..
	If Not TypeName(objXL) = "Empty" Then
		strMessage = "Excel Running."
	Else
		strMessage = "Excel Not Running."
	End If
	
	'	Feedback to user...
	MsgBox strMessage, vbInformation, "Excel Status"
	
	'	Make the Excel instance visible 
	'	if we found one
	if strMessage = "Excel Running." then _
				objXL.Visible = true 


-----Original Message-----
From: Josh McFarlane [mailto:darsant at gmail.com]
Sent: Wednesday, December 07, 2005 1:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access Function To Create Spreadsheet Fails On 2nd
Run


On 12/7/05, Paul Hartland (ISHARP) <paul.hartland at isharp.co.uk> wrote:
> To all,
>
> I think I may have a siilar problem to that post by Lonnie a couple of
weeks
> back, I have a function that creates an Excel sheet which I have to 
> call twice, once for last weeks figures and once for year to 
> date.....The first run works without a hitch (and I close all 
> references to Excel objects
etc),
> on the second run I get the error message:
>
>         Run-time error '1004';
>         Method 'Sheets' of object '_Global' failed

Is _Global a global variable?

--
Josh McFarlane

"Peace cannot be kept by force. It can only be achieved by understanding."
-Albert Einstein
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or entity
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of or
taking action in reliance upon this information by persons or entities other
than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email, you are
responsible for screening its contents and the contents of any attachments
for the presence of viruses. No liability is accepted for any damages caused
by any virus transmitted by this email.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com