Access Compile Error Type Mismatch

I'm using the following and I get a type mismatch error on line -  Shell "explorer " \ strFolder & "\" & strFileName


 Dim strFileName As String
 Dim strFolder   As String


 Dim Msg, Style, Response
    Msg = "Mail Merge Successful, would you like to view your completed files?"
    Style = vbYesNo
   
    'MsgBox Me.fraOutput.Value
    Call startMerge(Me.fraOutput.Value)
    
    Response = MsgBox(Msg, Style)
    If Response = vbYes Then
    strFolder = CurrentProject.Path & "\Word Merge\completed"
    strFileName = Dir(strFolder & "\" & "*MailMergeFileSingle*.PDF")
   
   
    Shell "explorer " \ strFolder & "\" & strFileName
    End If
End Sub

Open in new window

shieldscoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Maybe:

Shell "explorer" & "\" &  strFolder & "\" & strFileName
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
In this line:
Shell "explorer " \ strFolder & "\" & strFileName

Open in new window

You are missing quotatin marks around the first \. \ just happens to be the sign to use for "Integer division", and thus we get the type mismatch, as "Explorer" is not an integer.
I think Joe is missing a space in his reply, it should probably be:
Shell "explorer " & "\" &  strFolder & "\" & strFileName 

Open in new window

Fabrice LambertConsultingCommented:
Hi,

Additional notes:
I can't stress this enough, avoid using the Dir() function, as it store its criteria in an hidden global scope, thus can't be reliable.
Consider the following code:
Public Sub test()
    Dim criteria As String
    criteria = Environ("TEMP") & "\*.tmp"
    
    Dim fileName As String
    fileName = Dir(criteria)
    While fileName <> vbNullString
        Debug.Print fileName
        myFunction Environ("TEMP") & "\" & path
        fileName = Dir
    Wend
End Sub

Open in new window

At first, the Test function list all tmp files in the temp folder.
Bad luck, you call myFunction that does interresting stuffs, was written by one of your friend, co-worker, or picked on the internet in a library (in other words: You're not supposed to touch it) and your Test function is now listing only one file.
Why this behavior ?

Answer: myFunction was writen as follow:
Public Sub myFunction(ByVal path As String)
        '// check for file existance
    Dir path
        '// other interresting code
        '// ...
        '// ...
        '// ...
End Sub

Open in new window

It change the Dir function's criteria, invalidating those you've set in the first place, ehence the buggy behavior you obtain with the Test function.

Solution:
Use the FileSystemObject library when working with the file system, as it is designed specifically for this purpose.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Anders :-)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Not sure how the Space disappeared ... cut & paste I guess

Shell "explorer " & "\" &  strFolder & "\" & strFileName
shieldscoAuthor Commented:
Two of the suggested solutions did not work. Although I did not receive an error message the file did not open. In the cans of Fabrice Lambert's suggestion I did not test since the comments were not clear. Any other suggested  solutions?
Fabrice LambertConsultingCommented:
My comment wasn't about a solution, but about good practices ....
shieldscoAuthor Commented:
Thanks
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK .... if this:
Shell "explorer " & "\" &  strFolder & "\" & strFileName

is not working, then something must be wrong with either strFolder and/or strFileName

    strFolder = CurrentProject.Path & "\Word Merge\completed"
    strFileName = Dir(strFolder & "\" & "*MailMergeFileSingle*.PDF")

For example ... if I put this in the VBA immediate window:

?CurrentProject.Path
C:\$$AccessDB

and then
Dir(CurrentProject.Path)
returns ""
whereas

?CurrentProject.Path
C:\$$AccessDB


Wait a minute ... why do you even have the first  "\" after Explorer ?

This is what that looks like (emulating what you have) in the VBA immediate window

?"explorer " & "\" & strFolder & "\" & strFileName
explorer \C:\$$AccessDB\Word Merge\completed\
                ^  ??

Try this:

Shell "explorer " & strFolder & "\" & strFileName
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
You need to add extra quotes, when your folder or file path contains a space. To include a quote inside a string, it must be doubled up.

Shell "explorer """ & strFolder & "\" & strFileName & """"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Thanks Andres your solution worked.... Joe received a compile error, however still awarding you points based on your effort. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.