MS Access Compile Error

NewPadawan

New Member
Joined
Dec 1, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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
VBA Code:


This is where I call the Function:
VBA Code:
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
VBA Code:
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,222,103
Messages
6,163,953
Members
451,867
Latest member
csktwyr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top