CODEWITHSUNDEEP
vbaexcel
John Henry
John Henry

Reputation: 13

Argument Not Optional and Wrong number of arguments or invalid property assignment Errors

I would like the following code to run each time the workbook opens. I am getting a couple of errors that you can see in my comments. I'm pretty sure it is a very simple thing that I am missing and I have not been able to figure it out. Thank you for your time!

Public FSO As Scripting.FileSystemObject
Public SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Public FileItem As Scripting.File
Public FolderName As Object

Private Sub Workbook_Open()
'I would like to run this every time the workbook opens. This is where I am getting my Argument Not Optional Error.
Call ListFilesInFolder(SourceFolder)

End Sub

Sub ListFilesInFolder(SourceFolder As Scripting.Folder, Subfolders As Boolean) 'Do I need everything in the parentheses?

Application.ScreenUpdating = False

MsgBox "SOP's Database will update automatically"

Dim r, LstR As Long
Dim SOPRng As Range

Set SOPRng = ActiveSheet.ListObjects("SOPDatabase").Range

ActiveSheet.ListObjects("SOPDatabase").Unlist

SOPRng.ClearFormats

UserName = Environ("UserName")
fPath = "Target File Path"

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(fPath)

    r = 2

    On Error Resume Next
    For Each FileItem In SourceFolder.Files

        Cells(r, 1) = r - 1
        Cells(r, 2) = FileItem.Name
        Cells(r, 3) = FileItem.Type
        Cells(r, 4) = FileItem.DateLastModified
        Cells(r, 6).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"

        r = r + 1

    Next FileItem
I can get up to here to work just fine.  Going into the subfolders below is not working.  I am getting  error.

    If Subfolders Then
        For Each SubFolder In SourceFolder.Subfolders
            ListFilesInFolder SubFolder, True '"wrong number of arguments or invalid property assignment" and "ListFilesInFolder" is highlighted
        Next SubFolder
    End If
'The rest of the macro is working just fine.

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing

LstR = ActiveSheet.Columns("$B").End(xlDown).Row

Set SOPRng = ActiveSheet.Range("$A$1:$G$" & LstR)

Set SOPDatabase = ActiveSheet.ListObjects.Add(xlSrcRange, SOPRng, , xlYes, , "TableStyleMedium2")

        SOPDatabase.Name = "SOPDatabase"

With SOPDatabase.Range
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
With SOPDatabase.HeaderRowRange
    .Font.Bold = True
    .Font.Size = 14
    .Font.Color = vbBlack
    .WrapText = True
End With


Application.ScreenUpdating = True

End Sub

It may be my lack of knowledge of VBA and how to define variables. I am still trying to understand that part.

Upvotes: 0

Views: 257

Answers (2)

Yuca
Yuca

Reputation: 6091

change

Call ListFilesInFolder(SourceFolder)

to 

Call ListFilesInFolder(SourceFolder,True)

this should at least yield a different error from your current one

Upvotes: 1

Ken Love
Ken Love

Reputation: 178

I don't think the code you posted will compile. You call Call ListFilesInFolder(SourceFolder), but the sub requires two arguments: SourceFolder and Subfolders. Did you perhaps add the Subfolders parameter to ListFilesInFolder(SourceFolder As Scripting.Folder, Subfolders As Boolean) AFTER you got the "wrong number of arguments" error in ListFilesInFolder SubFolder, True?

In other words, does this code currently compile and run up until ListFilesInFolder SubFolder, True?

Upvotes: 0

Related Questions