Problems referencing FileSystemObject

ArtDecade · November 16th, 2008, 08:12 PM

I need help w/ a problem I have encountered before....

I am writing an Excel application that will be distributed to non-programmer end-users. Within the project I need to check for the existence of some files on the user's machine. Needless to say, inside the VBA IDE I click on the Windows Script Host Object Model as a library reference, start using the FileSystemObject (yes, Dim fso as FileSystemObject, then Set fso = new CreateObject(blah-blah)) and write the code the find the appropriate file(s).

However, in doing this my experience has been that when the end-user runs the macro he is greeted with an error message about not himself having included the WSHOM as a library reference. I REALLY want to avoid this problem.

So, after some digging I am now playing with the following Declare statement and Sub:

    Public Declare Function FileExists Lib "scrrun.dll" ()

    Public Sub Test()
    dim pathFlNm
    pathFlNm = some legit path and file name string...

    If FileExists(pathFlNm) Then
        MsgBox "Found It!"
        Else
        MsgBox "No luck"
    End If
    End Sub

When I run this sub, however, I get an error message "Can't find DLL entry point FileExists in scrrun.dll"

So, two questions, I guess...

1) Am I even on-target with trying to use a Declare Statement as a way to avoid having my non-programmer end-users having to rummage around in the VBA IDE to get my macro(s) to run?

2) If so, how do I set up the Declare statement and subsequent Subs to work?

Shasur · November 17th, 2008, 02:45 AM

Hi

Can you try the Dir function instead:

Code:

Public Sub Test()
    Dim pathFlNm

    pathFlNm = "c:\temp\sample.txt"

    If Len(Dir(pathFlNm)) <> 0 Then
        MsgBox "Found It!"
        Else
        MsgBox "No luck"
    End If
    End Sub

Cheers
Shasur

C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Old Pedant · November 17th, 2008, 07:22 PM

Use HELP in Access. (Because the HELP in Excel is woefully inadequate! But the fundamentals of VBA are the same in all the Office products.)

Click on "Table of Contents".
Click on "Microsoft Visual Basic Documentation"
Click on "Visual Basic Language Reference"
Click on "Statements"
Click on "A-L"
Click on "Declare Statement"
Read.

Read especially the "Remarks". Including this:

Quote:

quote:
Remarks
...
Empty parentheses indicate that the Sub or Function procedure has no arguments and that Visual Basic should ensure that none are passed.
...

So NOW try using

Code:

Public Declare Function FileExists Lib "scrrun.dll" (path As String) As Boolean

And see if it works.

[Untested by me! Just reading the docs, period!]

Old Pedant · November 17th, 2008, 08:00 PM

The reason I think this might be the problem is the message you go:
Can't find DLL entry point FileExists in scrrun.dll

Excel was clearly able to find "scrrun.dll" and clearly able to scan it for entry points.

So I *think* the problem is that it is looking for a function named FileExists that takes no arguments, as you specified (inadvertently).

It's been years since I worked with VB and used the DECLARE statement in it, but I vaguely recalled the need to match arguments, so...

Hope this works!