VBA SPLIT Error

[STEVEMILLS04]

Ok, an hour of searching and bending my mind isn't doing the trick, so I turn to you MrExcel experts.

I am doing a what-should-be-simple SPLIT on a filename, code below. I get the following error "Subscript out of range", which I believe is because there is nothing in the (1) position in the array?

Filenames are as follows: 12300-AddressChange.pdf, 12921-Promotion.pdf, 12929-RateIncrease.pdf

When I print the filename, it shows accurately "12300-AddressChange.pdf". When I print splitName(0) it's accurate, "12300". When I add splitName(1) it errors out.

I believe it has something to do with the fileName, as when I split the string instead of the variable, it works just fine. For example,

splitName = Split("12300-AddressChange.pdf", "-")

I just don't know why.

Sub loopAllSubFolderSelectStartDirector()

'Another Macro must call LoopAllSubFolders Macro to start procedure
Call LoopAllSubFolders("R:\Human Resources Private\Employee Files Dayforce\To Be Filed\")

End Sub


Sub LoopAllSubFolders(ByVal folderPath As String)

    Dim fileName As String
    Dim fullFilePath As String
    Dim numFolders As Long
    Dim folders() As String
    Dim i As Long
    Dim lPosition As Long
    Dim eeNum As String
    Dim parentFolder As String
    Dim splitName() As String
    Dim colF As String
        
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    fileName = Dir(folderPath & "*.*", vbDirectory)
    
    While Len(fileName) <> 0
    
        If Left(fileName, 1) <> "." Then
     
            fullFilePath = folderPath & fileName
            
            splitName = Split(fileName, "-")
            eeNum = splitName(0)
            colF = splitName(1)
     
            If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
                ReDim Preserve folders(0 To numFolders) As String
                folders(numFolders) = fullFilePath
                numFolders = numFolders + 1
            Else
                'Insert the actions to be performed on each file
                'This example will print the ee# then the file name to the immediate window
                Debug.Print fileName
                Debug.Print "eeNum = " & eeNum & ", colF = " & colF
            End If
     
        End If
     
        fileName = Dir()
    
    Wend
    
    For i = 0 To numFolders - 1
    
        LoopAllSubFolders folders(i)
     
    Next i


End Sub

 

[Rick Rothstein]

I think the problem may stem from the fact that the first directory name Dir(Path,vbDirectory) returns is nothing more than a single dot (the second directory name will be a double dot... after that it will return normal directory names. I think you simply need to check for and ignore the "." and ".." directory names in your While/Wend loop.

 

[STEVEMILLS04]

Rick, thanks for the reply. Any quick tips on how to go about this? I gave it a go and was not successful.

 

[STEVEMILLS04]

I think the problem may stem from the fact that the first directory name Dir(Path,vbDirectory) returns is nothing more than a single dot (the second directory name will be a double dot... after that it will return normal directory names. I think you simply need to check for and ignore the "." and ".." directory names in your While/Wend loop.

I got it to work. Don't know why, but it works!

Sub LoopAllSubFolders(ByVal folderPath As String)

    Dim fileName As String
    Dim fullFilePath As String
    Dim numFolders As Long
    Dim folders() As String
    Dim i As Long
    Dim lPosition As Long
    Dim eeNum As String
    Dim splitName() As String
    Dim colF As String
    Dim colG As String
    
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    fileName = Dir(folderPath & "*.*", vbDirectory)
        
        While Len(fileName) <> 0
        
           If Left(fileName, 1) <> "." Then
        
               fullFilePath = folderPath & fileName
               
               'Extract the employee number from fileName and set to variable
               
               splitName = Split(fileName, "-")
               eeNum = splitName(0)
               colG = eeNum
        
               If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
                   ReDim Preserve folders(0 To numFolders) As String
                   folders(numFolders) = fullFilePath
                   numFolders = numFolders + 1
               Else
                   'Insert the actions to be performed on each file
                   'This example will print the ee# then the file name to the immediate window
                   colF = splitName(1)
                   Debug.Print fileName & ", " & colF
               End If
        
           End If
     
            fileName = Dir()
    
        Wend
        
    For i = 0 To numFolders - 1
    
        LoopAllSubFolders folders(i)
     
    Next i


End Sub