asked on

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Maybe:

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

Anders Ebro (Microsoft MVP)

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 Lambert

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.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Anders :-)

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Not sure how the Space disappeared ... cut & paste I guess

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

shieldsco

ASKER

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 Lambert

My comment wasn't about a solution, but about good practices ....

shieldsco

ASKER

Thanks

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION