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

I think that your problem is something to do with the fact that you are
using New Excel.Application instead of the CreateObject function that MS
recommend be used for Office Automation. Though I'm not certain, it's
probably also because you are not doing anything with the Excl object's
UserControl property, here is what help has to say on that...

"
UserControl Property

True if the application is visible or if it was created or started by the
user. False if you created or started the application programmatically by
using the CreateObject or GetObject functions, and the application is
hidden. Read/write Boolean.

Remarks
When the UserControl property is False for an object, that object is
released when the last programmatic reference to the object is released. If
this property is False, Microsoft Excel quits when the last object in the
session is released.

Example
This example displays the status of the UserControl property.

If Application.UserControl Then
    MsgBox "This workbook was created by the user"
Else
    MsgBox "This workbook was created programmatically"
End If
"

Here is the code I use to open Excel files, and I don't have any problems
with it leaving instances behind.

The code uses a few helper functions (IsDirectory etc.) and the code for
those is shown at the end.

First I have a function that opens the workbook as hidden. Useful if you
want to open a file and extract data without showing the worksheet for a
blink of an eye....

Function Excel_OpenWorkBookHidden(Path As String, Optional UpdateLinks As
Boolean = False, _ 
	Optional password As String = "") As Excel.Application

    Dim xlObj As Excel.Application
       
    On Error GoTo Excel_OpenWorkBookHidden_err
    'Check to see if the file name passed into the function is valid
    If IsNull(Path) Or isDirectory(Path) Or Not FileExists(Path) Then
        MsgBox Path & " isn't a valid path!", vbCritical, "Open Excel
Workbook"
        Set Excel_OpenWorkBookHidden = Nothing
        Exit Function
    Else
        Set xlObj = CreateObject("Excel.Application")
    
        xlObj.Workbooks.Open Path, UpdateLinks, , , password
        Set Excel_OpenWorkBookHidden = xlObj
    End If
Excel_OpenWorkBookHidden_exit:
    Exit Function
Excel_OpenWorkBookHidden_err:
'@@@@@@@@@@@@@@@@@@@@@@@
' Replace with a call to your own error reporting routine.
'@@@@@@@@@@@@@@@@@@@@@@@
    ReportError Err.Number, Err.Description, "Excel_OpenWorkBookHidden",
"Excel_mod", "File Name=" & Path
    Set Excel_OpenWorkBookHidden = Nothing
    Resume Excel_OpenWorkBookHidden_exit
End Function

Then, if you want to open the workbook visibly the corresponding function is
simply...

Function Excel_OpenWorkBook(Path As String, Optional UpdateLinks As Boolean
= False, Optional password As String = "") As Excel.Application
    Dim xlObj As Excel.Application
    On Error GoTo Excel_OpenWorkBook_err
    
    Set xlObj = Excel_OpenWorkBookHidden(Path, UpdateLinks, password)
    
    If xlObj.Name > "" Then xlObj.Visible = True
    
    Set Excel_OpenWorkBook = xlObj
Excel_OpenWorkBook_exit:
    Exit Function
Excel_OpenWorkBook_err:
'@@@@@@@@@@@@@@@@@@@@@@@
' Replace with a call to your own error reporting routine.
'@@@@@@@@@@@@@@@@@@@@@@@    ReportError Err.Number, Err.Description,
"Excel_OpenWorkBook", "Excel_mod", "File Name=" & Path
    Set Excel_OpenWorkBook = Nothing
    Resume Excel_OpenWorkBook_exit
End Function

And the all important routine to close the workbook...

Sub Excel_CloseWorkBook(xlApp As Excel.Application, Optional bSaveChanges As
Boolean = False)
Dim wb As Excel.Workbook
    On Error Resume Next
    If xlApp.Name > "" Then
	' here just to force an error if an invalid object is passed in
    End If
    If Err.Number <> 0 Then Exit Sub ' give up
    On Error GoTo 0
        For Each wb In xlApp.Workbooks         'Close all open workbooks
            wb.Close bSaveChanges
        Next wb
    xlApp.UserControl = False
    Set xlApp = Nothing
End Sub


And here are the helper functions...

Function isDirectory(sDir As String) As Boolean
    On Error Resume Next
    isDirectory = (GetAttr(sDir) And vbDirectory) <> 0
    If Err.Number <> 0 Then isDirectory = False
    On Error GoTo 0
End Function

Function FileExists(strFile As String) As Boolean
  ' Comments  : Determines if the file exists
  '             Works for hidden files and folders
  ' Parameters: strFile - file to check
  ' Returns   : True if the file exists, otherwise false
  Dim intAttr As Integer
  Dim errnum As Long
  On Error Resume Next
  'GET THE FILE ATTRIBUTE INSTEAD OF THE LENGTH OF THE FILE NAME
  intAttr = GetAttr(strFile)
  errnum = Err.Number
  FileExists = (Err.Number = 0)
End Function

'===============================
As a brief example of usage...

Sub TestExcel(strFile As String)
' strFile is the full path to an Excel File
  Dim xlObj As Excel.Application
  Dim xlWs As Excel.Worksheet

  Set xlObj = Excel_OpenWorkBookHidden(strFile)
  If Not xlObj Is Nothing Then
      Set xlWs = xlObj.Worksheets(1)
    ' Do stuff with the worksheet object
    Debug.Print xlWs.Cells(1, 2)
    ' Now we are ready to close it
    Set xlWs = Nothing ' release the worksheet object
    ' and then close the workbook object (without saving changes in this
example)
    Excel_CloseWorkBook xlObj
  Else
    MsgBox "Invalid file:" & strFile
  End If
End Sub

HTH


Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
\(ISHARP\)
Sent: Thursday, December 08, 2005 5:36 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access Function To Create Spreadsheet Fails On 2nd
Run


That's exactly the old code I had in place still getting the same
error....but I will try changing it back and running it again with Jim Hales
code in between and see what I get.... 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 08 December 2005 10:19
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Access Function To Create Spreadsheet Fails On 2nd
Run

Hi Paul

No, you may not be closing the workbook properly. Try this:

objWB.Close
Set objWB = Nothing

/gustav

>>> paul.hartland at isharp.co.uk 08-12-2005 09:57:32 >>>
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


--
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