[AccessD] Setting objects to nothing

Re your comment about 14 and y: this strongly suggests that y is not being
set to the correct value in the first place (or is being altered later on).
In any case, where the number of elements in an array is (potentially) in
doubt I always use..

For x = lBound(SomeArrayName) to uBound(SomeArrayName)

... The next thing that confuses me (very easy to do!) is...

If blState = True Then .ActiveWorkbook.SaveAs FileName:=strFileSave

.ActiveWorkbook.Close SaveChanges:=False
End With

I'm not sure why you are trying to save the workbook to some specific file
name, but I assume that your code has changed the excel file that was opened
and you want to then save it to a *new* file with a different path/name. If
not, then why does the next line close the workbook without saving the
changes?

For what it's worth here is how I open and close excel workbooks...

I have a routine that opens files invisibly...

**************code starts *******************

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 in to the procedure 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 your own error logging/reporting routine
    ReportError Err.Number, Err.Description, "Excel_OpenWorkBookHidden",
"Excel_mod", "File Name=" & Path
    Set Excel_OpenWorkBookHidden = Nothing
    Resume Excel_OpenWorkBookHidden_exit
End Function

And another one for opening them and show them on screen...


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 your own error logging/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 for closing the workbook I use...

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
    End If
    If Err.Number <> 0 Then Exit Sub
    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 is the isDirectory function...
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

And the FieExisit 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

**************code ends *******************

Now these routines do not allow you to open one file and save it under a
different name, but then I would instead copy the file to a new name first,
open the copy and change it any way I want, and then close the copy, saving
the changes.

One other comment, you have...

 If blState = False Then GoTo Err_Load_Financials

<soap box mode> 
The only time I ever need to use Goto is in setting up an error handler,
because that's the only way you can do it in VB/VBA. Otherwise I've never
needed to code anything using Goto. Plus, in your code it looks like the
Goto target really is an error handler, which means that if blState IS false
and your code jumps there, you will get another error when the Resume line
executes, because you did not enter the error handler legally (by raising an
error).
</soap box mode> 

Hope some of this helps.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Wednesday, October 05, 2005 4:42 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Setting objects to nothing


Here is the bottom part of the function. BTW if I change "For x = 1 To y" to
"For x = 1 To 14" Excel seems to go away. Strange Jim Hale

If blState = True Then .ActiveWorkbook.SaveAs FileName:=strFileSave

.ActiveWorkbook.Close SaveChanges:=False
End With
 If blState = False Then GoTo Err_Load_Financials
If Err.Number <> 0 Then
    Load_Financials = False
Else
    Load_Financials = True
 End If
fm.lblFilename.Visible = False

The_End:
    Set fm = Nothing
    For x = 1 To 14 'y
        If Not (rs(x) Is Nothing) Then rs(x).Close: Set rs(x) = Nothing
    Next x
    
    If Not (dbs2 Is Nothing) Then dbs2.Close: Set dbs2 = Nothing
    Set Wsheet = Nothing
    appExcel.Quit
    Set appExcel = Nothing
     If Excel_is_running = True Then MsgBox "Excel still running"
    
DoCmd.Hourglass False
Exit Function

Err_Load_Financials:
    Load_Financials = False
    If Not Err.Description = "" Then MsgBox Err.Description
    Resume The_End
End Function

-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com]
Sent: Wednesday, October 05, 2005 3:09 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Setting objects to nothing


What's the code for setting the Excel instance, and the code you are using
to close it?

Lambert