File List with User customisations?

[rosspmm]

I got this code from this forum fairly recently and i works great :clap:



Sub GetFileList()
Dim iCtr As Integer
With Application.FileSearch
.NewSearch
.LookIn = "N:\DEPT\Projects\DD - Projects by Business Unit\2005\"
.SearchSubFolders = True
.Filename = "*.*"
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Next iCtr
End If
End With
End Sub

What I now want to do is to be able to specify which element of the directory path should be hidden/ommitted when displayed on screen as the directory path is very long and makes it almost impossible to use the subsequent output.

Any ideas?
would rather I could add it to the above rather than as a separate macro and be able to specify via a message box.

I thought the fileloader.xls responses earlier would help but I cant seem to work out how to make that produce a printed list :doh:

Ross

[Bob Phillips]

What I now want to do is to be able to specify which element of the directory path should be hidden/ommitted when displayed on screen as the directory path is very long and makes it almost impossible to use the subsequent output.

Any ideas?
would rather I could add it to the above rather than as a separate macro and be able to specify via a message box.

I thought the fileloader.xls responses earlier would help but I cant seem to work out how to make that produce a printed list

One way



Sub GetFileList()
Dim iCtr As Integer
Dim sTemp
Dim iPos As Long
Const MAX_LENGTH As Long = 30
Const DIR_PATH As String = "C:\MyTest" '"N:\DEPT\Projects\DD - Projects by Business Unit\2005\"
With Application.FileSearch
.NewSearch
.LookIn = DIR_PATH
.SearchSubFolders = True
.Filename = "*.*"
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
sTemp = .FoundFiles(iCtr)
If Len(sTemp) > MAX_LENGTH Then
iPos = InStrRev(sTemp, "\")
If Len(sTemp) - iPos > MAX_LENGTH Then
iPos = 25
End If
Cells(iCtr, 1).Value = Left(sTemp, MAX_LENGTH - (Len(sTemp) - iPos)) & _
"..." & Right(sTemp, Len(sTemp) - iPos + 1)
Else
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
End If
Next iCtr
End If
End With
End Sub

[rosspmm]

Thanks XLD

I have just run this and get a Run Time Error '5' Invalid Procedure Call or Argument at the following line:



Cells(iCtr, 1).Value = Left(sTemp, MAX_LENGTH - (Len(sTemp) - iPos)) & _
"..." & Right(sTemp, Len(sTemp) - iPos + 1)


Any ideas on why?

How do I fix it?

Many thanks in advance

Ross

[Bob Phillips]

Thanks XLD

I have just run this and get a Run Time Error '5' Invalid Procedure Call or Argument at the following line:



Cells(iCtr, 1).Value = Left(sTemp, MAX_LENGTH - (Len(sTemp) - iPos)) & _
"..." & Right(sTemp, Len(sTemp) - iPos + 1)


Any ideas on why?

Debug all the values and post them.

[jetted]

Hi Ross

Your Run Time Error '5' Invalid Procedure Call is due to the fact the path is longer then 30 characters long if you change the line code
Const MAX_LENGTH As Long = 30 by

Const MAX_LENGTH As Long = 100. It should be ok

Thanks