Object Variable or with block variable not set - filesystem object on unload

[Movian]

So i have some code that runs on a form unload so that it gets triggered when the database is close

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
CurrentProject.Connection.CommandTimeout = 10
CurrentProject.Connection.Close
CurrentProject.OpenConnection ""
On Error GoTo 0
Dim fso As FileSystemObject
If fso.FileExists(CurrentProject.path & "\Tools\Connected.dat") Then
    fso.DeleteFile (CurrentProject.path & "\Tools\Connected.dat")
End If
On Error Resume Next
CloseForms
End Sub

However i am recieving an error 91: Object Variable or With block variable not set on the line

If fso.FileExists(CurrentProject.path & "\Tools\Connected.dat") Then

I am unable to find an error in the code and am thinking it may be because the form is unloading?

Any thoughts would be apriciated.

[SamT]

Try this

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.FileExists(CurrentProject.Path & blah, blah, blah

[Movian]

Ok Switching to late binding seems to have done the trick! Thank you!

Don't supose you could enlighten me as to why early binding would fail when atempting to utelize a bound variable object on an unload procedure?
While late binding continues to function as expected in this same scenario?

[SamT]

Where's your early binding code?

[Movian]

I may be incorrect but i was under the impression that the following

Dim fso As FileSystemObject

Was considered Early binding and allowed access to things like intellisense for completion as it binds to the external item (FileSystemObject) in the code.

While the following is considered late binding as the variable itself is just an object and gets assigned to the FileSystemOjbect at run time using the set. This also prevents you from using intellisence in your coding.

Dim fso As Object 
Set fso = CreateObject("Scripting.FileSystemObject")

I am familiar with the difference due to my work with office automation where if you add a reference to say Word2007 and use early binding to produce your code, then every machine you install on must use 2007 - if they have 2010 the code will not work as it is looking for the wrong version. However if you use late binding, it binds to whatever office version is installed on the machine and is somewhat version independant (2003, 2007 OR 2010 will work fine).

I was assuming that this was similar and was simply trying to understand why the first version of my code (Which works everywhere else in the system) did not work on the unload event. While the modified code that resembles late binding DID.

Hope that clarifies my confusion and what i was trying to ascertain.

[SamT]

Do you have a reference set to the Scripting Library?

IIRC, it can be set with code allowing Version checking before setting the reference.

I actually haven't worked with a well distributed enough Application that it would be a problem

[Movian]

I do have a reference set to the scripting library.

and our clients have every different version of office you can think of (from 2000 to 365) so it was a major issue for us (and still is as 365 and 2013 seem to have chaned a number of things!!)

[SamT]

Normally, I would autodidact it and help you myself, but not today.

Not to worry, I've called for help.

[Kenneth Hobs]

I don't see why early binding would fail and late binding does not for you. Most programmers like to program using early binding but change the production version to late binding. Late binding is a bit slower but does provide a more generic solution avoiding version errors. When using early binding, the object may be 32bit will the end user's computer may be 64bit so therein lies the problem. Dim is how the early or late binding is set. I have had cases where I used both. Of course you need to use constant values rather than variables for the constants when you convert an early bound object to late binding. Tip: use ?constantname in the Immediate window to get the value of a constant. e.g. ?vbRed and then press Enter key.

When troubleshooting, comment out your On Error Resume Next. While the compiler generally shows the problem line of code, it is not always 100% correct. Step through the code using F8.

I am guessing that you used a public object for CurrentProject. I use Option Explicit as the first line of code in my objects.

For your case, I would not use FSO. Dir() can be used to see if a file or folder exists. Kill() can be used to delete a file.

It should not be needed but most would remove the object from memory at the end of the code. e.g.

Set FSO = Nothing

I don't know what version 365 means. Keep in mind that some things are just going to be changed between Excel versions. e.g. FileSearch(). It was removed in the 2007 Excel version.

Some code that uses API functions will not work in all versions due to the 32bit versus 64bit issue. One can code for both if one really needs to do that.

[snb]

early binding

    with new FileSystemObject 
        if .FileExists(CurrentProject.path & "\Tools\Connected.dat") Then .DeleteFile CurrentProject.path & "\Tools\Connected.dat" 
    End with

or

    Dim fso As FileSystemObject 
    set fso=new filesystemobject 

    If fso.FileExists(CurrentProject.path & "\Tools\Connected.dat") Then  fso.DeleteFile CurrentProject.path & "\Tools\Connected.dat"

[SamT]

Oh, yeah, you can use Compiler Directives; #Const, #If... Then... #ElseIf... #Else so that only the selected code segment is compiled on the User's machine.

'Module level Compiler Constants
#UseFileSearch = (CInt(Application.Version) <= 10)
#Mac = (Application.OperatingSystem = "Apple Version String")

'Sub Code
# If UseFileSearch Then
   'Use Excel 97 to 2003 code
#ElseIf Mac
   'Use Apple Coding
#End If

[Movian]

All really good info,

I will take a look at the suggested changes and let you know how they go.