MS Access Compile Error

[NewPadawan]

Hello All,

Quite new to VBA in MS Access, and I've got the following code which checks for open file before executing the remaining code. The issue is, when I run the code without the IsFileOpen portion, everything works as expected. However when I add in the IsFileOpen portion, I get a compile error " Wrong Number of Arguments or Invalid Property Assignment. Does anyone know why this is occurring, or how to resolve the error?

This is the IsFileOpen Funtion:

Private Sub IsFileOpen()
Dim fileNum As Long
Dim errNum As Long
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
IsFileOpen = errNum
End Select

End Sub

This is where I call the Function:

Dim FileName As String
FileName = "C:\Users\Jennifer.Maley\Desktop\DoNotOpenDCA.xlsx"
If IsFileOpen(FileName) = False Then

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim table_name As String
Dim fld As DAO.Field
table_name = "CycleCountResearchExports"

'Excel objects:
Dim excel_application As Excel.Application
Dim workbook As Excel.workbook
Dim sheet As Excel.Worksheet
Dim excel_file_name As String
Dim sheet_name As String
Dim lastRow As Long
Dim lastColumn As Integer
Dim i As Long
Dim j As Integer

Set db = CurrentDb

Set rs = CurrentDb.OpenRecordset("CycleCountResearchExports")

Set excel_application = Excel.Application
Set workbook = excel_application.Workbooks.Open("C:\Users\Jennifer.Maley\Desktop\DoNotOpenDCA.xlsx")
Set sheet = workbook.Sheets("CycleCountResearch")

With sheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
sheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("E" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("F" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("G" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("H" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("I" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("J" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("K" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("L" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("M" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("N" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("O" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("P" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("Q" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("R" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("S" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("T" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("U" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("V" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("W" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("X" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("Y" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs
sheet.Range("Z" & Rows.Count).End(xlUp).Offset(1, 0).CopyFromRecordset rs

workbook.Save
workbook.Close
excel_application.Quit

'Clean up:
Set sheet = Nothing
Set workbook = Nothing
Set excel_application = Nothing
Set rs = Nothing
Set db = Nothing
MsgBox "Export Successful! Please save, and continue"
Else
MsgBox "Someone else is updating. Please wait a moment and try again."
Exit Sub
End If
End Sub

 

[Micron]

Your sub takes no arguments,
Private Sub IsFileOpen() <<-- change to Private Sub IsFileOpen(strPath As String) or similar.

therefor you cannot pass any argument(s)
IsFileOpen(FileName)

I imagine that the error message makes sense now?
Your code should go within the code tags. You have two empty spots for code and your code is outside of them. The way you have it makes it much harder to read (especially with no indentation) so I'm afraid I didn't read all the rest of it. Probably not relevant to your issue anyway.
Last but not least, AFAIK a sub cannot return a value so I would expect your code to fail anyway. If you want a procedure to return a value
(e.g. If SomeProcedure = False Then...) use a function instead. Not always required but IMO, best practice to declare the return type:

Private Function SomeFunctionNameHere(typeDeclarationsHere) As TypeDeclarationHere or in your case

Private Function(strPath As String) As Boolean

 

[NewPadawan]

Your sub takes no arguments,
Private Sub IsFileOpen() <<-- change to Private Sub IsFileOpen(strPath As String) or similar.

therefor you cannot pass any argument(s)
IsFileOpen(FileName)

I imagine that the error message makes sense now?
Your code should go within the code tags. You have two empty spots for code and your code is outside of them. The way you have it makes it much harder to read (especially with no indentation) so I'm afraid I didn't read all the rest of it. Probably not relevant to your issue anyway.
Last but not least, AFAIK a sub cannot return a value so I would expect your code to fail anyway. If you want a procedure to return a value
(e.g. If SomeProcedure = False Then...) use a function instead. Not always required but IMO, best practice to declare the return type:

Private Function SomeFunctionNameHere(typeDeclarationsHere) As TypeDeclarationHere or in your case

Private Function(strPath As String) As Boolean

@Micron That completely makes sense. Thank you for the response. Will Adjust accordingly, thank you very much.

 

[Micron]

You're welcome.