Function ByRef argument type mismatch

[Pendragon]

Access03/WinXP

I am using the function IsFileOpen(filename as string) to determine if the
file "c:\datafiles\TextExport.xls" is open before continuing with a process.
If I use the following in my routine, the function performs properly.

If IsFileOpen("c:\datafiles\TestExport.xls") = True Then
etc. etc.

However, if I first assign a string variable to the file and then use that
variable in the function call, I get a ByRef Argument Type Mismatch error
message. Why does this happen and is there a way to resolve it other than
using the literal path and file name (which I am not against using)?

If IsFileOpen(strFileName) = True Then
etc. etc.

FAILS!!

Thanks.

 

[Klatuu]

Try this:
If IsFileOpen(ByVal strFileName As String) Then

 

[Pendragon]

When I modified my If..Then statement to your suggestion, that line went red.
I also tried If IsFileOpen(ByVal strFileName as String) = -1 and also ...=
True; both times the line went red.

 

[Stephen Lebans]

It works here with A2003/WinXP Pro.

What is the code behind the IsFileOpen function you are using. I
GoogleGroups to find mine. Here is the code behind the CommandButton I used:

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer


On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.


' Check to see which error occurred.
Select Case errnum


' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False


' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True


' Another error occurred.
Case Else
Error errnum
End Select
End Function




Private Sub cmdIs_Click()
On Error GoTo Err_cmdIs_Click

Dim s As String

s = "c:\Test.xls"

'If IsFileOpen("c:\Test.xls") = True Then
If IsFileOpen(s) = True Then
MsgBox "file Open"
Else
MsgBox "File Closed"
End If

Exit_cmdIs_Click:
Exit Sub

Err_cmdIs_Click:
MsgBox Err.Description
Resume Exit_cmdIs_Click

End Sub


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Klatuu]

I'm sorry. I total spazed this one. The code should actually be in the
IsFileOpen function.

Function IsFileOpen(ByVal strFileName As string)...

 

[Pendragon]

I copied the code from the MSDN #138621 which looks like your code:

Function IsFileOpen(FileName As String)
Dim FileNum As Integer, ErrNum As Integer

On Error Resume Next
FileNum = FreeFile()
Open FileName For Input Lock Read As #FileNum
Close FileNum
ErrNum = Err
On Error GoTo 0

Select Case ErrNum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
Case Else
Error ErrNum
End Select

End Function


I then use this in my code:

stFileName = "c:\DataFiles\TestExport.xls"
....
If IsFileOpen(stDocName) = True Then
....
End If

VBA accepts the language syntax but when the code is run, the mismatch error
kicks in. If I try to use Dave's suggestion, the code line goes red. I've
double and triple checked to make sure I have stFileName defined as string in
my Dim statements (check the obvious!), and made sure all of my function
calls and variable calls are typed correctly. I'm at a loss.

I have many other functions in play elsewhere in my database, including the
fIsFileDir and fIsAppRunning which on found on the MSDN pages. They all run
without issue.

Ideas?

 

[Douglas J. Steele]

How have you declared strFileName?

It should be

Dim strFileName As String

Note that if you've got

Dim strFileName, strFolderName As String

only strFolderName is declared as a string: strFileName is declared as a
variant.

You should be able to get by the problem using

If IsFileOpen(CStr(strFileName)) = True Then

but you really should correct the declaration.

 

[Pendragon]

What a revelation!! I had indeed defined strFileName as String, but doing so
in the same line as other variables, e.g.,:

Dim strFileName, MySQL, SomeOtherVariable as String

When I removed strFileName from this line and gave it its own Dim statement,
the process worked without error. I had absolutely no idea that the variable
was not being set as String but as you said, Variant.

Which then moves the question - in writing Dim statements, should you not
place variables of the same type in the same line, as in my example above?
Or is the implication from your solution that every variable should have its
own Dim statement?

Thank you very much!!

 

[Douglas J. Steele]

Unlike some other languages, VBA doesn't support "short circuiting" in
declarations.

Every variable must have a type associated with it, or else it's declared as
a variant.

Dim strFileName, MySQL, SomeOtherVariable as String

means strFileName and MySQL are both variants. You must use either

Dim strFileName As String, MySQL As String, SomeOtherVariable As String

or

Dim strFileName As String
Dim MySQL As String
Dim SomeOtherVariable As String

to make all three strings.